SSM 实训笔记 -11- 使用 Spring MVC + JDBC Template 实现筛选、检索功能（maven）

悠悠 2022-03-28 07:46 219阅读 0赞

### SSM 实训笔记 -11- 使用 Spring MVC + JDBC Template 实现筛选、检索功能（maven） ###

本篇是新建的一个数据库，新建的一个完整项目。

**本篇内容：**  
（1）使用 Spring MVC + JDBC Template 实现数据库查询

（2）实现对数据库信息的筛选、检索功能

（3）分类查询的功能

**效果演示：**

![在这里插入图片描述][20190109224834559.gif]

### 一、创建数据库 ###

（1）如果没有 Navicat 可以自己手动在命令行建库。  
分享一篇文章：[Navicat for MySQL 最新版安装与破解 + 报错解决办法][Navicat for MySQL _ _]

**（2）创建 hrdb 数据库，创建 employee 表：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTQ3ODYz_size_16_color_FFFFFF_t_70]

**（3）创建 post 表（职位表）：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTQ3ODYz_size_16_color_FFFFFF_t_70 1]

### 二、创建和配置 Maven Web 项目、 ###

**（1）如果不会创建项目请参考：**

**（！特别提醒，建议项目名及包名和下面的本篇的项目文件名一致）**

[SSM 实训笔记 -05- 创建 Maven Web 项目 + Tomcat 及目录结构配置][SSM _ -05- _ Maven Web _ _ Tomcat]

**（2）完整项目目录结构：**  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTQ3ODYz_size_16_color_FFFFFF_t_70 2][]

（3）先创建项目，标记文件夹（java、resources）

**（4）使用 Maven 加载依赖包：**

<dependencies>
        <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <version>4.11</version>
          <scope>test</scope>
        </dependency>
    
        
        <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>8.0.13</version>
        </dependency>
        
        <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-jdbc</artifactId>
          <version>5.1.3.RELEASE</version>
        </dependency>
        
        <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-webmvc</artifactId>
          <version>5.1.3.RELEASE</version>
        </dependency>
        
        <dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>fastjson</artifactId>
          <version>1.2.54</version>
        </dependency>
        
        <dependency>
          <groupId>javax.servlet</groupId>
          <artifactId>javax.servlet-api</artifactId>
          <version>4.0.1</version>
          <scope>provided</scope>
        </dependency>
    
    
      </dependencies>

**配置 pom 需要导入，我们选择自动导入：**  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTQ3ODYz_size_16_color_FFFFFF_t_70 3][]

（5）打开配置 web.xml 文件：

<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd" >
    
    <web-app>
      <display-name>Archetype Created Web Application</display-name>
      <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>/WEB-INF/applicationContext.xml</param-value>
      </context-param>
      <listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
      </listener>
      <servlet>
        <servlet-name>spring</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <init-param>
          <param-name>contextConfigLocation</param-name>
          <param-value>/WEB-INF/springmvc.xml</param-value>
        </init-param>
        <load-on-startup>1</load-on-startup>
      </servlet>
      <servlet-mapping>
        <servlet-name>spring</servlet-name>
        <url-pattern>*.do</url-pattern>
      </servlet-mapping>
    </web-app>

**（6）在 web.xml 文件的目录下，创建 springmvc.xml （建议名称一致）：**

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:mvc="http://www.springframework.org/schema/cache" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/cache http://www.springframework.org/schema/cache/spring-cache.xsd">
        <context:annotation-config/>
        <context:component-scan base-package="com.xpwi.controller"/>
    
        
        <bean class="org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter">
            <property name="messageConverters">
                <list>
                    <bean class="com.alibaba.fastjson.support.spring.FastJsonHttpMessageConverter"></bean>
                </list>
            </property>
        </bean>
    
    </beans>

**（7）同样在 web.xml 文件的目录下，创建 applicationContext.xml （建议名称一致）：**

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
        <context:annotation-config/>
        <context:component-scan base-package="com.xpwi.dao,com.xpwi.service"/>
        
        <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            <property name="url" value="jdbc:mysql://localhost:3306/hrdb?serverTimezone=UTC"></property>
            <property name="username" value="root"></property>
            <property name="password" value="xiaopengwei"></property>
            <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
        </bean>
        <bean id="jdbcTemplage" class="org.springframework.jdbc.core.JdbcTemplate">
            <property name="dataSource" ref="dataSource"></property>
        </bean>
    </beans>

**（8）在 resources/jdbc.properties（手动创建的目录，没有直接创建此类的文件的方式，直接新建 file，自己写后缀名即可，内容：**

