基于 Spring JDBC Template实现数据表的基本操作
数据库连接字符串
jdbc:mysql://localhost:3306/db_database18?useUnicode=true&characterEncoding=UTF-8
基于 Spring JDBC Template实现数据表的基本操作
数据表结构
DROP TABLE IF EXISTS `tb_employee`;
CREATE TABLE `tb_employee` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '编号',
`name` varchar(45) NOT NULL COMMENT '姓名',
`dept` varchar(45) NOT NULL COMMENT '部门',
`level` varchar(45) NOT NULL COMMENT '职务',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=gbk;
编写Employee.java 文件，作为实体类模型
|
package dao; public class Employee {
private Integer id; //员工编号 private String name; //员工姓名 private String dept; //所在部门 private String level; //员工职位
/** * 获取员工编号 * @return Integer 员工编号 */ public Integer getId() { return this.id; }
/** * 设置员工编号 * @param id - Integer 员工编号 */ public void setId(Integer id) { this.id = id; }
/** * 获取员工姓名 * @return String 员工姓名 */ public String getName() { return this.name; }
/** * 设置员工姓名 * @param name - String 员工姓名 */ public void setName(String name) { this.name = name; }
/** * 获取员工所在部门 * @return String 部门名称 */ public String getDept() { return this.dept; }
/** * 设置部门名称 * @param dept - String 部门名称 */ public void setDept(String dept) { this.dept = dept; }
/** * 获取员工职位 * @return String 职位名称 */ public String getLevel() { return this.level; }
/** * 设置员工职位名称 * @param level - String 职位名称 */ public void setLevel(String level) { this.level = level; } } |
编写EmployeeDao.java 类，该类继承JdbcDaoSupport类，主要实现对数据库进行操作
|
package dao;
import java.util.List;
import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport;
/** * 对数据库进行操作 * @author Jingweiyu */ public class EmployeeDao extends JdbcDaoSupport { /** * 插入新记录 * @param employee - Employee 类对象 */ public void insert(Employee employee){ Object[] o = {employee.getName(),employee.getDept(),employee.getLevel()}; getJdbcTemplate().update("INSERT INTO tb_employee(name,dept,level) values (?,?,?)", o); } /** * @更新记录 * @param employee - Employee 类对象 */ public void update(Employee employee){ Object[] o = {employee.getName(),employee.getDept(),employee.getLevel(),employee.getId()}; getJdbcTemplate().update("UPDATE tb_employee set name=?,dept=?,level=? WHERE id = ?", o); } /** * 跟据id删除记录 * @param id - Integer 员工编号 */ public void delete(Integer id){ Object[] o = {id}; getJdbcTemplate().update("DELETE FROM tb_employee WHERE id = ?",o); } /** * 跟据id查找记录 * @param id - Integer 员工编号 * @return List 存放指定id员工信息的集合对象 */ public List findById(Integer id){ Object[] o = {id}; List list = getJdbcTemplate().queryForList("SELECT * FROM tb_employee WHERE id = ?", o); return list; } /** * 查询全部记录 * @return List 存放所有员工信息的集合对象 */ public List findAll(){ List list = getJdbcTemplate().queryForList("SELECT * FROM tb_employee"); return list; } } |
编写EmployeeServlet.java 文件，根据用户请求的参数不同调用不同的操作数据库的方法。
|
public class EmployeeServlet extends HttpServlet { /** * 对数据库进行操作类对象 */ private EmployeeDao employeeDao;
@Override public void init() throws ServletException { super.init(); // 获取Spring上下文件 对象 ApplicationContext webContext=new ClassPathXmlApplicationContext("applicationContext.xml"); // 从上下文件对象中获取employeeDao对象 employeeDao = (EmployeeDao) webContext.getBean("employeeDao"); }
|
doGet() 方法对应页面中的get请求，在此让get请求去执行doPost() 方法，代码如下
|
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request, response); // 让get请求执行post方法 } |
doPost() 方法对应页面的post请求，该方法从request对象中获取action请求，并根据参数的类型调用对应的操作数据库的方法。
|
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { int action = Integer.parseInt(request.getParameter("action"));// 获取请求中的参数 switch (action) { case 1: doFindAll(request, response); // 查找数据表中全部记录 break; case 2: doInsert(request, response); // 向数据表中插入记录 break; case 3: doUpdate(request, response); // 更新记录 break; case 4: doDelete(request, response); // 删除记录 break; case 5: doFindById(request, response); // 按id查询记录 break; } }
/** * 插入记录 * * @param request * @param response * @throws ServletException * @throws IOException */ public void doInsert(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { Employee employee = new Employee(); employee.setName(request.getParameter("name")); employee.setDept(request.getParameter("dept")); employee.setLevel(request.getParameter("level")); employeeDao.insert(employee); doFindAll(request, response); }
/** * 按照id更新数据表中的记录 * * @param request * @param response * @throws ServletException * @throws IOException */ public void doUpdate(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { Employee employee = new Employee(); employee.setName(request.getParameter("name")); employee.setDept(request.getParameter("dept")); employee.setLevel(request.getParameter("level")); employee.setId(Integer.parseInt(request.getParameter("id"))); employeeDao.update(employee); doFindAll(request, response); }
/** * 按照id删除数据表中的记录 * * @param request * @param response * @throws ServletException * @throws IOException */ public void doDelete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { employeeDao.delete(Integer.parseInt(request.getParameter("id"))); doFindAll(request, response); }
/** * 按照id查找数据记录 * * @param request * @param response * @throws ServletException * @throws IOException */ public void doFindById(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { List list = employeeDao.findById(Integer.parseInt(request .getParameter("id"))); request.setAttribute("list", list); request.getRequestDispatcher("update.jsp").forward(request, response); }
/** * 查出数据表中全部记录 * * @param request * @param response * @throws ServletException * @throws IOException */ public void doFindAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { List list = employeeDao.findAll(); request.setAttribute("list", list); request.getRequestDispatcher("show.jsp").forward(request, response); } |
编写applicationContext.xml配置文件,在该文件中配置一个数据源dataSource 和 jdbcTemplate,并且将注入到EmployeeDao中。
|
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"> <!-- 配置数据源 --> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName"> <value>com.mysql.jdbc.Driver</value> </property> <property name="url"> <value>jdbc:mysql://localhost:3306/db_database16?useUnicode=true&characterEncoding=utf-8 </value> </property> <property name="username"> <value>root</value> </property> <property name="password"> <value></value> </property> </bean> <!-- 配置jdbcTemplate --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource"> <ref local="dataSource"/> </property> </bean> <!-- 将jdbcTemplate注入到employeeDao中 --> <bean id="employeeDao" class="dao.EmployeeDao"> <property name="jdbcTemplate" ref="jdbcTemplate"/> </bean> </beans> |
编写index.jsp首页文件，在该文件中自动转向show.jsp页面。
|
<%@ page language="java" import="java.util.*" pageEncoding="GBK"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <body> <c:redirect url="EmployeeServlet"> <c:param name="action" value="1" /> </c:redirect> </body> </html>
|
编写show.jsp页面文件，该页面从request对象中获取保存表中的所有数据的list列表，并通过<c:forEach>标签将list列表中的所有信息显示在页面中。
|
<a href="insert.jsp" class="STYLE1">插入新记录</a>
<table border="1" align="center" cellpadding="1" cellspacing="1" bordercolor="#F7FCFF" bgcolor="#BEEFFF"> <tr align="center"> <td width="50" height="23" align="center" bordercolor="#F7FCFF" bgcolor="#F7FCFF"> <span class="STYLE3">编号</span> </td> <td width="100" height="23" align="center" bordercolor="#F7FCFF" bgcolor="#F7FCFF"> <span class="STYLE3">姓名</span> </td> <td width="50" height="23" align="center" bordercolor="#F7FCFF" bgcolor="#F7FCFF"> <span class="STYLE3">部门</span> </td> <td width="200" height="23" align="center" bordercolor="#F7FCFF" bgcolor="#F7FCFF"> <span class="STYLE3">职务</span> </td> <td width="70" height="23" align="center" bordercolor="#F7FCFF" bgcolor="#F7FCFF"> <span class="STYLE3">操作</span> </td> </tr> <c:forEach items='${list }' var="item" varStatus="i"> <tr> <td height="23" align="center" bordercolor="#F7FCFF" bgcolor="#F7FCFF" class="STYLE3"> <c:out value="${item.id}" /> </td> <td height="23" align="center" bordercolor="#F7FCFF" bgcolor="#F7FCFF" class="STYLE3"> <c:out value="${item.name}" /> </td> <td height="23" align="center" bordercolor="#F7FCFF" bgcolor="#F7FCFF" class="STYLE3"> <c:out value="${item.dept}" /> </td> <td height="23" align="center" bordercolor="#F7FCFF" bgcolor="#F7FCFF" class="STYLE3"> <c:out value="${item.level}" /> </td> <td height="23" align="center" bordercolor="#F7FCFF" bgcolor="#F7FCFF" class="STYLE3"> <a href='EmployeeServlet?action=5&id=<c:out value="${item.id}"/>'>修改</a> <a href='EmployeeServlet?action=4&id=<c:out value="${item.id}"/>'>删除</a> </td> </tr> </c:forEach> </table> |
编写insert.jsp页面文件，用于输入新员工信息。
|
<form action="EmployeeServlet?action=2" method="post"> <table width="596" border="0" align="center" cellpadding="0" cellspacing="0"> <tr> <td> <img src="images/bj_01.gif"></td> </tr> <tr> <td background="images/bj_02.gif" height="260px" align="center"> <table> <tr> <td width="50" height="26" align="center"><span class="STYLE2">姓名：</span></td> <td width="200" height="23"><input type="text" name="name"></td> </tr> <tr> <td height="26" align="center"><span class="STYLE2">部门：</span></td> <td height="23"><input type="text" name="dept"> </td> </tr> <tr> <td height="26" align="center"><span class="STYLE2">职务：</span></td> <td height="23"><input type="text" name="level"> </td> </tr> <tr> <td height="23" colspan="2" align="center"><input type="submit" value="确定"> </td> </tr> </table> </td> </tr> <tr> <td> <img src="images/bj_03.gif"></td> </tr> </table> </form> |
编写update.jsp页面文件，用于修改员工信息
|
<form action="EmployeeServlet?action=3" method="post"> <table width="596" border="0" align="center" cellpadding="0" cellspacing="0"> <tr> <td> <img src="images/bj_01.gif"></td> </tr> <tr> <td background="images/bj_02.gif" height="260px" align="center"> <table> <tr> <td width="50" height="26" align="center"><span class="STYLE2">姓名：</span></td> <td width="200" height="23"><input type="text" name="name" value="${list[0].name}"></td> </tr> <tr> <td height="26" align="center"><span class="STYLE2">部门：</span></td> <td height="23"><input type="text" name="dept" value="${list[0].dept}"> </td> </tr> <tr> <td height="26" align="center"><span class="STYLE2">职务：</span></td> <td height="23"> <input name="level" type="text" value="${list[0].level}"> <input type="hidden" name="id" value="${list[0].id}"> </td> </tr> <tr> <td height="23" colspan="2" align="center"> <input type="submit" value="确定"> <input type="reset" value="重置"> </td> </tr> </table> </td> </tr> <tr> <td> <img src="images/bj_03.gif"></td> </tr> </table> </form> |
Servlet过滤器 用于编码过滤器
|
package tools;
import java.io.IOException;
import javax.servlet.Filter; import javax.servlet.FilterChain; import javax.servlet.FilterConfig; import javax.servlet.ServletException; import javax.servlet.ServletRequest; import javax.servlet.ServletResponse; import javax.servlet.annotation.WebFilter; import javax.servlet.annotation.WebInitParam;
/** * @function 编码过滤器 */ @WebFilter( urlPatterns = { "/*" }, initParams = { @WebInitParam(name = "encoding", value = "UTF-8") }) //配置过滤器
public class CharacterEncodingFilter implements Filter{
protected String encoding = null; protected FilterConfig filterConfig = null;
public void init(FilterConfig filterConfig) throws ServletException { this.filterConfig = filterConfig; this.encoding = filterConfig.getInitParameter("encoding"); }
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException { if (encoding != null) { request.setCharacterEncoding(encoding); response.setContentType("text/html; charset="+encoding); } chain.doFilter(request, response); }
public void destroy() { this.encoding = null; this.filterConfig = null; } } |
逻辑结构图如下所示：
浙公网安备 33010602011771号