JSP+servlet对数据库数据增删改查

<%--
  Created by IntelliJ IDEA.
  User: Lenovo
  Date: 2021/12/6
  Time: 16:32
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="C" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>Title</title>
    <style>
        table trth, td {
            text-align: center;
        }

        button {
            border: none;
            width: 80px;
            height: 27px;
            border-radius: 5px;
            background-color: #888888;
            box-sizing: content-box;
        }

        input {
            width: 180px;
            height: 25px;
            opacity: 0.5;
            border-radius: 5px;
            margin-right: 5px;
        }
    </style>
</head>
<body>
<h3>员工信息</h3>
<hr>

<table border="1px" width="600px" cellpadding="8px" cellspacing="0" align="center" bgcolor="#c0c0c0">
    <tr>
        <th colspan="6" style="text-align: center"><input type="text" placeholder="请输入eid进行查找"></th>
        <th><button>查找</button></th>
    </tr>
    <form action="add" method="post">
        <tr>
            <th><input type="text" placeholder="eid" name="eid"></th>
            <th><input type="text" placeholder="name" name="name"></th>
            <th><input type="date" placeholder="birthday" name="birthday"></th>
            <th><input type="text" placeholder="phone" name="phone"></th>
            <th><input type="text" placeholder="salary" name="salary"></th>
            <th><input type="text" placeholder="did" name="did"></th>
            <th>
                <button>添加</button>
            </th>
        </tr>
    </form>
    <tr>
        <th>eid</th>
        <th>name</th>
        <th>birthday</th>
        <th>phone</th>
        <th>salary</th>
        <th>did</th>
        <th>修改</th>
    </tr>
    <c:forEach items="${emps}" var="emp">
        <tr>
            <td>${emp.eid}</td>
            <td>${emp.name}</td>
            <td>${emp.birthday}</td>
            <td>${emp.phone}</td>
            <td>${emp.salary}</td>
            <td>${emp.did}</td>
            <td style="text-align: center">
                <button onclick="remove(${emp.eid})">删除</button>
                |
                <button onclick="eidemp(${emp.eid})">修改</button>
            </td>
        </tr>
        <c:choose>
            <c:when test="${emp.eid==index}">
                <tr>
                    <form action="eidemp">
                        <th><input type="text" placeholder="${emp.eid}" name="eid" value="${index}" readonly></th>
                        <th><input type="text" placeholder="name" name="name" value="${emp.name}"></th>
                        <th><input type="date" placeholder="birthday" name="birthday" value="${emp.birthday}"></th>
                        <th><input type="text" placeholder="phone" name="phone" value="${emp.phone}"></th>
                        <th><input type="text" placeholder="salary" name="salary" value="${emp.salary}"></th>
                        <th><input type="text" placeholder="did" name="did" value="${emp.did}"></th>
                        <th>
                            <button type="submit">确认修改</button>
                        </th>
                    </form>
                </tr>
            </c:when>
        </c:choose>
    </c:forEach>
</table>
<script>
    function remove(eid) {
        window.location.href = "http://localhost:8080/web1206_war_exploded/EmpDeleteServlet?eid=" + eid
    }

    function eidemp(eid) {
        window.location.href = "http://localhost:8080/web1206_war_exploded/form1?eid=" + eid
    }
</script>
</body>
</html>

package com.yq.dao;

import com.yq.util.JdbcUtil;

