1.首先创建好如图的数据库表格
2.添加依赖
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>2.5</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.29</version>
</dependency>
3.写各种实现类
3.1Empservlet类
public class EmpServlet extends HttpServlet {
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws IOException{
//1. 设置编码
resp.setContentType("text/html;charset=UTF-8");
req.setCharacterEncoding("utf-8");
// 2. 判断操作的类型
if(req.getServletPath().equals("/add.do")){
// 增加
try {
add(req , resp);
} catch (Exception e) {
e.printStackTrace();
}
}else if(req.getServletPath().equals("/list.do")){
// 查询
getEmps(resp);
}else if(req.getServletPath().equals("/update.do")){
// 更新
try {
update(req,resp);
} catch (Exception e) {
e.printStackTrace();
}
}else if(req.getServletPath().equals("/delete.do")){
// 删除
try {
delete(req,resp);
} catch (Exception e) {
e.printStackTrace();
}
}
}
private EmpService service = new EmpService();
//增加
private void add(HttpServletRequest request , HttpServletResponse response) throws Exception {
String emp_name = request.getParameter("emp_name");
String emp_job = request.getParameter("emp_job");
String emp_hirdate = request.getParameter("emp_hirdate");
String emp_salary = request.getParameter("emp_salary");
String emp_manager = request.getParameter("emp_manager");
String emp_detno = request.getParameter("emp_detno");
//类型转换
Double esalary1=0.0;
Date ehirdate1 =new Date();
Integer emanager1=0;
Integer edetno1=0;
if (emp_salary!=null) {
esalary1 = Double.parseDouble(emp_salary);
}
if (emp_hirdate!=null) {
ehirdate1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(emp_hirdate);
}
if (emp_detno!= null) {
edetno1 = Integer.parseInt(emp_detno);
}
if (emp_manager !=null) {
emanager1 = Integer.parseInt(emp_manager);
}
//面向对象的操作:封装对象
Emp emp= new Emp();
emp.setSalary(esalary1);
emp.setDetno(edetno1);
emp.setJob(emp_job);
emp.setManager(emanager1);
emp.setName(emp_name);
emp.setHirdate(ehirdate1);
//添加到数据库
service.addEmp(emp);
response.sendRedirect(request.getContextPath() + "/list.do");
}
//删除
private void delete(HttpServletRequest request , HttpServletResponse response)throws Exception{
String no = request.getParameter("emp_no");
if (no==null) {
response.sendRedirect(request.getContextPath()+"/list.do");
return;
}else{
Integer no1 = Integer.parseInt(no);
Boolean aBoolean = service.deleteByNo(no1);
// 删除完成后,重定向到查询的界面,用户根据查询结果,增加判断是否删除成功
response.sendRedirect(request.getContextPath()+"/list.do");
}
}
//修改
private void update(HttpServletRequest request , HttpServletResponse response) throws Exception{
String emp_no = request.getParameter("emp_no");
String emp_name = request.getParameter("emp_name");
String emp_job = request.getParameter("emp_job");
String emp_hirdate = request.getParameter("emp_hirdate");
String emp_salary = request.getParameter("emp_salary");
String emp_manager = request.getParameter("emp_manager");
String emp_detno = request.getParameter("emp_detno");
//类型转换
Integer eno1 =0;
Double esalary1=0.0;
Date ehirdate1 =new Date();
Integer emanager1=0;
Integer edetno1=0;
if (emp_no!=null) {
eno1 = Integer.parseInt(emp_no);
}
if (emp_salary!=null) {
esalary1 = Double.parseDouble(emp_salary);
}
if (emp_hirdate!=null) {
ehirdate1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(emp_hirdate);
}
if (emp_detno!= null) {
edetno1 = Integer.parseInt(emp_detno);
}
if (emp_manager !=null) {
emanager1 = Integer.parseInt(emp_manager);
}
//面向对象的操作:封装对象
Emp emp= new Emp();
emp.setNo(eno1);
emp.setSalary(esalary1);
emp.setDetno(edetno1);
emp.setJob(emp_job);
emp.setManager(emanager1);
emp.setName(emp_name);
emp.setHirdate(ehirdate1);
//调用业务层的更新方法,实现更新数据库的数据
service.updateEmp(emp);
//响应结果
response.sendRedirect(request.getContextPath()+"/list.do");
}
//查询
private void getEmps(HttpServletResponse response) throws IOException {
// 1. 查询数据
List<Emp> list = service.getEmps();
// 2. 拼展示数据的html字符串,以输出流的方式,响应到前端网页进行展示。
PrintWriter writer = response.getWriter();
writer.println("<!DOCTYPE html>\n" +
"<html>\n" +
"\t<head>\n" +
"\t\t<meta charset=\"utf-8\">\n" +
"\t\t<title></title>\n" +
"\t</head>\n" +
"\t<body>\n" +
"\t\t<a href=\"emp/addEmp.html\">增加员工</a>\n" +
"\t\t<hr>\n" +
"\t\t<h2>显示员工列表</h2>\n" +
"\t\t<table border=\"1\">\n" +
"\t\t\t<tr>\n" +
"\t\t\t\t<th>员工编号</th><th>员工名字</th><th>工作岗位</th><th>部门经理编号</th><th>入职日期</th><th>员工工资</th><th>部门编号</th><th>操作</th>\n" +
"\t\t\t</tr>");
// 循环每行数据
for (Emp e : list) {
String hirdate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(e.getHirdate());
writer.println("<tr>\n" +
"\t\t\t\t<td>"+e.getNo()+ "</td>" +
"<td>" + e.getName() + "</td>" +
"<td>" + e.getJob() + "</td>" +
"<td>" + e.getManager() + "</td>" +
"<td>" + hirdate + "</td>" +
"<td>" + e.getSalary() + "</td>" +
"<td>" + e.getDetno() + "</td>\n" +
"\t\t\t\t<td><a href=\"emp/updateEmp.html?" +
"emp_no="+e.getNo() +
"&emp_name="+e.getName()+
"&emp_job="+ e.getJob() +
"&emp_manager="+ e.getManager()+
"&emp_hirdate="+ hirdate +
"&emp_salary=" + e.getSalary() +
"&emp_detno=" + e.getDetno()+
"\""+">修改</a> <a href=\"delete.do?emp_no="+ e.getNo()+"\">删除</a></td>\n" +
"\t\t\t</tr>");
}
writer.println("</table>\n" + "\t</body>\n" + "</html>");
}}
3.2.EmpDao类
public class EmpDao {
//1.查询所有
public List<Emp> getEmps(){
Connection conn=DBUtil.getConn();
String sql ="SELECT * FROM emp";
try {
PreparedStatement preparedStatement = conn.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
List<Emp> list = new ArrayList<Emp>();
while (resultSet.next()) {
Emp emp = new Emp();
emp.setNo(resultSet.getInt(1));
emp.setName(resultSet.getString(2));
emp.setJob(resultSet.getString(3));
emp.setManager(resultSet.getInt(4));
emp.setHirdate(resultSet.getTimestamp(5));
emp.setSalary(resultSet.getDouble(6));
emp.setDetno(resultSet.getInt(7));
list.add(emp);
}
return list;
}catch (Exception e) {
e.printStackTrace();
}finally {
DBUtil.getClose(conn,null,null);
}
return null;
}
//修改
public int updateEmp( Emp emp ) throws IOException {
Connection conn = DBUtil.getConn();
String sql="update emp set emp_name=?,emp_job=?,emp_manager=?," +
"emp_hirdate=?,emp_salary=?,emp_detno=? where emp_no=?";
PreparedStatement preparedStatement=null;
try {
preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1, emp.getName());
preparedStatement.setString(2, emp.getJob());
preparedStatement.setInt(3, emp.getManager());
preparedStatement.setTimestamp(4,new java.sql.Timestamp(emp.getHirdate().getTime()));
preparedStatement.setDouble(5,emp.getSalary());
preparedStatement.setInt(6,emp.getDetno());
preparedStatement.setInt(7, emp.getNo());
int i=preparedStatement.executeUpdate();//返回值就是执行更新语句影响的行数
return i;
}catch(SQLException throwables){
throwables.printStackTrace();
}finally{
DBUtil.getClose(conn,preparedStatement,null);
}
return 0;
}
//根据id删除
public Integer deleteByNo(Integer no) throws SQLException{
Connection conn = DBUtil.getConn();
String sql = "delete from emp where emp_no = " + no;
int i =0;
PreparedStatement preparedStatement = conn.prepareStatement(sql);
i=preparedStatement.executeUpdate();
return i;
}
//增加
public Integer addEmp(Emp emp) throws Exception{
Connection conn =DBUtil.getConn();
String sql = "insert into emp values(null,?,?,?,?,?,?)";
int row=0;
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1, emp.getName());
preparedStatement.setString(2, emp.getJob());
preparedStatement.setInt(3, emp.getManager());
preparedStatement.setTimestamp(4,new java.sql.Timestamp(emp.getHirdate().getTime()));
preparedStatement.setDouble(5, emp.getSalary());
preparedStatement.setInt(6, emp.getDetno());
row = preparedStatement.executeUpdate();
return row;
}
}
3.3Emp类
public class Emp {
private int no;
private String name;
private String job;
private int manager;
private Date hirdate;
private double salary;
private int detno;
public Emp() {
}
public Emp(int no, String name, String job, int manager, Date hirdate, double salary, int detno) {
this.no = no;
this.name = name;
this.job = job;
this.manager = manager;
this.hirdate = hirdate;
this.salary = salary;
this.detno = detno;
}
public int getNo() {
return no;
}
public void setNo(int no) {
this.no = no;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public int getManager() {
return manager;
}
public void setManager(int manager) {
this.manager = manager;
}
public Date getHirdate() {
return hirdate;
}
public void setHirdate(Date hirdate) {
this.hirdate = hirdate;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public int getDetno() {
return detno;
}
public void setDetno(int detno) {
this.detno = detno;
}
}
3.4Empservice类
public class DBUtil {
//定义静态常量字符串表示连接的数据库的路径、用户名和密码
private final static String URL = "jdbc:mysql://localhost:3306/emp?serverTimezone=Asia/Shanghai";
private final static String USERNAME = "root";
private final static String PASSWORD = "1234";
//静态代码块用于加载一次驱动
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("加载驱动异常" + e);
}
}
/*
* 静态方法获取连接对象
* */
public static Connection getConn() {
Connection conn = null;
try {
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (SQLException e) {
System.out.println("连接异常" + e);
}
return conn;
}
/*
* 关闭对象
* */
public static void getClose(Connection conn, PreparedStatement pst, ResultSet rs) {
try {
if (conn != null)
conn.close();
if (pst != null)
pst.close();
if (rs != null)
rs.close();
} catch (SQLException e) {
System.out.println("关闭异常" + e);
}
}
}
3.5DBUtil类
public class DBUtil {
//定义静态常量字符串表示连接的数据库的路径、用户名和密码
private final static String URL = "jdbc:mysql://localhost:3306/emp?serverTimezone=Asia/Shanghai";
private final static String USERNAME = "root";
private final static String PASSWORD = "1234";
//静态代码块用于加载一次驱动
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("加载驱动异常" + e);
}
}
/*
* 静态方法获取连接对象
* */
public static Connection getConn() {
Connection conn = null;
try {
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (SQLException e) {
System.out.println("连接异常" + e);
}
return conn;
}
/*
* 关闭对象
* */
public static void getClose(Connection conn, PreparedStatement pst, ResultSet rs) {
try {
if (conn != null)
conn.close();
if (pst != null)
pst.close();
if (rs != null)
rs.close();
} catch (SQLException e) {
System.out.println("关闭异常" + e);
}
}
}
4.HTML页面
4.1addEmp.html
<body>
<a href="../list.do">查询员工信息</a>
<hr />
<h3>添加员工</h3>
<form action="../add.do" method="GET">
<label>员工的名字</label>
<input type="text" name="emp_name"/>
<br />
<label>工作岗位</label>
<input type="text" name="emp_job"/>
<br />
<label>部门经理</label>
<select name="emp_manager">
<option value="1">杜经理</option>
<option value="2">李经理</option>
<option value="3">王经理</option>
<option value="4">吴经理</option>
</select>
<br />
<label>入职日期</label>
<input type="datetime" name="emp_hiredate"/>
<br />
<label>工资</label>
<input type="text" name="emp_salary"/>
<br />
<label>部门:</label>
<select name="emp_detno">
<option value="1">财务部</option>
<option value="2">人事部</option>
<option value="3">法律部</option>
<option value="4">咨询部</option>
</select>
<br />
<input type="submit" value="增加"/>
<input type="reset" value="重置"/>
</form>
</body>
4.2 selectEmp.html
<body>
<a href="../list.do">增加员工</a>
<hr />
<h2>显示员工列表</h2>
<table border="1">
<tr>
<th>员工编号</th><th>员工名字</th><th>员工岗位</th><th>部门经理编号</th><th>员工入职时间</th><th>员工工资</th><th>部门编号</th><th>操作</th>
</tr>
<tr>
<th>1001</th><th>张三</th><th>咨询师</th><th>100</th><th>2022-9-20</th><th>5000</th><th>1</th>
<td><a href="#">修改</a> <a href="#">删除</a></td>
</tr>
<tr>
<th>1002</th><th>李四</th><th>HR</th><th>101</th><th>2022-9-20</th><th>6000</th><th>2</th>
<td><a href="updateEmp.html?emp_no=1001&emp_name=张三&emp_job=咨询师&emp_manager=3&emp_hirdate=2009-10-1&emp_salary=10000&emp_detno=2">修改</a>
<a href="#">删除</a></td>
</tr>
</table>
</body>
4.3updateEmp.html
<body>
<a href="../list.do">查询员工信息</a>
<hr/>
<h3>修改员工</h3>
<form action="../update.do" method="get">
<input type="hidden" name="emp_no"/>
<label>员工的名字:</label>
<input type="text" name="emp_name"/>
<br />
<label>工作岗位</label>
<input type="text" name="emp_job"/>
<br />
<label>部门经理</label>
<select name="emp_manager">
<option value="1">杜经理</option>
<option value="2">李经理</option>
<option value="3">王经理</option>
<option value="4">吴经理</option>
</select>
<br />
<label>入职日期</label>
<input type="datetime" name="emp_hirdate"/>
<br />
<label>工资</label>
<input type="text" name="emp_salary"/>
<br />
<label>部门:</label>
<select name="emp_detno">
<option value="1">财务部</option>
<option value="2">人事部</option>
<option value="3">法律部</option>
<option value="4">咨询部</option>
</select>
<br />
<input type="submit" value="增加"/>
<input type="reset" value="重置"/>
</form>
<script>
//1.获取地址栏的地址
console.log(window.location.href);
console.log(window.location.search);
let argus =window.location.search.substring(1);
let arr = argus.split("&");
$("input").eq(0).val(arr[0].split("=")[1]);// 编号
$("input").eq(1).val(decodeURI(arr[1].split("=")[1])); // 名字
$("input").eq(2).val(decodeURI(arr[2].split("=")[1]));// 岗位
$("select").eq(0).val(decodeURI(arr[3].split("=")[1])); // 经理
$("input").eq(3).val(decodeURI(arr[4].split("=")[1])); // 入职日期
$("input").eq(4).val(decodeURI(arr[5].split("=")[1])); // 工资
$("select").eq(1).val(decodeURI(arr[6].split("=")[1])); // 部门
</script>
5.添加请求名称:找到WEB-INF文件夹下的web.xml文件,添加以下一段代码
<servlet>
<servlet-name>emp</servlet-name>
<servlet-class>EmpSys.action.EmpServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>emp</servlet-name>
<url-pattern>*.do</url-pattern>
</servlet-mapping>
6.结果显示:
在地址栏的最后加上"list.do"即可查看所有信息