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

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

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

本篇内容:

(1)使用 Spring MVC + JDBC Template 实现数据库查询

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

(3)分类查询的功能

效果演示:

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

一、创建数据库

(1)如果没有 Navicat 可以自己手动在命令行建库。

分享一篇文章:Navicat for MySQL 最新版安装与破解 + 报错解决办法

(2)创建 hrdb 数据库,创建 employee 表:

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

(3)创建 post 表(职位表):

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

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

(1)如果不会创建项目请参考:

(!特别提醒,建议项目名及包名和下面的本篇的项目文件名一致)

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

(2)完整项目目录结构:

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

(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 需要导入,我们选择自动导入:

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

(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">&times;</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;
}
}

四、运行调试

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

更多文章链接:

上一篇:Lua中的协同程序 coroutine(转)


下一篇:【EasyUI】combotree和combobox模糊查询