import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BaseDao {
    public <T> List<T> query(Class<T> cla, String sql, Object... params) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<T> list = new ArrayList<>();
        try {
            connection = JdbcUtil.getConnection();
            statement = connection.prepareStatement(sql);
            setParams(statement, params);

            //执行查询
            resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            while (resultSet.next()) {
                Constructor<T> constructor = cla.getConstructor();
                T t = constructor.newInstance();
                int columnCount = metaData.getColumnCount();
                for (int i = 0; i < columnCount; i++) {
                    String columnName = metaData.getColumnName(i + 1);
//                    System.out.println(columnName);
                    Field field = cla.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t, resultSet.getObject(i + 1));
                }
                list.add(t);
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtil.release(null, statement, connection);
        }
        return list;
    }

    public boolean execute(String sql, Object... params) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = JdbcUtil.getConnection();
            statement = connection.prepareStatement(sql);
            setParams(statement, params);
            return statement.executeUpdate() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtil.release(null, statement, connection);
        }
        return false;
    }

    public void setParams(PreparedStatement statement, Object... params) throws SQLException {
        if (params != null && params.length > 0) {
            for (int i = 0; i < params.length; i++) {
                statement.setObject(i + 1, params[i]);
            }
        }
    }

}

package com.yq.dao;

import com.yq.model.Emp;

import java.util.List;

public class EmployeeDao extends BaseDao{
    public boolean saveEmployee(Emp emp){
        String sql = "insert into emp(eid,name,birthday,phone,salary,did)values(?,?,?,?,?,?)";
        return  execute(sql,emp.getEid(),emp.getName(),emp.getBirthday(),emp.getPhone(),emp.getSalary(),emp.getDid());
    }

    public List<Emp> findAll(){
        String sql = "select * from emp";
        return query(Emp.class,sql);
    }

    public boolean delete(int eid){
        String sql="delete from emp where eid=?";
        return execute(sql,eid);
    }

    public boolean eidEmp(Emp emp){
        String sql ="update emp set name=?,birthday=?,phone=?,salary=?,did=? where eid=?";
        return execute(sql,emp.getName(),emp.getBirthday(),emp.getPhone(),emp.getSalary(),emp.getDid(),emp.getEid());
    }

    public Emp findById(int id){
        String sql = "select * from emp where eid=?";
        return query(Emp.class,sql,id).get(0);
    }
}

package com.yq.model;


import java.sql.Date;

public class Emp {

  private int eid;
  private String name;
  private Date birthday;
  private String phone;
  private double salary;
  private int  did;

  public Emp() {
  }

  public Emp(int eid, String name, Date birthday, String phone, double salary, int did) {
    this.eid = eid;
    this.name = name;
    this.birthday = birthday;
    this.phone = phone;
    this.salary = salary;
    this.did = did;
  }

  public int getEid() {
    return eid;
  }

  public void setEid(int eid) {
    this.eid = eid;
  }


  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }


  public Date getBirthday() {
    return birthday;
  }

  public void setBirthday(Date birthday) {
    this.birthday = birthday;
  }


  public String getPhone() {
    return phone;
  }

  public void setPhone(String phone) {
    this.phone = phone;
  }


  public double getSalary() {
    return salary;
  }

  public void setSalary(double salary) {
    this.salary = salary;
  }


  public int getDid() {
    return did;
  }

  public void setDid(int did) {
    this.did = did;
  }

  @Override
  public String toString() {
    return "Emp{" +
            "eid=" + eid +
            ", name='" + name + '\'' +
            ", birthday=" + birthday +
            ", phone='" + phone + '\'' +
            ", salary=" + salary +
            ", did=" + did +
            '}';
  }
}

package com.yq.servlet;

import com.yq.dao.EmployeeDao;
import com.yq.model.Emp;

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.sql.Date;

@WebServlet("/add")
public class EmpAddServlet extends HttpServlet {
    EmployeeDao employeeDao = new EmployeeDao();

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        int eid = Integer.parseInt(req.getParameter("eid"));
        String name = req.getParameter("name");
        String birthday = req.getParameter("birthday");
        String phone = req.getParameter("phone");
        double salary = Double.parseDouble(req.getParameter("salary"));
        int did = Integer.parseInt(req.getParameter("did"));
        System.out.println("添加的信息："+eid + "," + name + "," + birthday + "," + phone + "," + salary + "," + did);
        Emp emp = new Emp();
        emp.setEid(eid);
        emp.setName(name);
        emp.setBirthday(Date.valueOf(birthday));
        emp.setPhone(phone);
        emp.setSalary(salary);
        emp.setDid(did);
        employeeDao.saveEmployee(emp);
        req.getRequestDispatcher("/form1").forward(req,resp);
    }
}

