JSP+Servlet+JDBC+JavaBean+DAO单表查询标准模式

做的一个小Demo，对单表的增删查改，效果图如下：

数据库表结构

create table booktype
(
    tid  int auto_increment
        primary key,
    name varchar(20) not null
)

项目结构

Bean

package bean;

import java.io.Serializable;

/** * booktype * * @author Leo 2019-07-08 */
public class BookType implements Serializable {
    private static final long serialVersionUID = 1L;

    /** * name */
    private String name;


    public BookType() {
    }

    public BookType(String name) {
        this.name = name;
    }

    public String getName() {
        return name;
    }

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

    @Override
    public String toString() {
        return "BookType{" +
                "name='" + name + '\'' +
                '}';
    }
}

DAO

package dao;

import bean.BookType;
import util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/** * @ClassName: BookTypeDao * @Author: Leo * @Description: * @Date: 7/8/2019 10:36 AM */
public class BookTypeDao {
    private static Connection connection = null;

    static {
        connection = DBUtil.getConnection();
    }

    /** * 保存 * * @param bookType */
    public void save(BookType bookType) {
        String sql = "insert into booktype values(null,?)";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, bookType.getName());
            statement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    /** * 查询所有 * * @return */
    public List<BookType> selectAll() {
        List<BookType> list = new ArrayList<>();
        String sql = "select * from booktype order by tid";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new BookType(resultSet.getString("name")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /** * 根据名字模糊查询 * * @return */
    public List<BookType> selectByName(String keyword) {
        List<BookType> list = new ArrayList<>();
        String sql = "select * from booktype where name like ?";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, keyword + "%");
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new BookType(resultSet.getString("name")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /** * 通过id查询记录 * * @param id * @return */
    public BookType selectById(int id) {
        BookType bookType = null;
        String sql = "select * from booktype where tid = ?";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                bookType = new BookType(resultSet.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return bookType;
    }

    /** * 更新记录 * * @param name * @return */
    public int update(String name, int id) {
        String sql = "update booktype set name = ? where tid = ?";
        int result = 0;
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, name);
            statement.setInt(2, id);
            result = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /** * 通过id删除记录 * * @param id * @return */
    public int deleteById(int id) {
        String sql = "delete from booktype where tid = ?";
        int result = 0;
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            result = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
}

Servlet

AddServlet

package servlet;

import bean.BookType;
import dao.BookTypeDao;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;

/** * @ClassName: ${NAME} * @Author: Leo * @Description: * @Date: 7/8/2019 1:51 PM */

public class AddServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        response.setCharacterEncoding("UTF-8");
        request.setCharacterEncoding("UTF-8");
        String name = (String) request.getParameter("booktypename");
        BookTypeDao dao = new BookTypeDao();
        dao.save(new BookType(name));
        response.sendRedirect("index.jsp");
    }

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

DeleteServlet

package servlet;

import dao.BookTypeDao;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/** * @ClassName: ${NAME} * @Author: Leo * @Description: * @Date: 7/8/2019 1:54 PM */
public class DeleteServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        response.setCharacterEncoding("UTF-8");
        request.setCharacterEncoding("UTF-8");
        request.getParameter("id");
        BookTypeDao dao = new BookTypeDao();
        dao.deleteById(Integer.parseInt(request.getParameter("id")));
        response.sendRedirect("index.jsp");
    }
}

EditServlet

package servlet;

import dao.BookTypeDao;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/** * @ClassName: ${NAME} * @Author: Leo * @Description: * @Date: 7/8/2019 2:07 PM */
public class EditServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        response.setCharacterEncoding("UTF-8");
        request.setCharacterEncoding("UTF-8");
        int id = Integer.parseInt(request.getParameter("booktypeid"));
        String name = request.getParameter("booktypename");
        BookTypeDao dao = new BookTypeDao();
        dao.update(name, id);
        response.sendRedirect("index.jsp");
    }
}

QueryServlet

package servlet;

import bean.BookType;
import dao.BookTypeDao;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

/** * @ClassName: ${NAME} * @Author: Leo * @Description: * @Date: 7/8/2019 3:03 PM */
public class QueryServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        response.setCharacterEncoding("UTF-8");
        request.setCharacterEncoding("UTF-8");
        String keyword = request.getParameter("keyword");
        BookTypeDao dao = new BookTypeDao();
        List<BookType> list = dao.selectByName(keyword);
        request.setAttribute("queryResult", list);
        request.getRequestDispatcher("query.jsp").forward(request, response);
    }

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

Util