**（填写自己创建的数据库的信息）**

url=jdbc:mysql://localhost:3306/hrdb?serverTimezone=UTC
    username=root
    password=xiaopengwei
    driverClassName=com.mysql.cj.jdbc.Driver

\*\*（9）在 webapp/node 目录下，安装 jQuery 和 Bootstrap3 \*\*：

**步骤：**

*  安装 node.js
 *  在 cmd 进入上述目录
 *  使用：

npm install jquery

npm install bootstrap3

### 三、项目源代码 ###

**（1）index.html 文件：**

<!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>查询用户</title>
        <link rel="stylesheet" href="node/node_modules/bootstrap3/dist/css/bootstrap.min.css">
        <script type="text/javascript" src="node/node_modules/jquery/dist/jquery.js"></script>
        <script type="text/javascript" src="node/node_modules/bootstrap3/dist/js/bootstrap.min.js"></script>
        <script type="text/javascript"> $(function () { $.ajax({ url:"findAllPost.do", //async:false, method:"get", dataType:"json", success:function (data) { //alert(data); var str="<option value='0'>未选择</option>"; //var jsonObj = JSON.parse(data); $.each(data,function (index,row,rs) { str+="<option value='"+row.id+"'>"+row.post_name+"</option>"; }); $("#input_post").html(str); }, error:function () { alert("请求失败"); } }); }); function doSelect() { var input_name = $("#input_name").val(); var input_post = $("#input_post").val(); $.ajax({ // url:"http://10.2.21.34:8080/StudentSystem/userServletForAjax", url:"doSelect.do", method:"get", data:{ "emp_name":input_name, "post_type":input_post }, dataType:"json", success:function (data) { //alert("成功"); var str = ""; $.each(data,function (i,item) { //对数据库数据进行转换 if (item.post_type == 1) { item.post_type = "行政助理"; }else if(item.post_type == 2) { item.post_type = "业务经理"; }else{ item.post_type = "总经理"; }; if (item.emp_sex == 1) { item.emp_sex = "男"; }else{ item.emp_sex = "女"; }; str+="<tr><td><input type='checkbox' value='" +item.id+"'></td><td>" +item.id+"</td><td>" +item.post_type+"</td><td>" +item.emp_name+"</td><td>" +item.emp_sex+"</td><td>" +item.emp_age+"</td><td>" +item.emp_depart+"</td><td>" +item.emp_year+"</td><td>" +"<a href='javascript:deleteUserById(\""+data.id+"\")' title='删除' onclick='return confirm(\"是否真的删除记录?\")'><span class='glyphicon glyphicon-remove'></span>删除</a></td></tr>"; }); $("#alluser").html(str); }, error:function () { alert("服务器请求失败") } }) } </script>
        
        <script> function addUserInfo() { $.post("deleteUserByIdServlet",{ "uname":$("#uname").val(),"role":$("#role").val()},function (data) { if (data == "true"){ alert("添加成功！"); findAllUser(); } else { alert("添加失败！"); } }); } function showAddModal() { $("#myModal").modal("show"); } //删除 function deleteUserById(id) { $.ajax({ url:"deleteUserByIdServlet", data:{ "id":id}, success:function (result) { if (result=="true"){ findAllUser(); } else { alert("删除记录失败！"); } },error:function () { alert("访问服务器失败") } }) } //查询 function findAllUser() { $.ajax({ // url:"http://10.2.21.34:8080/StudentSystem/userServletForAjax", url:"doFindAllUser.do", method:"get", dataType:"json", success:function (data) { //alert("成功"); var str = ""; $.each(data,function (i,item) { //对数据库数据进行转换 if (item.post_type == 1) { item.post_type = "行政助理"; }else if(item.post_type == 2) { item.post_type = "部门经理"; }else{ item.post_type = "总经理"; }; if (item.emp_sex == 1) { item.emp_sex = "男"; }else{ item.emp_sex = "女"; }; str+="<tr><td><input type='checkbox' value='" +item.id+"'></td><td>" +item.id+"</td><td>" +item.post_type+"</td><td>" +item.emp_name+"</td><td>" +item.emp_sex+"</td><td>" +item.emp_age+"</td><td>" +item.emp_depart+"</td><td>" +item.emp_year+"</td><td>" +"<a href='javascript:deleteUserById(\""+data.id+"\")' title='删除' onclick='return confirm(\"是否真的删除记录?\")'><span class='glyphicon glyphicon-remove'></span>删除</a></td></tr>"; }); $("#alluser").html(str); }, error:function () { alert("服务器请求失败") } }) } findAllUser(); </script>
    </head>
    <body>
    <div style="width: 80%;margin-left: 10%">
    <div class="panel panel-default">
        <div class="panel-heading">
            <h3 class="text-info">
                雇员信息管理系统
            </h3>
        </div>
        <form name="loginForm">
        <div class="panel-body">
            雇员姓名
            <input class="form-control" name="input_name" id="input_name" placeholder="请输入姓名"><br>
            雇员职位
            <select id="input_post" name="input_post" class="form-control">
                <option value="0">请选择</option>
            </select>
        </div>
    
        
        <a class="btn btn-primary" style="margin-left: 10%" onclick="doSelect()">查询一下</a>
        </form>
    </div>
    
    
    </div>
    
    
    <hr>
    
    
    <div style="width: 80%;margin-left: 10%">
        <div class="well" style="height: 70px">
            <h3>用户信息</h3>
            
        </div>
        <table class="table table-striped table-hover">
            <tr>
                <td>全选</td>
                <td>雇员编号</td>
                <td>雇员职位</td>
                <td>雇员姓名</td>
                <td>雇员性别</td>
                <td>雇员年龄</td>
                <td>所属部门</td>
                <td>雇员工龄</td>
                <td>操作</td>
            </tr>
            <tbody id="alluser">
    
            </tbody>
        </table>
    
    
        
        
        
        <div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
            <div class="modal-dialog">
                <div class="modal-content">
                    <div class="modal-header">
                        <button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
                        <h4 class="modal-title" id="myModalLabel">添加用户信息</h4>
                    </div>
    
                    <div class="modal-footer">
                        <div>
    
                            
                            <div class="input-group">
                                <input id="uname" type="text" class="form-control" placeholder="请输入用户名">
                                <span class="glyphicon glyphicon-user input-group-addon"></span>
                            </div>
                            <br>
                            <select id="role" name="role" class="form-control ">
                                <option value="">请选择</option>
                                <option value="admin">管理员</option>
                                <option value="teacher">教师</option>
                                <option value="student">学生</option>
                            </select>
                        </div>
                        <br>
                        <button type="button" class="btn btn-default" data-dismiss="modal">关闭</button>
                        <button type="button" class="btn btn-primary" onclick="addUserInfo()">添加</button>
                    </div>
                </div>
            </div>
        </div>
    
    </div>
    
    </body>
    </html>

**（2）service/EmployeeService.java 文件：**

package com.xpwi.service;
    
    import com.xpwi.dao.EmployeeDAO;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import java.util.List;
    import java.util.Map;
    
    @Service
    public class EmployeeService { 
        @Autowired
        EmployeeDAO employeeDAO;
        public List<Map<String, Object>> findPost() { 
            return employeeDAO.findPost();
        }
    
    
        public List<Map<String, Object>> doFindAllUser() { 
            return employeeDAO.doFindAllUser();
        }
    
        public List<Map<String, Object>> doSelect(String emp_name, int post_type) { 
            return employeeDAO.doSelect(emp_name,post_type);
        }
    }

**（3）dao/Employee.java 文件：**

package com.xpwi.dao;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.ResultSetExtractor;
    import org.springframework.stereotype.Repository;
    
    import java.util.List;
    import java.util.Map;
    
    @Repository
    public class EmployeeDAO { 
        @Autowired
        JdbcTemplate jdbcTemplate;
        public List<Map<String,Object>> findPost(){ 
            String sql="select * from post";
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
            return maps;
        }
    
    
        public List<Map<String, Object>> doFindAllUser() { 
            String sql="select * from employee";
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
            return maps;
        }
    
        public List<Map<String, Object>> doSelect(String emp_name, int post_type) { 
            String sql = "select * from employee where emp_name = '"+emp_name +"'";
            String sql2 = "select * from employee where post_type = "+ post_type;
            //System.out.println("111");
            //System.out.println("**2**:"+post_type);
            List<Map<String, Object>> maps;
            maps = jdbcTemplate.queryForList(sql);
            if (maps.isEmpty()){ 
                maps = jdbcTemplate.queryForList(sql2);
                return maps;
            }else { 
                return maps;
            }
        }
    }

**（4）controller/Employee.java 文件：**

package com.xpwi.controller;
    
    import com.xpwi.service.EmployeeService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.ResponseBody;
    
    import java.io.UnsupportedEncodingException;
    import java.util.List;
    import java.util.Map;
    
    @Controller
    public class Employee { 
        @Autowired
        EmployeeService employeeService;
        @RequestMapping(value = "/findAllPost.do",method = RequestMethod.GET)
        @ResponseBody
        public List<Map<String, Object>> findPost() throws UnsupportedEncodingException { 
            List<Map<String, Object>> post = employeeService.findPost();
            return post;
        }
    
    
        @RequestMapping(value = "/doFindAllUser.do",method = RequestMethod.GET)
        @ResponseBody
        public List<Map<String, Object>> doFindAllUser() throws UnsupportedEncodingException { 
            List<Map<String, Object>> post = employeeService.doFindAllUser();
            return post;
        }
    
        @RequestMapping(value = "/doSelect.do")
        @ResponseBody
    // public List<Map<String, Object>> doSelect(UserInfo userInfo) throws UnsupportedEncodingException { 
        public List<Map<String, Object>> doSelect(String emp_name,int post_type) throws UnsupportedEncodingException { 
    
            System.out.println(emp_name+"-"+post_type);
    
            List<Map<String, Object>> post = employeeService.doSelect(emp_name,post_type);
            return post;
        }
    }

### 四、运行调试 ###

![在这里插入图片描述][20190109224834559.gif]

### 五、功能优化 ###

**优化内容：**  
上述方式是在前端 js 对【 1 => 行政助理 】数据处理，不是很好

于是进行优化，我们有两张表，一张员工表，一张职位表，我们可以通过多表关联查询，实现对数据的处理

（提示：本次优化使用 and 作为 sql 条件，所以，查询为精确查询，必须名称和类型都对时，才返回数据）

**优化的步骤：**

**（1）替换上面【dao/Employee.java 文件】为：**

package com.xpwi.dao;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.stereotype.Repository;
    
    import java.util.List;
    import java.util.Map;
    
    @Repository
    public class EmployeeDAO { 
        @Autowired
        JdbcTemplate jdbcTemplate;
        public List<Map<String,Object>> findPost(){ 
            String sql="select * from post";
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
            return maps;
        }
    
    
        public List<Map<String, Object>> doFindAllUser() { 
            String sql="select * from employee";
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
            return maps;
        }
    
        public List<Map<String, Object>> doSelect(String emp_name, int post_type) { 
    
            //精确查询，必须名称和类型都正确
            String sql = "select e.id,post_name post_type,emp_name,case emp_sex when 1 then \"男\" when 2 then \"女\" else \"保密\" end emp_sex,emp_age,emp_depart,emp_year from employee e inner join post p on e.post_type=p.id where emp_name=\""+emp_name+"\" and post_type="+post_type;
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
            System.out.println("执行。。。");
    
            return maps;
        }
    }

**（2）删掉 index.html 中的部分代码：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTQ3ODYz_size_16_color_FFFFFF_t_70 4][]

### 更多文章链接： ###

*  [SSM 实训笔记][SSM]

### 支持博主 ###

我正在参加 CSDN 2018 年博客之星评选，希望大家能支持我，  
我是【No. 001】号 肖朋伟 ，感谢大家宝贵的一票 ^\_^/  
投票地址：[https://bss.csdn.net/m/topic/blog\_star2018/index][https_bss.csdn.net_m_topic_blog_star2018_index]

[20190109224834559.gif]: /images/20220328/163abbea20e642ca99fa6470da0c8761.png
[Navicat for MySQL _ _]: https://blog.csdn.net/qq_40147863/article/details/84231811
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTQ3ODYz_size_16_color_FFFFFF_t_70]: /images/20220328/c2dd2e90c6ad459c89e0cb72c03a8bef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTQ3ODYz_size_16_color_FFFFFF_t_70 1]: /images/20220328/bd8aaed4a77c4ef9862e4b37aa20f43c.png
[SSM _ -05- _ Maven Web _ _ Tomcat]: https://blog.csdn.net/qq_40147863/article/details/85771619
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTQ3ODYz_size_16_color_FFFFFF_t_70 2]: /images/20220328/256172e123d348e4b1ed27d8658f5214.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTQ3ODYz_size_16_color_FFFFFF_t_70 3]: /images/20220328/402ddba247644608aa9289a29f2c8c08.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTQ3ODYz_size_16_color_FFFFFF_t_70 4]: /images/20220328/f1ad7d77473b494ba7381b9e09219112.png
[SSM]: https://blog.csdn.net/qq_40147863/article/category/8577666
[https_bss.csdn.net_m_topic_blog_star2018_index]: https://bss.csdn.net/m/topic/blog_star2018/index