DBUtil.java 连接数据库
package com.ambow.util;
import java.sql.*;
public class DBUtil {
private static String driverName = "com.mysql.cj.jdbc.Driver";
private static String url = "jdbc:mysql://127.0.0.1:3306/ambow?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8";
private static String username = "root";
private static String password = "root";
//获取连接
public static Connection getConn() {
Connection connection = null;
try {
Class.forName(driverName);
connection = DriverManager.getConnection(url,username,password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//释放资源
public static void closeAll(Connection conn, Statement stmt, ResultSet rs){
if (rs!= null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Employee.java
package com.ambow.pojo;
public class Employee {
private int empid;
private String empname;
private String empsex;
private int empage;
private String empdepart;
public Employee(){
}
public int getEmpid() {
return empid;
}
public void setEmpid(int empid) {
this.empid = empid;
}
public String getEmpname() {
return empname;
}
public void setEmpname(String empname) {
this.empname = empname;
}
public String getEmpsex() {
return empsex;
}
public void setEmpsex(String empsex) {
this.empsex = empsex;
}
public int getEmpage() {
return empage;
}
public void setEmpage(int empage) {
this.empage = empage;
}
public String getEmpdepart() {
return empdepart;
}
public void setEmpdepart(String empdepart) {
this.empdepart = empdepart;
}
public Employee(String empname, String empsex, int empage, String empdepart) {
this.empname = empname;
this.empsex = empsex;
this.empage = empage;
this.empdepart = empdepart;
}
public Employee(int empid, String empname, String empsex, int empage, String empdepart) {
this.empid = empid;
this.empname = empname;
this.empsex = empsex;
this.empage = empage;
this.empdepart = empdepart;
}
}
list.java 列表or查全部的页面
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<meta charset="UTF-8">
<title>雇员列表</title>
<style type="text/css">
table{
border-collapse: collapse;
width: 40%;
}
td{
text-align: center;
}
a{
text-decoration: none;
}
</style>
</head>
<body>
<center>
<div>
<h1>雇员管理系统</h1>
<a href="addEmp.html">添加</a>
<table border="1">
<tr>
<th>编号</th>
<th>姓名</th>
<th>性别</th>
<th>年龄</th>
<th>部门</th>
<th>操作</th>
</tr>
<c:forEach items="${list}" var="emp">
<tr>
<td>${emp.empid}</td>
<td>${emp.empname}</td>
<td>${emp.empsex}</td>
<td>${emp.empage}</td>
<td>${emp.empdepart}</td>
<td><a href="getEmp?id=${emp.empid}">修改</a>/<a href="dropEmp?id=${emp.empid}" onclick="return window.confirm('确定 删 除 该电影吗?')">删除</a></td>
</tr>
</c:forEach>
</table>
</div>
</center>
</body>
</html>
addEmp.html 添加的页面
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>添加雇员</title>
<style type="text/css">
td{
text-align: center;
}
table{
border-collapse: collapse;
}
</style>
</head>
<body>
<center>
<h3>增加雇员</h3>
<form action="addemp" method="post">
<table width="40%" border="1">
<tr>
<td>雇员姓名:</td>
<td><input type="text" name="empName"></td>
</tr>
<tr>
<td>雇员性别:</td>
<td>
<input type="radio" name="empSex" value="男" checked>
<input type="radio" name="empSex" value="女">
</td>
</tr>
<tr>
<td>雇员年龄:</td>
<td><input type="text" name="empAge"></td>
</tr>
<tr>
<td>所属部门:</td>
<td><input type="text" name="empDepart"></td>
</tr>
<tr>
<td colspan="2">
<input type="submit" value="添加" class="btn">
<input type="reset" value="取消" class="btn">
</td>
</tr>
</table>
</form>
</center>
</body>
</html>
updateEmp.jsp 修改的页面
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>修改</title>
<style type="text/css">
td{
text-align: center;
}
table{
border-collapse: collapse;
}
</style>
</head>
<body>
<center>
<h3>修改雇员</h3>
<form action="Modify" method="post">
<table width="40%" border="1">
<tr>
<td>雇员编号:</td>
<td><input type="text" name="empid" value="${emp.empid}" readonly></td>
</tr>
<tr>
<td>雇员姓名:</td>
<td><input type="text" name="empName" value="${emp.empname}"></td>
</tr>
<tr>
<td>雇员性别:</td>
<td>
<c:if test="${emp.empsex == '男'}">
<input type="radio" name="empSex" value="男" checked>
<input type="radio" name="empSex" value="女">
</c:if>
<c:if test="${emp.empsex == '女'}">
<input type="radio" name="empSex" value="男">
<input type="radio" name="empSex" value="女" checked>
</c:if>
</td>
</tr>
<tr>
<td>雇员年龄:</td>
<td><input type="text" name="empAge" value="${emp.empage}"></td>
</tr>
<tr>
<td>所属部门:</td>
<td><input type="text" name="empDepart" value="${emp.empdepart}"></td>
</tr>
<tr>
<td colspan="2">
<input type="submit" value="修改" class="btn">
<input type="reset" value="取消" class="btn">
</td>
</tr>
</table>
</form>
</center>
</body>
</html>
业务层实现类接口
package com.ambow.service;
import com.ambow.pojo.Employee;
import java.util.ArrayList;
业务层实现类
public interface EmployeeService {
//添加
public int addEmployee(Employee employee);
//查询
public ArrayList<Employee> getAllEmployees();
//找id
Employee getEmployees(String id);
//修改
int modifyEmployee(Employee employee);
//删除
int dropEmployee(String id);
EmployeeServicelmpl.java 业务层实现类
package com.ambow.service.impl;
import com.ambow.dao.EmployeeDao;
import com.ambow.dao.lmpl.EmployeeDaolmpl;
import com.ambow.pojo.Employee;
import com.ambow.service.EmployeeService;
import java.sql.SQLException;
import java.util.ArrayList;
//业务层实现类
public class EmployeeServicelmpl implements EmployeeService {
private EmployeeDao employeeDao = new EmployeeDaolmpl();
//添加
@Override
public int addEmployee(Employee employee) {
//调dao添加方法
int row = 0;
try {
row = employeeDao.insertEmp(employee);
}catch(SQLException e){
e.printStackTrace();
}
return row;
}
//查询
@Override
public ArrayList<Employee> getAllEmployees() {
ArrayList<Employee> list = null;
try {
list = employeeDao.selectEmployees();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
//找id
@Override
public Employee getEmployees(String id) {
Employee employee = null;
try {
employee = employeeDao.selectEmpByid(id);
} catch (SQLException e) {
e.printStackTrace();
}
return employee;
}
//修改
@Override
public int modifyEmployee(Employee employee) {
int i = 0;
try {
i = employeeDao.updateEmployee(employee);
}catch (SQLException e){
e.printStackTrace();
}
return i;
}
//删除
@Override
public int dropEmployee(String id) {
int i = 0;
try {
i = employeeDao.deleteEmployee(id);
}catch (SQLException e){
e.printStackTrace();
}
return i;
}
}
数据访问层接口
package com.ambow.dao;
import com.ambow.pojo.Employee;
import java.sql.SQLException;
import java.util.ArrayList;
//数据访问层
public interface EmployeeDao {
//添
public int insertEmp(Employee employee) throws SQLException;
//查
ArrayList<Employee> selectEmployees() throws SQLException;
//找id
Employee selectEmpByid(String id) throws SQLException;
//改
int updateEmployee(Employee employee) throws SQLException;
//删
int deleteEmployee(String id) throws SQLException;
}
EmployeeDaolmpl.java 数据访问层实现类
package com.ambow.dao.lmpl;
import com.ambow.dao.EmployeeDao;
import com.ambow.pojo.Employee;
import com.ambow.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.SQLException;
import java.util.ArrayList;
public class EmployeeDaolmpl implements EmployeeDao {
private QueryRunner qr = new QueryRunner();
//添
@Override
public int insertEmp(Employee employee) throws SQLException {
String sql = "insert into employee values (null,?,?,?,?)";
int row = qr.update(DBUtil.getConn(),sql,employee.getEmpname(),employee.getEmpsex(),employee.getEmpage(),employee.getEmpdepart());
return row;
}
//查
@Override
public ArrayList<Employee> selectEmployees() throws SQLException {
String sql = "select * from employee";
ArrayList<Employee> list = (ArrayList<Employee>)qr.query(DBUtil.getConn(),sql,new BeanListHandler<Employee>(Employee.class));
return list;
}
//找id
@Override
public Employee selectEmpByid(String id) throws SQLException {
String sql = "select * from employee where empid = ?";
Employee employee = qr.query(DBUtil.getConn(),sql,new BeanHandler<Employee>(Employee.class),id);
return employee;
}
//改
@Override
public int updateEmployee(Employee employee) throws SQLException {
String sql = "update employee set empname = ?,empsex = ?,empage = ?,empdepart = ? where empid = ?";
int row = qr.update(DBUtil.getConn(),sql,employee.getEmpname(),employee.getEmpsex(),employee.getEmpage(),employee.getEmpdepart(),employee.getEmpid());
return row;
}
//删
@Override
public int deleteEmployee(String id) throws SQLException {
String sql = "delete from employee where empid = ?";
int row = qr.update(DBUtil.getConn(),sql,id);
return row;
}
}
AddEmpServlet.java 添加的Servlet
package com.ambow.controller;
import com.ambow.pojo.Employee;
import com.ambow.service.EmployeeService;
import com.ambow.service.impl.EmployeeServicelmpl;
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;
@WebServlet("/addemp")
public class AddEmpServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
//获取表单参数
String empName = request.getParameter("empName");
String empSex = request.getParameter("empSex");
int empAge = Integer.parseInt(request.getParameter("empAge"));
String empDepart = request.getParameter("empDepart");
Employee employee = new Employee(empName,empSex,empAge,empDepart);
//调添加方法
EmployeeService employeeService = new EmployeeServicelmpl();
int row = employeeService.addEmployee(employee);
if(row != 0){
//成功
request.getRequestDispatcher("/query").forward(request,response);
}else {
//失败
response.sendRedirect("addEmp.html");
}
}
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request,response);
}
}
DropEmployeeServlet.java 删除的Servlet
package com.ambow.controller;
import com.ambow.service.EmployeeService;
import com.ambow.service.impl.EmployeeServicelmpl;
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;
@WebServlet("/dropEmp")
public class DropEmployeeServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//获取参数
String id = request.getParameter("id");
//调方法
EmployeeService employeeService = new EmployeeServicelmpl();
//执行删除
int row = employeeService.dropEmployee(id);
//跳转列表
response.sendRedirect("query");
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request,response);
}
}
GetEmployeeServlet.java 找id的Servlet (修改用)
package com.ambow.controller;
import com.ambow.pojo.Employee;
import com.ambow.service.EmployeeService;
import com.ambow.service.impl.EmployeeServicelmpl;
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;
// 找id (修改用)
@WebServlet("/getEmp")
public class GetEmployeeServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//核心
//获取参数
String id = request.getParameter("id");
//做处理
EmployeeService employeeService = new EmployeeServicelmpl();
Employee employee = employeeService.getEmployees(id);
//跳转页面调度
request.setAttribute("emp",employee);
request.getRequestDispatcher("updateEmp.jsp").forward(request,response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request,response);
}
}
ModifyEmployeeServlet.java 修改的Servlet
package com.ambow.controller;
import com.ambow.pojo.Employee;
import com.ambow.service.EmployeeService;
import com.ambow.service.impl.EmployeeServicelmpl;
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;
@WebServlet("/Modify")
public class ModifyEmployeeServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
int empid = Integer.parseInt(request.getParameter("empid"));
String empName = request.getParameter("empName");
String empSex = request.getParameter("empSex");
int empAge = Integer.parseInt(request.getParameter("empAge"));
String empDepart = request.getParameter("empDepart");
//封装
Employee employee = new Employee(empid,empName,empSex,empAge,empDepart);
//执行修改
EmployeeService employeeService = new EmployeeServicelmpl();
int row = employeeService.modifyEmployee(employee);
//跳转
if (row != 0){
//成功
response.sendRedirect("query");
}else {
//失败
request.getRequestDispatcher("updateEmp.jsp").forward(request,response);
}
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request,response);
}
}
QueryServlet.java 查全部列表的Servlet
package com.ambow.controller;
import com.ambow.pojo.Employee;
import com.ambow.service.EmployeeService;
import com.ambow.service.impl.EmployeeServicelmpl;
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.ArrayList;
@WebServlet("/query")
public class QueryServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//请求处理代码 查询
EmployeeService employeeService = new EmployeeServicelmpl();
ArrayList<Employee> list = employeeService.getAllEmployees();
//跳转
request.setAttribute("list",list);
request.getRequestDispatcher("list.jsp").forward(request,response);
}
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request,response);
}
}