package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/** * @ClassName: DBUtil * @Author: Leo * @Description: * @Date: 7/8/2019 10:30 AM */
public class DBUtil {
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/books";
    private static final String USER = "root";
    private static final String PASSWORD = "root";

    private static Connection connection;

    public static Connection getConnection() {
        try {

            Class.forName(DRIVER);
            if (connection == null) {
                connection = DriverManager.getConnection(URL, USER, PASSWORD);
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
}

Web.xml

<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd" >

<web-app>
    <display-name>Archetype Created Web Application</display-name>
    <!--增加Servlet-->
    <servlet>
        <servlet-name>AddServlet</servlet-name>
        <servlet-class>servlet.AddServlet</servlet-class>
    </servlet>
    <!--删除Servlet-->
    <servlet>
        <servlet-name>DeleteServlet</servlet-name>
        <servlet-class>servlet.DeleteServlet</servlet-class>
    </servlet>
    <!--编辑Servlet-->
    <servlet>
        <servlet-name>EditServlet</servlet-name>
        <servlet-class>servlet.EditServlet</servlet-class>
    </servlet>
    <!--查询Servlet-->
    <servlet>
        <servlet-name>QueryServlet</servlet-name>
        <servlet-class>servlet.QueryServlet</servlet-class>
    </servlet>

    <servlet-mapping>
        <servlet-name>AddServlet</servlet-name>
        <url-pattern>/AddServlet</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>DeleteServlet</servlet-name>
        <url-pattern>/DeleteServlet</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>EditServlet</servlet-name>
        <url-pattern>/EditServlet</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>QueryServlet</servlet-name>
        <url-pattern>/QueryServlet</url-pattern>
    </servlet-mapping>
</web-app>

JSP

add.jsp

<%--
  Created by IntelliJ IDEA.
  User: 24234
  Date: 7/8/2019
  Time: 2:27 PM
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>添加</title>
</head>
<body>
<form action="AddServlet" method="post">
    <input type="text" name="booktypename">
    <input type="submit" value="添加">
</form>
</body>
</html>

edit.jsp

<%@ page import="dao.BookTypeDao" %>
<%@ page import="bean.BookType" %><%--
  Created by IntelliJ IDEA.
  User: 24234
  Date: 7/8/2019
  Time: 3:19 PM
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>编辑</title>
</head>
<body>
<%
    BookTypeDao dao = new BookTypeDao();
    BookType bookType = dao.selectById(Integer.parseInt(request.getParameter("id")));
    int id = Integer.parseInt(request.getParameter("id"));
    String name = bookType.getName();
%>
<form action="EditServlet" method="post">
    <input type="text" name="booktypeid" value="<%=id%>">
    <input type="text" name="booktypename" value="<%=name%>">
    <input type="submit" value="修改">
</form>
</body>
</html>

index.jsp

<%@ page import="dao.BookTypeDao" %>
<%@ page import="java.util.List" %>
<%@ page import="bean.BookType" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<body>
<table border="2">
    <tr>
        <a href="add.jsp">添加</a>

        <form action="QueryServlet" method="post">
            <label>
                <input type="text" name="keyword">
            </label>
            <input type="submit" name="query" value="查询">
        </form>

    </tr>
    <%
        BookTypeDao dao = new BookTypeDao();
        List<BookType> list = dao.selectAll();
        for (int i = 0; i < list.size(); i++) {
    %>
    <tr>
        <td>
            <%=i + 1%>
        </td>
        <td>
            <%=list.get(i).getName()%>
        </td>
        <td>
            <a href="edit.jsp?id=<%=i+1%>">编辑</a>
        </td>
        <td>
            <a href="DeleteServlet?id=<%=i+1%>">删除</a>
        </td>
    </tr>

    <%}%>
</table>
</body>
</html>

query.jsp

<%@ page import="bean.BookType" %>
<%@ page import="java.util.List" %><%--
  Created by IntelliJ IDEA.
  User: 24234
  Date: 7/8/2019
  Time: 3:13 PM
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>查询结果</title>
</head>
<body>
<%List<BookType> list = (List<BookType>) request.getAttribute("queryResult");%>
<table border="2">
    <%
        for (int i = 0; i < list.size(); i++) {
    %>
    <tr>
        <td>
            <%=i + 1%>
        </td>
        <td>
            <%=list.get(i).getName()%>
        </td>
        <td>
            <a href="EditServlet?id=<%=i+1%>">编辑</a>
        </td>
        <td>
            <a href="DeleteServlet?id=<%=i+1%>">删除</a>
        </td>
    </tr>
    <%}%>

    <td><a href="index.jsp">返回主页面</a></td>

</table>
</body>
</html>