SSM 实训笔记 -11- 使用 Spring MVC + JDBC Template 实现筛选、检索功能（maven）
本篇是新建的一个数据库，新建的一个完整项目。
本篇内容：
（1）使用 Spring MVC + JDBC Template 实现数据库查询
（2）实现对数据库信息的筛选、检索功能
（3）分类查询的功能
效果演示：
一、创建数据库
（1）如果没有 Navicat 可以自己手动在命令行建库。
分享一篇文章：Navicat for MySQL 最新版安装与破解 + 报错解决办法
（2）创建 hrdb 数据库，创建 employee 表：
（3）创建 post 表（职位表）：
二、创建和配置 Maven Web 项目、
（1）如果不会创建项目请参考：
（！特别提醒，建议项目名及包名和下面的本篇的项目文件名一致）
SSM 实训笔记 -05- 创建 Maven Web 项目 + Tomcat 及目录结构配置
（2）完整项目目录结构：
（3）先创建项目，标记文件夹（java、resources）
（4）使用 Maven 加载依赖包：
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.13</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.1.3.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-webmvc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>5.1.3.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.54</version>
</dependency>
<!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>4.0.1</version>
<scope>provided</scope>
</dependency>
</dependencies>
配置 pom 需要导入，我们选择自动导入：
（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"/>
<!--配置 json 消息转换器-->
<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 class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations" value="classpath*:jdbcConfig.properties">
</property>
</bean>-->
<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>
<!--数据展示部分 js -->
<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>
<!--<a class="btn btn-primary" style="float:right;margin-right: 10%" onclick="showAddModal()"><span class="glyphicon glyphicon-plus"></span>添加</a>-->
</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>
<!-- 按钮触发模态框 -->
<!--<button class="btn btn-primary btn-lg" data-toggle="modal" data-target="#myModal">开始演示模态框</button>-->
<!-- 模态框（Modal） -->
<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><!-- /.modal-content -->
</div><!-- /.modal -->
</div>
</div>
<!--80% 控制-->
</body>
</html>
（2）pojo/UserInfo.java 文件：
package pojo;
import org.springframework.jdbc.core.ResultSetExtractor;
import java.util.List;
import java.util.Map;
public class UserInfo {
private int id;
private int post_type;
private String emp_name;
private int emp_sex;
private int emp_age;
private String emp_depart;
private int emp_year;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getPost_type() {
return post_type;
}
public void setPost_type(int post_type) {
this.post_type = post_type;
}
public String getEmp_name() {
return emp_name;
}
public void setEmp_name(String emp_name) {
this.emp_name = emp_name;
}
public int getEmp_sex() {
return emp_sex;
}
public void setEmp_sex(int emp_sex) {
this.emp_sex = emp_sex;
}
public int getEmp_age() {
return emp_age;
}
public void setEmp_age(int emp_age) {
this.emp_age = emp_age;
}
public String getEmp_depart() {
return emp_depart;
}
public void setEmp_depart(String emp_depart) {
this.emp_depart = emp_depart;
}
public int getEmp_year() {
return emp_year;
}
public void setEmp_year(int emp_year) {
this.emp_year = emp_year;
}
}
（3）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);
}
}
（4）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;
}
}
}
（5）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 pojo.UserInfo;
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 {
System.out.println(userInfo.getEmp_name()+userInfo.getPost_type());
if (userInfo.getEmp_name()==""){
userInfo.setEmp_name("1");
}
String emp_name = userInfo.getEmp_name();
int post_type = userInfo.getPost_type();
List<Map<String, Object>> post = employeeService.doSelect(emp_name,post_type);
return post;
}
}