JSP+JDBC实现数据库的增删改查

package bean;

public class BookBean {
    private int id;
    private String name;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

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

    public BookBean(int id, String name) {
        this.id = id;
        this.name = name;
    }

    public BookBean(){

    }
}

package dao;
import bean.BookBean;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DbDao {
    private String driver = "com.mysql.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/testjdbc?useSSL=false&serverTimezone=UTC";
    private String user = "root";
    private String password = "root";
    private Connection conn = null;
    public static List<BookBean> list = new ArrayList<BookBean>();

    public void findALL() {
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
            String FINDALL = "select * from books";
            PreparedStatement ps = conn.prepareStatement(FINDALL);
            ResultSet rs;
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new BookBean(rs.getInt(1), rs.getString(2)));
            }
            conn.close();
            rs.close();
            ps.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public List<BookBean> findByName(String Name) throws ClassNotFoundException, SQLException {
        List<BookBean> list = new ArrayList<BookBean>();
        Class.forName(driver);
        conn = DriverManager.getConnection(url, user, password);
        String FINDBYNAME = "select ID,NAME from books where name = ?";
        PreparedStatement ps = conn.prepareStatement(FINDBYNAME);
        ps.setString(1,Name);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            list.add(new BookBean(rs.getInt(1), rs.getString(2)));
        }
        rs.close();
        ps.close();
        conn.close();
        return list;
    }

    public void Delete(int id) throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        conn = DriverManager.getConnection(url, user, password);
        String DELETE = "DELETE FROM books WHERE id=?";
        PreparedStatement ps = conn.prepareStatement(DELETE);
        ps.setInt(1,id);
       // int rs = ps.executeUpdate(); // 执行删除
        ps.execute();
        ps.close();
        conn.close();
    }

    public void Save(String name) throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        conn = DriverManager.getConnection(url, user, password);
        String INSERT = "INSERT INTO books(name) VALUES (?)";
        PreparedStatement ps = conn.prepareStatement(INSERT);
        ps.setString(1,name);
        ps.execute();
        ps.close();
        conn.close();
    }

    public void Update(BookBean bookbean) throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        conn = DriverManager.getConnection(url, user, password);
        final String UPDATE = "UPDATE books set name=? WHERE id=?";
        PreparedStatement ps = conn.prepareStatement(UPDATE);
        ps.setString(1,bookbean.getName());
        ps.setInt(2,bookbean.getId());
        ps.execute();
        ps.close();
        conn.close();
    }

}

package service;

import bean.BookBean;
import dao.DbDao;

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 java.io.IOException;
import java.sql.SQLException;
import java.util.List;

@WebServlet("/MyServlet")
public class MyServlet extends HttpServlet {

    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws IOException {
        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");
        String name = request.getParameter("name");
        String id = request.getParameter("delete");
        String addname = request.getParameter("add");
        String updateId = request.getParameter("updateId");
        String updateName = request.getParameter("updateName");

        HttpSession session = request.getSession();

        if (name.equals("ALL")) {
            DbDao test = new DbDao();
            test.findALL();
            List<BookBean> list = test.list;
            session.setAttribute("list", list);
        } else {
            DbDao dd = new DbDao();
            try {
                List<BookBean> book = dd.findByName(name);
                session.setAttribute("list", book);
            } catch (ClassNotFoundException | SQLException e) {
                e.printStackTrace();
            }
        }

        if (!"".equals(id)) {
            DbDao dd = new DbDao();
            dd.findALL();
            try {
                int bookid = Integer.parseInt(id);
                dd.Delete(bookid);
            } catch (ClassNotFoundException | SQLException e) {
                e.printStackTrace();
            }
        }

        if (!"".equals(addname)) {
            DbDao dd = new DbDao();
            try {
                dd.Save(addname);
            } catch (ClassNotFoundException | SQLException e) {
                e.printStackTrace();
            }
        }

        if (!"".equals(updateId) && !"".equals(updateName)) {
            try {
                BookBean bookbean = new BookBean();
                DbDao dbdao = new DbDao();
                bookbean.setId(Integer.valueOf(updateId));
                bookbean.setName(updateName);
                dbdao.Update(bookbean);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        response.sendRedirect("index.jsp");
    }
}

<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<%@ page import="java.util.ArrayList" %>
<%@ page import="bean.BookBean" %>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>JDBC - CRUD</title>
</head>
<body>
<form action="MyServlet" method="post">
    <p>Tip：输入ALL查询全部书籍</p>
    请输入要查询的书名:<input type="text" name="name"><br>
    请输入要添加的书名:<input type="text" name="add"><br>
    请输入删除的书的ID号:<input type="text" name="delete"><br>
    请输入要更新的书的ID号:<input type="text" name="updateId">书名改为:<input type="text" name="updateName"><br>
    <input type="submit" name="确定">
    <table border="1px solid black">
        <tr>
            <th>ID</th>
            <th>Name</th>
        </tr>
        <%
            ArrayList<BookBean> books = (ArrayList)session.getAttribute("list");
            if (session.getAttribute("list") != null){
                for(BookBean db : books){
        %>
        <tr>
            <td><%=db.getId()%></td>
            <td><%=db.getName()%></td>
        </tr>
        <%
                }
                books.clear();
            }
        %>
    </table>
</form>
</body>
</html>