package com.yq.servlet;

import com.yq.dao.EmployeeDao;

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("/EmpDeleteServlet")
public class EmpDeleteServlet extends HttpServlet {
    EmployeeDao employeeDao = new EmployeeDao();

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String eid = req.getParameter("eid");
        System.out.println(eid);
        int id = Integer.parseInt(eid);
        employeeDao.delete(id);
        req.getRequestDispatcher("/form1").forward(req,resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }
}

package com.yq.servlet;

import com.yq.dao.EmployeeDao;
import com.yq.model.Emp;

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.sql.Date;

@WebServlet("/eidemp")
public class EmpEidServlet extends HttpServlet {
    EmployeeDao employeeDao = new EmployeeDao();
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String eid =req.getParameter("eid");
        String name = req.getParameter("name");
        String birthday = req.getParameter("birthday");
        String phone = req.getParameter("phone");
        String salary = req.getParameter("salary");
        String did =req.getParameter("did");
        System.out.println("修改信息："+eid + "," + name + "," + birthday + "," + phone + "," + salary + "," + did);
        Emp emp = new Emp();
        emp.setEid(Integer.parseInt(eid));
        emp.setName(name);
        emp.setBirthday(Date.valueOf(birthday));
        emp.setPhone(phone);
        emp.setSalary(Double.parseDouble(salary));
        emp.setDid(Integer.parseInt(did));
        employeeDao.eidEmp(emp);
        req.getRequestDispatcher("/form1").forward(req,resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req,resp);
    }
}

package com.yq.servlet;

import com.yq.dao.EmployeeDao;
import com.yq.model.Emp;

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.io.PrintWriter;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.sql.Date;
import java.util.List;

@WebServlet("/form1")
public class EmpServlet extends HttpServlet {
    EmployeeDao employeeDao = new EmployeeDao();

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//        resp.setContentType("text/html;charset=UTF-8");
//        PrintWriter out = resp.getWriter();
//        out.append("<ul>");
//        for (int i = 0; i < list.size(); i++) {
//            out.append("<li>" + list.get(i) + "</li>");
//        }
//        out.append("</ul>");
        String eid = req.getParameter("eid");
        System.out.println("获取修改用户的eid=" + eid);
        if (eid != null && !eid.equals("")) {
            Integer index = Integer.parseInt(eid);
            req.setAttribute("index", index);
        }
        List<Emp> list = employeeDao.findAll();
        req.setAttribute("emps", list);
        req.getRequestDispatcher("/list.jsp").forward(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }
}

package com.yq.util;

import com.alibaba.druid.pool.DruidDataSource;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtil {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    private static DataSource source;

    static {
        Properties properties = new Properties();
        try {
//            InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
//            properties.load(in);
            properties.load(new FileInputStream("E:\\javaEE-workspace\\yqjavawork\\web1206\\src\\jdbc.properties"));
            driver = properties.getProperty("jdbc.driver");
            url = properties.getProperty("jdbc.url");
            username = properties.getProperty("jdbc.username");
            password = properties.getProperty("jdbc.password");
            //创建数据库连接池对象
            DruidDataSource ds = new DruidDataSource();
            ds.setDriverClassName(driver);
            ds.setUrl(url);
            ds.setUsername(username);
            ds.setPassword(password);
            source = ds;
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        try {
            return source.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public static void release(ResultSet resultSet, Statement statement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html>
<head>
    <title>JSP</title>
</head>
<body>
<form action="form1">
    <p><input type="submit" value="获取员工表信息"></p>
</form>
</body>
</html>

原文链接：https://blog.csdn.net/qq_57633918/article/details/121777720