JdbcTemplate的功能类似于SSM三层架构的查询数据库的信息页面(十六课)
代码模块的展示
第一步 定义一个页面用于在Html中展示数据的所有信息 这个页面发出Ajax请求到服务器中去 利用Json解析数据
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>员工管理系统-员工信息列表</title> <style> * { font-size: 28px; font-family: 方正粗黑宋简体; } h1 { background-color: lightseagreen; color: white; } button { width: 90%; } #sea, .delete, .update { color: white; font-weight: bold; padding: 5px 10px; border-width: 0px; border-radius: 6px; } #sea { background-color: #aae5f2; width: 100px; } .update { background-color: lightseagreen; color: black; } .delete { background-color: white; color: black; } #sea:hover, .update:hover { background-color: green; opacity: 0.9; color: white; height: 40px; } .delete:hover { background-color: red; color: white; height: 40px; } table { width: 100%; background-color: lightseagreen; color: white; border-radius: 20px; text-align: center; padding: 4px; } td:hover { background-color: white; color: black; border-radius: 20px; height: 40px; } td { height: 20%; } input { border-radius: 8px; border: 2px solid black; } datalist:hover { background-color: black; color: red; } </style> <script src="js/jquery-3.6.js"></script> <script> $(function () { //当页面加载完成后，自动发出ajax请求 $.ajax({ url: "ListServlet", type: "POST", data: "", success: function (json) { }, error: function () { alert("连接失败"); }, dataType: "json" }); }); //定义方法，用于判断文本框中内容改变事件 function change(obj) { //获得用户选择的部门名称 var info= obj.value; //将部门名称通过ajax请求发送给服务器，要求服务器根据部门名称查询该部门下的员工的入职时间 $.ajax({ url:"ListServlet", type:"GET", data:"workClass="+info, success:function (json) { //将原有的数据进行清空 $("#d3").empty(); //["","",""] for (var i = 0; i <json.length ; i++) { $("#d3").append("<option>"+json[i]+"</option>"); } }, error:function () { alert("链接失败"); }, dataType:"json" }); } </script> </head> <body> <div align="center"> <h1>员工信息列表</h1> <p> <input type="search" list="d2" onchange="change(this)" name="workClass" placeholder="请选择部门"> <datalist id="d2"></datalist> <input type="search" list="d3" name="openDate" placeholder="请选择入职时间"> <datalist id="d3"></datalist> <input id="sea" type="button" onclick="search()" value="搜索"> </p> <table id="tab"> <tr> <th>员工编号</th> <th>员工姓名</th> <th>员工性别</th> <th>员工年龄</th> <th>入职时间</th> <th>所属部门</th> <th>修改</th> <th>删除</th> </tr> </table> <input type="search" list="d4" name="name" placeholder="请查看姓名"> <datalist id="d4"></datalist> <input type="search" list="d5" name="age" placeholder="请查看年龄"> <datalist id="d5"></datalist> <input type="search" list="d6" name="name" placeholder="请查看性别"> <datalist id="d6"></datalist> </div> </body> </html>
访问这个地址进入SSM中的控制层ListServlet
第二步:SSM三层的架构搭建
控制层 @WebServlet(urlPatterns = "/ListServlet")
package Com.Emp.Controller; import Com.Emp.Service.EmpServiceImp; import Com.Emp.Service.IEmpService; import com.fasterxml.jackson.databind.ObjectMapper; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.util.HashMap; import java.util.List; import java.util.Map; @WebServlet(urlPatterns = "/ListServlet") public class ListServlet extends HttpServlet { IEmpService service = new EmpServiceImp(); protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("application/json"); response.setCharacterEncoding("UTF-8"); request.setCharacterEncoding("UTF-8"); //1.从数据库中查询所有部门名称 List<String> workClass = service.getWorkClass(); List<String> name = service.getName(); List<String> age = service.getAge(); List<String> sex = service.getSex(); //2.从数据库中查询所有入职时间 List<String> dates = service.getDates(); //3.从数据库中查询所有员工信息 SELECT * FROM emp List<Map<String, Object>> list = service.getList(); //采用jso格式封装 将三部分的数据采用json格式进行封装 [][][] map中调用list集合 Map<String, Object> map = new HashMap<>(); map.put("workClass", workClass); map.put("name", name); map.put("age", age); map.put("sex", sex); map.put("dates", dates); map.put("list", list); }
业务逻辑层
package Com.Emp.Service; import java.util.List; import java.util.Map; public interface IEmpService { //查询的是一个字段 然后是多个部门信息所以在java中用的类型是 //list<String>的类型的方式 List<String> getWorkClass(); List<String> getName(); List<String> getSex(); List<String> getAge(); int add(Map<String, String[]> map); List<String> getDates(); List<Map<String, Object>> getList(); //根据部门名称查询该部门下的入职时间 List<String> getDateByWorkClass(String workClass); List<Map<String, Object>> getlist(String woClass,String openDate); }
package Com.Emp.Service; import Com.Emp.Dao.EmpDaoImp; import Com.Emp.Dao.IEmpDao; import java.text.SimpleDateFormat; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; public class EmpServiceImp implements IEmpService { IEmpDao dao = new EmpDaoImp(); @Override public List<String> getWorkClass() { return dao.getWorkClass(); } @Override public List<String> getName() { return dao.getName(); } @Override public List<String> getSex() { return dao.getSex(); } @Override public List<String> getAge() { return dao.getAge(); } @Override public List<String> getDates() { return dao.getDates(); } @Override public List<Map<String, Object>> getList() { return dao.getList(); } @Override public List<String> getDateByWorkClass(String workClass) { //判断部门信息是否是空的，如果不为空，则将部门信息作为查询条件 //select openDate from emp where workClass=? order by openDate //如果为空，查询整张表的入职时间 //select openDate from emp order by openDate //以上两种情况可以通过模糊查询解决 workClass=(workClass!=null)?"%"+workClass+"%":"%%"; return dao.getDateByWorkClass(workClass); } @Override public List<Map<String, Object>> getlist(String woClass, String openDate) { //用户选择部门 no 时间 // woClass=(woClass!=null,openDate=null); //用户选择时间 no 部门 // woClass=(woClass=null,openDate!=null); //用户 选择 部门 选择时间 // woClass=(woClass!=null,openDate=null); //啥也不想干 // workClass = (workClass != null) ? "%" + workClass + "%" : "%%"; woClass = (woClass != null) ? "%" + woClass + "%" : "%%"; openDate = (openDate != null) ? "%" + openDate + "%" : "%%"; return dao.getlist(woClass, openDate); } }
数据访问层
package Com.Emp.Dao; import java.util.List; import java.util.Map; public interface IEmpDao { List<String> getWorkClass(); /*select distinct name from emp select distinct sex from emp select distinct age from emp*/ List<String> getName(); List<String> getSex(); List<String> getAge(); int add(Map<String, Object> map); //获取入职时间 List<String> getDates(); List<Map<String, Object>> getList(); //根据部门名称查询该部门下的入职时间 List<String> getDateByWorkClass(String workClass); //重载 List<Map<String, Object>> getlist(String woClass,String openDate); }
package Com.Emp.Dao; import Com.Emp.Utils.DBUtil; import org.springframework.jdbc.core.RowMapper; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import java.util.Map; public class EmpDaoImp implements IEmpDao { /*一级联动*/ @Override public List<String> getWorkClass() { /*在*/ return DBUtil.jt.query("select distinct workClass from emp", new MyRowMapper("workClass")); } @Override public List<String> getName() { return DBUtil.jt.query("select distinct name from emp", new MyRowMapper("name")); } @Override public List<String> getSex() { return DBUtil.jt.query("select distinct sex from emp", new MyRowMapper("sex")); } @Override public List<String> getAge() { return DBUtil.jt.query("select distinct age from emp", new MyRowMapper("age")); } /** * 增加 * @param map * @return */ @Override public int add(Map<String, Object> map) { return DBUtil.jt.update("insert into emp values(?,?,?,?,?,?)", new Object[]{0, map.get("name"), map.get("sex"), map.get("age"), map.get("openDate"), map.get("workClass")}); } @Override public List<String> getDates() { //SELECT DISTINCT openDate FROM emp ORDER BY openDate return DBUtil.jt.query("SELECT DISTINCT openDate FROM emp ORDER BY openDate", new MyRowMapper("openDate")); } @Override public List<Map<String, Object>> getList() { return DBUtil.jt.queryForList("SELECT * FROM emp"); } @Override public List<String> getDateByWorkClass(String workClass) { return DBUtil.jt.query("select distinct openDate from emp where workClass like ? order by openDate", new Object[]{workClass}, new MyRowMapper("openDate")); } @Override public List<Map<String, Object>> getlist(String woClass, String openDate) { return DBUtil.jt.queryForList("select * from emp where workClass like ?and operDate like ?", new Object[]{woClass, openDate}); } /*自己定义*/ class MyRowMapper implements RowMapper<String> { String str = ""; public MyRowMapper(String str) { this.str = str; } @Override public String mapRow(ResultSet resultSet, int i) throws SQLException { return resultSet.getString(str); } } }
返回控制层
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("application/json"); response.setCharacterEncoding("UTF-8"); request.setCharacterEncoding("UTF-8"); //1.从数据库中查询所有部门名称 List<String> workClass = service.getWorkClass(); List<String> name = service.getName(); List<String> age = service.getAge(); List<String> sex = service.getSex(); //2.从数据库中查询所有入职时间 List<String> dates = service.getDates(); //3.从数据库中查询所有员工信息 SELECT * FROM emp List<Map<String, Object>> list = service.getList(); //采用jso格式封装 将三部分的数据采用json格式进行封装 [][][] map中调用list集合 Map<String, Object> map = new HashMap<>(); map.put("workClass", workClass); map.put("name", name); map.put("age", age); map.put("sex", sex); map.put("dates", dates); map.put("list", list); //{workclass:[],dates:[],listMap:[]} //{} Map集合 自定义 实体类 //数组 list集合 set集合 //{workclass:["","",""] ,dates:["","",""],list[{"id":1,"name":"李四"}],{},{}} new ObjectMapper().writeValue(response.getWriter(), map); }
运行结果展示:
下篇博客的内容介绍
