1.工程目录：
2.数据库（mysql）：
/**员工表*/
create table employee
(
emp_no varchar(20) primary key ,/** 员工编号 */
emp_name varchar(50) , /** 员工姓名 */
mobile varchar(11),/** 手机号码 */
sex int,/** 性别 */
birthday varchar(20),/** 出生日期 */
salary int,/** 薪水(月薪) */
dep_name varchar(50),/** 部门名称 */
homeplace varchar(50) /** 籍贯 */
);3.代码：
package com.servlet01.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SqlConn {
/**
* 数据库连接
*/
public final static Connection getConn() {
Connection conn = null;
try {
// 1.加载MYSQL数据库驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.设置数据库账户,即用户名及密码
String user = "root";
String password = "123456";
// 3.设置数据库地址或URL
String url = "jdbc:mysql://127.0.0.1:3306/j2ee?useUnicode=true&characterEncoding=utf-8";
// 4.获取数据库连接,返回数据连接
conn = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
// 发生异常后返回null
return conn;// 返回数据库连接
}
/**
* 关闭资源
*
* @param rs
* 结果集
* @param ps
* Statement
* @param conn
* 数据库连接
*/
public final static void closeAll(ResultSet rs, Statement ps, Connection conn) {
try {
if (null != rs) {
rs.close();
}
if (null != ps) {
ps.close();
}
if (null != conn) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
package com.servlet01.model;
public class Employee {
private String empNo; // 员工编号
private String empName; // 员工姓名
private String mobile; // 手机号码
private int sex; // 性别(1男,2女)
private String birthday; // 出生日期
private Integer salary; // 薪水(月薪)
private String depName; // 部门名称
private String homeplace; // 籍贯员工
public Employee() {
super();
}
public Employee(String empNo, String empName, String mobile, int sex, String birthday, Integer salary,
String depName, String homeplace) {
super();
this.empNo = empNo;
this.empName = empName;
this.mobile = mobile;
this.sex = sex;
this.birthday = birthday;
this.salary = salary;
this.depName = depName;
this.homeplace = homeplace;
}
public String getEmpNo() {
return empNo;
}
public void setEmpNo(String empNo) {
this.empNo = empNo;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public String getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
public Integer getSalary() {
return salary;
}
public void setSalary(Integer salary) {
this.salary = salary;
}
public String getDepName() {
return depName;
}
public void setDepName(String depName) {
this.depName = depName;
}
public String getHomeplace() {
return homeplace;
}
public void setHomeplace(String homeplace) {
this.homeplace = homeplace;
}
}
package com.servlet01.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.servlet01.model.Employee;
public class EmpDao {
/**
* 根据员工编号查询员工信息
*
* @param empNo
* 员工编号
* @return
*/
public Employee queryEmp(String empNo) {
Employee emp = null;
// 1.连接数据库
Connection conn = SqlConn.getConn();
// 2.设置PreparedStatement和ResultSet
PreparedStatement ps = null;
ResultSet rs = null;
// 3.设置SQL查询语句
String sql = "select emp_no, emp_name, mobile, sex, birthday, salary, dep_name, homeplace from employee where emp_no = ?";
try {
// 4.准备查询
ps = conn.prepareStatement(sql);
ps.setString(1, empNo);
// 5.处理结果集
rs = ps.executeQuery();
while (rs.next()) {
String empName = rs.getString("emp_name");
String mobile = rs.getString("mobile");
int sex = rs.getInt("sex");
String birthday = rs.getString("birthday");
int salary = rs.getInt("salary");
String depName = rs.getString("dep_name");
String homeplace = rs.getString("homeplace");
emp = new Employee(empNo, empName, mobile, sex, birthday, salary, depName, homeplace);
}
} catch (SQLException e) {
e.printStackTrace();
}
// 6.关闭资源
SqlConn.closeAll(rs, ps, conn);
return emp;
}
/**
* 查询所有员工信息
*
* @return
*/
public List<Employee> queryEmpAll() {
List<Employee> emps = new ArrayList<>();
// 1.连接数据库
Connection conn = SqlConn.getConn();
// 2.设置PreparedStatement和ResultSet
PreparedStatement ps = null;
ResultSet rs = null;
// 3.设置SQL查询语句
String sql = "select emp_no, emp_name, mobile, sex, birthday, salary, dep_name, homeplace from employee";
// 4.准备查询
try {
ps = conn.prepareStatement(sql);
// 5.处理结果集
rs = ps.executeQuery();
while (rs.next()) {
String empNo = rs.getString("emp_no");
String empName = rs.getString("emp_name");
String mobile = rs.getString("mobile");
int sex = rs.getInt("sex");
String birthday = rs.getString("birthday");
int salary = rs.getInt("salary");
String depName = rs.getString("dep_name");
String homeplace = rs.getString("homeplace");
Employee emp = new Employee(empNo, empName, mobile, sex, birthday, salary, depName, homeplace);
emps.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
}
// 6.关闭资源
SqlConn.closeAll(rs, ps, conn);
return emps;
}
/**
* 添加员工
*
* @param employee
*
* @return 1成功,-1异常,0失败
*/
public int addEmp(Employee employee) {
int result = 0;
String empNo = employee.getEmpNo();
String empName = employee.getEmpName();
String mobile = employee.getMobile();
int sex = employee.getSex();
String birthday = employee.getBirthday();
int salary = employee.getSalary();
String depName = employee.getDepName();
String homeplace = employee.getHomeplace();
// 1.连接数据库
Connection conn = SqlConn.getConn();
// 2.设置PreparedStatement和ResultSet
PreparedStatement ps = null;
ResultSet rs = null;
// 3.设置SQL添加语句
String sql = "insert into employee(emp_no,emp_name,mobile,sex,birthday,salary,dep_name,homeplace)values(?,?,?,?,?,?,?,?)";
// 4.预处理SQL语句
try {
ps = conn.prepareStatement(sql);
// 5.给占位符赋值
ps.setString(1, empNo);
ps.setString(2, empName);
ps.setString(3, mobile);
ps.setInt(4, sex);
ps.setString(5, birthday);
ps.setInt(6, salary);
ps.setString(7, depName);
ps.setString(8, homeplace);
// 6.执行,并返回影响行数
int rows = ps.executeUpdate();
if (rows >= 1) {
result = 1;
}
} catch (SQLException e) {
result = -1;
e.printStackTrace();
}
// 7.关闭资源
SqlConn.closeAll(rs, ps, conn);
return result;
}
/**
* 删除员工
*
* @param empNo
* 员工编号
* @return 1成功,0失败, -1异常
*/
public int delEmp(String empNo) {
int result = 0;
// 1.连接数据库
Connection conn = SqlConn.getConn();
// 2.设置PreparedStatement和ResultSet
PreparedStatement ps = null;
ResultSet rs = null;
// 3.设置SQL删除语句
String sql = "delete from employee where emp_no = ?";
// 4.预处理SQL语句
try {
ps = conn.prepareStatement(sql);
// 5.给占位符赋值
ps.setString(1, empNo);
// 6.执行, 并返回影响行数
int rows = ps.executeUpdate();
result = rows;
} catch (SQLException e) {
result = -1;
e.printStackTrace();
}
// 7.关闭资源
SqlConn.closeAll(rs, ps, conn);
return result;
}
/**
* 修改员工信息
*
* @param employee
* @return 1成功, 0失败, -1异常
*/
public int updateEmp(Employee employee) {
int result = 0;
String empNo = employee.getEmpNo();
String empName = employee.getEmpName();
String mobile = employee.getMobile();
int sex = employee.getSex();
String birthday = employee.getBirthday();
int salary = employee.getSalary();
String depName = employee.getDepName();
String homeplace = employee.getHomeplace();
// 1.连接数据库
Connection conn = SqlConn.getConn();
// 2.设置PreparedStatement和ResultSet
PreparedStatement ps = null;
ResultSet rs = null;
// 3.设置SQL修改语句
String sql = "update employee set emp_name = ?, mobile = ?, sex = ?, birthday = ?, salary = ?, dep_name = ?, homeplace = ? where emp_no = ?";
// 4.预处理SQL语句
try {
ps = conn.prepareStatement(sql);
// 5.给占位符赋值
ps.setString(1, empName);
ps.setString(2, mobile);
ps.setInt(3, sex);
ps.setString(4, birthday);
ps.setInt(5, salary);
ps.setString(6, depName);
ps.setString(7, homeplace);
ps.setString(8, empNo);
// 6.执行, 并获取影响行数
int rows = ps.executeUpdate();
result = rows;
} catch (SQLException e) {
result = -1;
e.printStackTrace();
}
// 7.关闭资源
SqlConn.closeAll(rs, ps, conn);
return result;
}
/**
* 根据关键字查询员工信息
*
* @param keyWord
* @return
*/
public List<Employee> queryEmpByKey(String keyWord) {
List<Employee> emps = new ArrayList<>();
// 1.连接数据库
Connection conn = SqlConn.getConn();
// 2.设置PreparedStatement和ResultSet
PreparedStatement ps = null;
ResultSet rs = null;
// 3.设置SQL查询语句
String sql = "select emp_no, emp_name, mobile, sex, birthday, salary, dep_name, homeplace from employee "
+ "where emp_no like ? or emp_name like ? or mobile like ? or sex like ? or birthday like ? or salary like ? or dep_name like ? or homeplace like ?";
try {
// 4.准备查询
ps = conn.prepareStatement(sql);
ps.setString(1, "%" + keyWord + "%");
ps.setString(2, "%" + keyWord + "%");
ps.setString(3, "%" + keyWord + "%");
if(keyWord.equals("男")) {
ps.setString(4, "%" + 1 + "%");
} else if (keyWord.equals("女")) {
ps.setString(4, "%" + 2 + "%");
} else {
ps.setString(4, "%" + keyWord + "%");
}
ps.setString(5, "%" + keyWord + "%");
ps.setString(6, "%" + keyWord + "%");
ps.setString(7, "%" + keyWord + "%");
ps.setString(8, "%" + keyWord + "%");
// 5.处理结果集
rs = ps.executeQuery();
while (rs.next()) {
String empNo = rs.getString("emp_no");
String empName = rs.getString("emp_name");
String mobile = rs.getString("mobile");
int sex = rs.getInt("sex");
String birthday = rs.getString("birthday");
int salary = rs.getInt("salary");
String depName = rs.getString("dep_name");
String homeplace = rs.getString("homeplace");
Employee emp = new Employee(empNo, empName, mobile, sex, birthday, salary, depName, homeplace);
emps.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
}
// 6.关闭资源
SqlConn.closeAll(rs, ps, conn);
return emps;
}
}
package com.servlet01.servlet;
import java.io.IOException;
import java.util.List;
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 javax.servlet.http.HttpSession;
import com.servlet01.dao.EmpDao;
import com.servlet01.model.Employee;
@WebServlet(value = "/showEmp")
public class ShowEmpAll extends HttpServlet {
@Override
protected void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
HttpSession session = request.getSession();
session.setAttribute("uanme", "uname");
// 创建执行查询方法的 EmpDao
EmpDao empdao = new EmpDao();
// 关键字
String key = request.getParameter("key");
// 要显示的员工集合(默认显示全部)
List<Employee> emps = empdao.queryEmpAll();
// 显示筛选的员工集合
if (key != null && key != "") {
emps = empdao.queryEmpByKey(key.trim());
}
// 将对象数组添加request中去
request.setAttribute("emps", emps);
request.setAttribute("key", key);
request.getRequestDispatcher("show_emp_all.jsp").forward(request, response);
}
}
package com.servlet01.servlet;
import java.io.IOException;
import java.io.PrintWriter;
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 com.servlet01.dao.EmpDao;
@WebServlet(value = "/delEmp")
public class DelEmpServlet extends HttpServlet {
@Override
protected void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 获取参数
String empNo = request.getParameter("empNo");
EmpDao empdo = new EmpDao();
int result = empdo.delEmp(empNo);
if (result == -1) {
PrintWriter out = response.getWriter();
out.println("删除操作异常!");
} else {
//
request.getSession().invalidate();
response.sendRedirect("index.jsp");
}
}
}
package com.servlet01.servlet;
import java.io.IOException;
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 com.servlet01.dao.EmpDao;
import com.servlet01.model.Employee;
@WebServlet(value = "/addEmp")
public class AddEmpServlet extends HttpServlet {
@Override
protected void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 获取添加页面form表单数据
String empNo = request.getParameter("empNo");
String empName = request.getParameter("empName");
String mobile = request.getParameter("mobile");
int sex = Integer.parseInt(request.getParameter("sex"));
String birthday = request.getParameter("birthday");
int salary = Integer.parseInt(request.getParameter("salary"));
String depName = request.getParameter("depName");
String homeplace = request.getParameter("homeplace");
// 员工对象
Employee employee = new Employee(empNo, empName, mobile, sex, birthday, salary, depName, homeplace);
// 添加
EmpDao empdao = new EmpDao();
int result = empdao.addEmp(employee);
// 重定向
response.sendRedirect("add_emp_result.jsp?result=" + result);
}
}
package com.servlet01.servlet;
import java.io.IOException;
import java.io.PrintWriter;
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 com.servlet01.dao.EmpDao;
import com.servlet01.model.Employee;
@WebServlet(value = "/updateEmp")
public class UpdateEmpServlet extends HttpServlet {
@Override
protected void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 获取请求页面标识
String req = request.getParameter("req");
if (req.equals("1")) {
request.setCharacterEncoding("utf-8");
// 获取参数
String empNo = request.getParameter("empNo");
EmpDao empdo = new EmpDao();
// 查询员工
Employee emp = empdo.queryEmp(empNo);
if(emp == null) {
PrintWriter out = response.getWriter();
out.print("该用户不存在或已被删除!");
} else {
// 将对象存放到request中
request.setAttribute("emp", emp);
// 跳转
request.getRequestDispatcher("update_emp.jsp").forward(request, response);
}
}
if (req.equals("2")) {
// 获取添加页面form表单数据
String empNo = request.getParameter("empNo");
String empName = request.getParameter("empName");
String mobile = request.getParameter("mobile");
int sex = Integer.parseInt(request.getParameter("sex"));
String birthday = request.getParameter("birthday");
int salary = Integer.parseInt(request.getParameter("salary"));
String depName = request.getParameter("depName");
String homeplace = request.getParameter("homeplace");
// 员工对象
Employee employee = new Employee(empNo, empName, mobile, sex, birthday, salary, depName, homeplace);
// 添加
EmpDao empdao = new EmpDao();
int result = empdao.updateEmp(employee);
// 获取输出流
PrintWriter out = response.getWriter();
if (result == 1) {
out.print("<h4>修改成功!</h4>");
} else if (result == -1) {
out.print("<h4>出现异常,修改失败!</h4>");
} else {
out.print("<h4>由于未知原因,修改失败!</h4>");
}
response.sendRedirect("showEmp");
}
}
}
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>index</title>
</head>
<body>
<form action="showEmp" method="post">
<div>uname: <input type="text" name="uname" />
<input type="submit" value="提交" /></div>
</form>
</body>
</html><%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.util.List" %>
<%@ page import="com.servlet01.model.Employee" %>
<%@ page import="com.servlet01.dao.EmpDao" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>查询所有员工信息</title>
</head>
<body>
<form action="showEmp" method="post">
<div>
<input type="text" name="key" value="${key}" />
<input type="submit" value="查询" />
</div>
</form>
<table border="1">
<tr>
<th>编号</th>
<th>姓名</th>
<th>手机号码</th>
<th>性别</th>
<th>出生日期</th>
<th>薪水</th>
<th>部门名称</th>
<th>籍贯</th>
<th>操作</th>
</tr>
<c:forEach items="${requestScope.emps}" var="e">
<tr>
<td>${e.empNo }</td>
<td>${e.empName }</td>
<td>${e.mobile }</td>
<td>${e.sex == 1 ? "男" : "女" }</td>
<td>${e.birthday }</td>
<td>${e.salary }</td>
<td>${e.depName }</td>
<td>${e.homeplace }</td>
<td><a href="updateEmp?empNo=${e.empNo}&req=1">修改</a>
<a href="delEmp?empNo=${e.empNo}">删除</a></td>
</tr>
</c:forEach>
</table>
<a href="add_emp.jsp">添加新员工</a>
</body>
</html><%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>添加新员工</title>
</head>
<body>
<p><a href="showEmp">返回</a></p>
<form action="addEmp" method="post">
<div>员工编号: <input type="text" name="empNo" /></div>
<div>员工姓名: <input type="text" name="empName" /></div>
<div>手机号: <input type="text" name="mobile" /></div>
<div>性别: <label><input type="radio" name="sex" value="1" checked="checked" /> 男</label>
<label><input type="radio" name="sex" value="2" /> 女</label>
</div>
<div>出生日期: <input type="text" name="birthday" /></div>
<div>薪水: <input type="text" name="salary" value="0" /></div>
<div>部门: <select name="depName" >
<option value="后勤部" selected="selected">后勤部</option>
<option value="行政部">行政部</option>
<option value="开发部">开发部</option>
<option value="人事部">人事部</option>
<option value="市场部">市场部</option>
<option value="财务部">财务部</option>
</select>
</div>
<div>籍贯: <input type="text" name="homeplace" /></div>
<div><input type="submit" value="提交" />
<input type="reset" value="重置" /></div>
</form>
</body>
</html><%@page import="com.servlet01.model.Employee"%>
<%@page import="com.servlet01.dao.EmpDao"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>添加新员工处理页面</title>
</head>
<body>
<h4>
<c:choose>
<c:when test="${param.result == 1 }">
<c:out value="添加成功!"></c:out>
</c:when>
<c:when test="${param.result == -1 }">
<c:out value="出现异常,添加失败!"></c:out>
</c:when>
<c:otherwise>
<c:out value="由于未知原因,添加失败!"></c:out>
</c:otherwise>
</c:choose>
</h4>
<h4>
<a href="add_emp.jsp">继续添加</a>
<a href="showEmp">查询所有员工</a>
</h4>
</body>
</html><%@page import="com.servlet01.model.Employee"%>
<%@page import="com.servlet01.dao.EmpDao"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>修改员工信息</title>
</head>
<body>
<p><a href="showEmp">返回</a></p>
<form action="updateEmp?req=2" method="post">
<div><input type="text" name="empNo" value="${emp.empNo}" /></div>
<div><input type="text" name="empName" value="${emp.empName}" /></div>
<div><input type="text" name="mobile" value="${emp.mobile}" /></div>
<div>
<label><input type="radio" name="sex" value="1" ${emp.sex == 1?'checked':' '} /> 男</label>
<label><input type="radio" name="sex" value="2" ${emp.sex == 2?'checked':' '} /> 女</label>
</div>
<div><input type="text" name="birthday" value="${emp.birthday}" /></div>
<div><input type="text" name="salary" value="${emp.salary}" /></div>
<div><select name="depName" >
<option value="后勤部" ${emp.depName == '后勤部'?'selected':''}>后勤部</option>
<option value="行政部" ${emp.depName == '行政部'?'selected':''}>行政部</option>
<option value="开发部" ${emp.depName == '开发部'?'selected':''}>开发部</option>
<option value="人事部" ${emp.depName == '人事部'?'selected':''}>人事部</option>
<option value="市场部" ${emp.depName == '市场部'?'selected':''}>市场部</option>
<option value="财务部" ${emp.depName == '财务部'?'selected':''}>财务部</option>
</select>
</div>
<div><input type="text" name="homeplace" value="${emp.homeplace }" /></div>
<div><input type="submit" value="提交" />
<input type="reset" value="重置" /></div>
</form>
</body>
</html>
223
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
