• JSP Servlet JDBC MySQL CRUD 示例教程


    在本教程中,我们将构建一个简单的用户管理Web 应用程序,它管理具有基本功能的用户集合:列表、插入、更新、删除(或 CURD 操作 - 创建、更新、读取和删除)。
    您可以从我的 GitHub 存储库下载本教程的源代码,本教程末尾提供了链接。
    顶级 JSP、Servlet 和 JDBC 教程:
    查看 使用 JSP、Servlet、JDBC 和 MySQL 构建 Todo 应用程序。 

    我们将在我们的用户管理Web 应用程序中开发以下简单的基本功能:

    1. 创建用户
    2. 更新用户
    3. 删除用户
    4. 检索用户
    5. 所有用户列表
    该应用程序看起来像这样:

     

    使用的工具和技术

    • JSP - 2.2 +
    • IDE - STS/Eclipse Neon.3
    • JDK - 1.8 或更高版本
    • Apache Tomcat - 8.5
    • JSTL - 1.2.1
    • 小服务程序 API - 2.5
    • MySQL - mysql-connector-java-8.0.13.jar

    开发步骤

    1. 创建 Eclipse 动态 Web 项目
    2. 添加依赖项
    3. 项目结构
    4. MySQL 数据库设置
    5. 创建一个 JavaBean - User.java
    6. 创建一个 UserDAO.java
    7. 创建一个 UserServlet.java
    8. 创建用户列表 JSP 页面 - user-list.jsp
    9. 创建用户表单 JSP 页面 - user-form.jsp
    10. 创建错误 JSP 页面
    11. 部署和测试应用程序演示

    类图

     这是我们将在本教程中开发的用户管理Web 应用程序 的类图 :

    1. 创建一个 Eclipse 动态 Web 项目

    在 Eclipse 中创建一个新的动态 Web 项目:
    1. 在主菜单上选择 文件 > 新建 > 项目...。
    2. 在即将出现的向导中,选择 Web > Dynamic Web Project。
    3. 单击 下一步
    4. 输入项目名称为“jsp-servlet-jdbc-mysql-example”;
    5. 确保目标运行时设置为具有当前支持的版本的 Apache Tomcat。 

    2.添加依赖

    将以下 jar 文件的最新版本添加到 lib 文件夹。
    • jsp-api.2.3.1.jar
    • servlet-api.2.3.jar
    • mysql-connector-java-8.0.13.jar
    • jstl-1.2.jar

    三、项目结构

    标准项目结构供您参考- 
     

    4. MySQL 数据库设置

    让我们在 MySQL 中创建一个名为“demo”的数据库。现在,使用下面的 DDL 脚本创建一个用户表:
    1. CREATE DATABASE 'demo';
    2. USE demo;
    3. create table users (
    4. id int(3) NOT NULL AUTO_INCREMENT,
    5. name varchar(120) NOT NULL,
    6. email varchar(220) NOT NULL,
    7. country varchar(120),
    8. PRIMARY KEY (id)
    9. );

    您可以使用 MySQL 命令行客户端或 MySQL Workbench 工具来创建数据库。上面的 用户表如下所示:

    5.创建一个JavaBean——User.java

    让我们使用以下代码 创建一个User java 类来对数据库中的用户实体进行建模:
    1. package net.javaguides.usermanagement.model;
    2. /**
    3. * User.java
    4. * This is a model class represents a User entity
    5. * @author Ramesh Fadatare
    6. *
    7. */
    8. public class User {
    9. protected int id;
    10. protected String name;
    11. protected String email;
    12. protected String country;
    13. public User() {
    14. }
    15. public User(String name, String email, String country) {
    16. super();
    17. this.name = name;
    18. this.email = email;
    19. this.country = country;
    20. }
    21. public User(int id, String name, String email, String country) {
    22. super();
    23. this.id = id;
    24. this.name = name;
    25. this.email = email;
    26. this.country = country;
    27. }
    28. public int getId() {
    29. return id;
    30. }
    31. public void setId(int id) {
    32. this.id = id;
    33. }
    34. public String getName() {
    35. return name;
    36. }
    37. public void setName(String name) {
    38. this.name = name;
    39. }
    40. public String getEmail() {
    41. return email;
    42. }
    43. public void setEmail(String email) {
    44. this.email = email;
    45. }
    46. public String getCountry() {
    47. return country;
    48. }
    49. public void setCountry(String country) {
    50. this.country = country;
    51. }
    52. }

    6.创建一个UserDAO.java

    让我们创建一个UserDAO类,它是一个数据访问层 (DAO) 类,它为数据库中的表用户 提供 CRUD(创建、读取、更新、删除)操作。这是UserDAO的完整源代码:
    1. package net.javaguides.usermanagement.dao;
    2. import java.sql.Connection;
    3. import java.sql.DriverManager;
    4. import java.sql.PreparedStatement;
    5. import java.sql.ResultSet;
    6. import java.sql.SQLException;
    7. import java.util.ArrayList;
    8. import java.util.List;
    9. import net.javaguides.usermanagement.model.User;
    10. /**
    11. * AbstractDAO.java This DAO class provides CRUD database operations for the
    12. * table users in the database.
    13. *
    14. * @author Ramesh Fadatare
    15. *
    16. */
    17. public class UserDAO {
    18. private String jdbcURL = "jdbc:mysql://localhost:3306/demo?useSSL=false";
    19. private String jdbcUsername = "root";
    20. private String jdbcPassword = "root";
    21. private static final String INSERT_USERS_SQL = "INSERT INTO users" + " (name, email, country) VALUES "
    22. + " (?, ?, ?);";
    23. private static final String SELECT_USER_BY_ID = "select id,name,email,country from users where id =?";
    24. private static final String SELECT_ALL_USERS = "select * from users";
    25. private static final String DELETE_USERS_SQL = "delete from users where id = ?;";
    26. private static final String UPDATE_USERS_SQL = "update users set name = ?,email= ?, country =? where id = ?;";
    27. public UserDAO() {
    28. }
    29. protected Connection getConnection() {
    30. Connection connection = null;
    31. try {
    32. Class.forName("com.mysql.jdbc.Driver");
    33. connection = DriverManager.getConnection(jdbcURL, jdbcUsername, jdbcPassword);
    34. } catch (SQLException e) {
    35. // TODO Auto-generated catch block
    36. e.printStackTrace();
    37. } catch (ClassNotFoundException e) {
    38. // TODO Auto-generated catch block
    39. e.printStackTrace();
    40. }
    41. return connection;
    42. }
    43. public void insertUser(User user) throws SQLException {
    44. System.out.println(INSERT_USERS_SQL);
    45. // try-with-resource statement will auto close the connection.
    46. try (Connection connection = getConnection();
    47. PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {
    48. preparedStatement.setString(1, user.getName());
    49. preparedStatement.setString(2, user.getEmail());
    50. preparedStatement.setString(3, user.getCountry());
    51. System.out.println(preparedStatement);
    52. preparedStatement.executeUpdate();
    53. } catch (SQLException e) {
    54. printSQLException(e);
    55. }
    56. }
    57. public User selectUser(int id) {
    58. User user = null;
    59. // Step 1: Establishing a Connection
    60. try (Connection connection = getConnection();
    61. // Step 2:Create a statement using connection object
    62. PreparedStatement preparedStatement = connection.prepareStatement(SELECT_USER_BY_ID);) {
    63. preparedStatement.setInt(1, id);
    64. System.out.println(preparedStatement);
    65. // Step 3: Execute the query or update query
    66. ResultSet rs = preparedStatement.executeQuery();
    67. // Step 4: Process the ResultSet object.
    68. while (rs.next()) {
    69. String name = rs.getString("name");
    70. String email = rs.getString("email");
    71. String country = rs.getString("country");
    72. user = new User(id, name, email, country);
    73. }
    74. } catch (SQLException e) {
    75. printSQLException(e);
    76. }
    77. return user;
    78. }
    79. public List selectAllUsers() {
    80. // using try-with-resources to avoid closing resources (boiler plate code)
    81. List users = new ArrayList<>();
    82. // Step 1: Establishing a Connection
    83. try (Connection connection = getConnection();
    84. // Step 2:Create a statement using connection object
    85. PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL_USERS);) {
    86. System.out.println(preparedStatement);
    87. // Step 3: Execute the query or update query
    88. ResultSet rs = preparedStatement.executeQuery();
    89. // Step 4: Process the ResultSet object.
    90. while (rs.next()) {
    91. int id = rs.getInt("id");
    92. String name = rs.getString("name");
    93. String email = rs.getString("email");
    94. String country = rs.getString("country");
    95. users.add(new User(id, name, email, country));
    96. }
    97. } catch (SQLException e) {
    98. printSQLException(e);
    99. }
    100. return users;
    101. }
    102. public boolean deleteUser(int id) throws SQLException {
    103. boolean rowDeleted;
    104. try (Connection connection = getConnection();
    105. PreparedStatement statement = connection.prepareStatement(DELETE_USERS_SQL);) {
    106. statement.setInt(1, id);
    107. rowDeleted = statement.executeUpdate() > 0;
    108. }
    109. return rowDeleted;
    110. }
    111. public boolean updateUser(User user) throws SQLException {
    112. boolean rowUpdated;
    113. try (Connection connection = getConnection();
    114. PreparedStatement statement = connection.prepareStatement(UPDATE_USERS_SQL);) {
    115. statement.setString(1, user.getName());
    116. statement.setString(2, user.getEmail());
    117. statement.setString(3, user.getCountry());
    118. statement.setInt(4, user.getId());
    119. rowUpdated = statement.executeUpdate() > 0;
    120. }
    121. return rowUpdated;
    122. }
    123. private void printSQLException(SQLException ex) {
    124. for (Throwable e : ex) {
    125. if (e instanceof SQLException) {
    126. e.printStackTrace(System.err);
    127. System.err.println("SQLState: " + ((SQLException) e).getSQLState());
    128. System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
    129. System.err.println("Message: " + e.getMessage());
    130. Throwable t = ex.getCause();
    131. while (t != null) {
    132. System.out.println("Cause: " + t);
    133. t = t.getCause();
    134. }
    135. }
    136. }
    137. }
    138. }

    7.创建一个UserServlet.java

    现在,让我们创建UserServlet作为页面控制器来处理来自客户端的所有请求。我们先来看代码:
    1. package net.javaguides.usermanagement.web;
    2. import java.io.IOException;
    3. import java.sql.SQLException;
    4. import java.util.List;
    5. import javax.servlet.RequestDispatcher;
    6. import javax.servlet.ServletException;
    7. import javax.servlet.annotation.WebServlet;
    8. import javax.servlet.http.HttpServlet;
    9. import javax.servlet.http.HttpServletRequest;
    10. import javax.servlet.http.HttpServletResponse;
    11. import net.javaguides.usermanagement.dao.UserDAO;
    12. import net.javaguides.usermanagement.model.User;
    13. /**
    14. * ControllerServlet.java
    15. * This servlet acts as a page controller for the application, handling all
    16. * requests from the user.
    17. * @email Ramesh Fadatare
    18. */
    19. @WebServlet("/")
    20. public class UserServlet extends HttpServlet {
    21. private static final long serialVersionUID = 1L;
    22. private UserDAO userDAO;
    23. public void init() {
    24. userDAO = new UserDAO();
    25. }
    26. protected void doPost(HttpServletRequest request, HttpServletResponse response)
    27. throws ServletException, IOException {
    28. doGet(request, response);
    29. }
    30. protected void doGet(HttpServletRequest request, HttpServletResponse response)
    31. throws ServletException, IOException {
    32. String action = request.getServletPath();
    33. try {
    34. switch (action) {
    35. case "/new":
    36. showNewForm(request, response);
    37. break;
    38. case "/insert":
    39. insertUser(request, response);
    40. break;
    41. case "/delete":
    42. deleteUser(request, response);
    43. break;
    44. case "/edit":
    45. showEditForm(request, response);
    46. break;
    47. case "/update":
    48. updateUser(request, response);
    49. break;
    50. default:
    51. listUser(request, response);
    52. break;
    53. }
    54. } catch (SQLException ex) {
    55. throw new ServletException(ex);
    56. }
    57. }
    58. private void listUser(HttpServletRequest request, HttpServletResponse response)
    59. throws SQLException, IOException, ServletException {
    60. List listUser = userDAO.selectAllUsers();
    61. request.setAttribute("listUser", listUser);
    62. RequestDispatcher dispatcher = request.getRequestDispatcher("user-list.jsp");
    63. dispatcher.forward(request, response);
    64. }
    65. private void showNewForm(HttpServletRequest request, HttpServletResponse response)
    66. throws ServletException, IOException {
    67. RequestDispatcher dispatcher = request.getRequestDispatcher("user-form.jsp");
    68. dispatcher.forward(request, response);
    69. }
    70. private void showEditForm(HttpServletRequest request, HttpServletResponse response)
    71. throws SQLException, ServletException, IOException {
    72. int id = Integer.parseInt(request.getParameter("id"));
    73. User existingUser = userDAO.selectUser(id);
    74. RequestDispatcher dispatcher = request.getRequestDispatcher("user-form.jsp");
    75. request.setAttribute("user", existingUser);
    76. dispatcher.forward(request, response);
    77. }
    78. private void insertUser(HttpServletRequest request, HttpServletResponse response)
    79. throws SQLException, IOException {
    80. String name = request.getParameter("name");
    81. String email = request.getParameter("email");
    82. String country = request.getParameter("country");
    83. User newUser = new User(name, email, country);
    84. userDAO.insertUser(newUser);
    85. response.sendRedirect("list");
    86. }
    87. private void updateUser(HttpServletRequest request, HttpServletResponse response)
    88. throws SQLException, IOException {
    89. int id = Integer.parseInt(request.getParameter("id"));
    90. String name = request.getParameter("name");
    91. String email = request.getParameter("email");
    92. String country = request.getParameter("country");
    93. User book = new User(id, name, email, country);
    94. userDAO.updateUser(book);
    95. response.sendRedirect("list");
    96. }
    97. private void deleteUser(HttpServletRequest request, HttpServletResponse response)
    98. throws SQLException, IOException {
    99. int id = Integer.parseInt(request.getParameter("id"));
    100. userDAO.deleteUser(id);
    101. response.sendRedirect("list");
    102. }
    103. }

    8. 创建用户列表 JSP 页面 - user-list.jsp

    接下来,创建一个 JSP 页面来显示数据库中的所有用户让我们在项目的WebContent目录下创建一个 list-user.jsp页面,代码如下:
    1. <%@ page language="java" contentType="text/html; charset=UTF-8"
    2. pageEncoding="UTF-8"%>
    3. <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
    4. <html>
    5. <head>
    6. <title>User Management Applicationtitle>
    7. <link rel="stylesheet"
    8. href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
    9. integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T"
    10. crossorigin="anonymous">
    11. head>
    12. <body>
    13. <header>
    14. <nav class="navbar navbar-expand-md navbar-dark"
    15. style="background-color: tomato">
    16. <div>
    17. <a href="https://www.javaguides.net" class="navbar-brand"> User
    18. Management App a>
    19. div>
    20. <ul class="navbar-nav">
    21. <li><a href="<%=request.getContextPath()%>/list"
    22. class="nav-link">Usersa>li>
    23. ul>
    24. nav>
    25. header>
    26. <br>
    27. <div class="row">
    28. <div class="container">
    29. <h3 class="text-center">List of Usersh3>
    30. <hr>
    31. <div class="container text-left">
    32. <a href="<%=request.getContextPath()%>/new" class="btn btn-success">Add
    33. New Usera>
    34. div>
    35. <br>
    36. <table class="table table-bordered">
    37. <thead>
    38. <tr>
    39. <th>IDth>
    40. <th>Nameth>
    41. <th>Emailth>
    42. <th>Countryth>
    43. <th>Actionsth>
    44. tr>
    45. thead>
    46. <tbody>
    47. <c:forEach var="user" items="${listUser}">
    48. <tr>
    49. <td><c:out value="${user.id}" />td>
    50. <td><c:out value="${user.name}" />td>
    51. <td><c:out value="${user.email}" />td>
    52. <td><c:out value="${user.country}" />td>
    53. <td><a href="edit?id=">Edita>
    54.      <a
    55. href="delete?id=">Deletea>td>
    56. tr>
    57. c:forEach>
    58. tbody>
    59. table>
    60. div>
    61. div>
    62. body>
    63. html>

    一旦您将在 tomcat 中部署以上 JSP 页面并在浏览器中打开,如下所示:

    9. 创建用户表单 JSP 页面 - user-form.jsp

    接下来,我们创建一个 JSP 页面来创建一个名为user-form.jsp的新用户。这是它的完整源代码:
    1. <%@ page language="java" contentType="text/html; charset=UTF-8"
    2. pageEncoding="UTF-8"%>
    3. <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
    4. <html>
    5. <head>
    6. <title>User Management Applicationtitle>
    7. <link rel="stylesheet"
    8. href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
    9. integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T"
    10. crossorigin="anonymous">
    11. head>
    12. <body>
    13. <header>
    14. <nav class="navbar navbar-expand-md navbar-dark"
    15. style="background-color: tomato">
    16. <div>
    17. <a href="https://www.javaguides.net" class="navbar-brand"> User Management App a>
    18. div>
    19. <ul class="navbar-nav">
    20. <li><a href="<%=request.getContextPath()%>/list"
    21. class="nav-link">Usersa>li>
    22. ul>
    23. nav>
    24. header>
    25. <br>
    26. <div class="container col-md-5">
    27. <div class="card">
    28. <div class="card-body">
    29. <c:if test="${user != null}">
    30. <form action="update" method="post">
    31. c:if>
    32. <c:if test="${user == null}">
    33. <form action="insert" method="post">
    34. c:if>
    35. <caption>
    36. <h2>
    37. <c:if test="${user != null}">
    38. Edit User
    39. c:if>
    40. <c:if test="${user == null}">
    41. Add New User
    42. c:if>
    43. h2>
    44. caption>
    45. <c:if test="${user != null}">
    46. <input type="hidden" name="id" value="" />
    47. c:if>
    48. <fieldset class="form-group">
    49. <label>User Namelabel> <input type="text"
    50. value="" class="form-control"
    51. name="name" required="required">
    52. fieldset>
    53. <fieldset class="form-group">
    54. <label>User Emaillabel> <input type="text"
    55. value="" class="form-control"
    56. name="email">
    57. fieldset>
    58. <fieldset class="form-group">
    59. <label>User Countrylabel> <input type="text"
    60. value="" class="form-control"
    61. name="country">
    62. fieldset>
    63. <button type="submit" class="btn btn-success">Savebutton>
    64. form>
    65. div>
    66. div>
    67. div>
    68. body>
    69. html>

    一旦您将在 tomcat 中部署以上 JSP 页面并在浏览器中打开,如下所示:
    上面的页面用于创建新用户和编辑同一用户的两种功能。编辑页面如下所示:

    10. 创建错误 JSP 页面

    这是Error.jsp页面 的代码,它只显示异常消息:
    1. <%@ page language="java" contentType="text/html; charset=UTF-8"
    2. pageEncoding="UTF-8" isErrorPage="true" %>
    3. html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    4. "http://www.w3.org/TR/html4/loose.dtd">
    5. <html>
    6. <head>
    7. <title>Errortitle>
    8. head>
    9. <body>
    10. <center>
    11. <h1>Errorh1>
    12. <h2><%=exception.getMessage() %><br/> h2>
    13. center>
    14. body>
    15. html>

    11. 部署和测试应用程序

    是时候看一下上述用户管理Web 应用程序的演示了。在 tomcat 服务器中部署这个 web 应用程序。 
    在 Web 浏览器中键入以下 URL 以访问用户管理应用程序:  http://localhost:8080/jsp-servlet-jdbc-mysql-crud-example/

    创建新用户

    编辑用户

    所有用户列表

    GitHub 存储库

    本教程(用户管理)的源代码可在我的 GitHub 存储库中获得, 网址为GitHub - RameshMF/jsp-servlet-jdbc-mysql-crud-tutorial: JSP Servlet JDBC MySQL CRUD Example Tutorial
    附:中文乱码过滤器
    1. import java.io.IOException;
    2. import javax.servlet.Filter;
    3. import javax.servlet.FilterChain;
    4. import javax.servlet.FilterConfig;
    5. import javax.servlet.ServletException;
    6. import javax.servlet.ServletRequest;
    7. import javax.servlet.ServletResponse;
    8. public class CharaterEncodingFilter implements Filter {
    9. public void init(FilterConfig filterConfig) throws ServletException {
    10. }
    11. public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
    12. request.setCharacterEncoding("utf-8");
    13. response.setCharacterEncoding("utf-8");
    14. //response.setContentType("text/html; charset=UTF-8");
    15. chain.doFilter(request, response);
    16. }
    17. public void destroy() {
    18. }
    19. }
    1. <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    2. xmlns="http://xmlns.jcp.org/xml/ns/javaee"
    3. xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee
    4. http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
    5. id="WebApp_ID" version="3.1">
    6. <display-name>User Management Web Applicationdisplay-name>
    7. <error-page>
    8. <exception-type>java.lang.Exceptionexception-type>
    9. <location>/Error.jsplocation>
    10. error-page>
    11. <filter>
    12. <filter-name>CharacterEncodingFilterfilter-name>
    13. <filter-class>CharaterEncodingFilterfilter-class>
    14. filter>
    15. <filter-mapping>
    16. <filter-name>CharacterEncodingFilterfilter-name>
    17. <url-pattern>/*url-pattern>
    18. filter-mapping>
    19. web-app>

  • 相关阅读:
    解决win11更新后,文件夹打不开的bug
    CSS 中的 : 和 :: 有什么区别?
    异地办公也能畅快、无限制远程访问公司内网的解决方案
    使用van-dialog二次封装微信小程序模态框
    细节炸裂!阿里大佬深入探究G1源码之YoungGC技术
    Docker的自定义镜像
    MyBatisPlus学习笔记
    【lwip】07-链路层收发以太网数据帧源码分析
    DP4361国产六通道立体声D/A音频转换器芯片替代CS4361
    【收藏系列】多线程的代码案例(单例模式 + 阻塞队列 + 定时器 + 线程池)
  • 原文地址:https://blog.csdn.net/allway2/article/details/126054911