JSP Servlet JDBC MySQL CRUD 示例教程

• Servlet 教程

• JSP 教程

• JDBC 4.2 教程

查看 使用 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 脚本创建一个用户表：

CREATE DATABASE 'demo';
USE demo;

create table users (
	id  int(3) NOT NULL AUTO_INCREMENT,
	name varchar(120) NOT NULL,
	email varchar(220) NOT NULL,
	country varchar(120),
	PRIMARY KEY (id)
);

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

5.创建一个JavaBean——User.java 让我们使用以下代码 创建一个User java 类来对数据库中的用户实体进行建模：

package net.javaguides.usermanagement.model;


public class User {
	protected int id;
	protected String name;
	protected String email;
	protected String country;
	
	public User() {
	}
	
	public User(String name, String email, String country) {
		super();
		this.name = name;
		this.email = email;
		this.country = country;
	}

	public User(int id, String name, String email, String country) {
		super();
		this.id = id;
		this.name = name;
		this.email = email;
		this.country = country;
	}

	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 String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getCountry() {
		return country;
	}
	public void setCountry(String country) {
		this.country = country;
	}
}

6.创建一个UserDAO.java 让我们创建一个UserDAO类，它是一个数据访问层 (DAO) 类，它为数据库中的表用户 提供 CRUD（创建、读取、更新、删除）操作。这是UserDAO的完整源代码：

package net.javaguides.usermanagement.dao;

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

import net.javaguides.usermanagement.model.User;


public class UserDAO {
	private String jdbcURL = "jdbc:mysql://localhost:3306/demo?useSSL=false";
	private String jdbcUsername = "root";
	private String jdbcPassword = "root";

	private static final String INSERT_USERS_SQL = "INSERT INTO users" + "  (name, email, country) VALUES "
			+ " (?, ?, ?);";

	private static final String SELECt_USER_BY_ID = "select id,name,email,country from users where id =?";
	private static final String SELECt_ALL_USERS = "select * from users";
	private static final String DELETe_USERS_SQL = "delete from users where id = ?;";
	private static final String UPDATE_USERS_SQL = "update users set name = ?,email= ?, country =? where id = ?;";

	public UserDAO() {
	}

	protected Connection getConnection() {
		Connection connection = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection(jdbcURL, jdbcUsername, jdbcPassword);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connection;
	}

	public void insertUser(User user) throws SQLException {
		System.out.println(INSERT_USERS_SQL);
		// try-with-resource statement will auto close the connection.
		try (Connection connection = getConnection();
				PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {
			preparedStatement.setString(1, user.getName());
			preparedStatement.setString(2, user.getEmail());
			preparedStatement.setString(3, user.getCountry());
			System.out.println(preparedStatement);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			printSQLException(e);
		}
	}

	public User selectUser(int id) {
		User user = null;
		// Step 1: Establishing a Connection
		try (Connection connection = getConnection();
				// Step 2:Create a statement using connection object
				PreparedStatement preparedStatement = connection.prepareStatement(SELECT_USER_BY_ID);) {
			preparedStatement.setInt(1, id);
			System.out.println(preparedStatement);
			// Step 3: Execute the query or update query
			ResultSet rs = preparedStatement.executeQuery();

			// Step 4: Process the ResultSet object.
			while (rs.next()) {
				String name = rs.getString("name");
				String email = rs.getString("email");
				String country = rs.getString("country");
				user = new User(id, name, email, country);
			}
		} catch (SQLException e) {
			printSQLException(e);
		}
		return user;
	}

	public List selectAllUsers() {

		// using try-with-resources to avoid closing resources (boiler plate code)
		List users = new ArrayList<>();
		// Step 1: Establishing a Connection
		try (Connection connection = getConnection();

				// Step 2:Create a statement using connection object
			PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL_USERS);) {
			System.out.println(preparedStatement);
			// Step 3: Execute the query or update query
			ResultSet rs = preparedStatement.executeQuery();

			// Step 4: Process the ResultSet object.
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String email = rs.getString("email");
				String country = rs.getString("country");
				users.add(new User(id, name, email, country));
			}
		} catch (SQLException e) {
			printSQLException(e);
		}
		return users;
	}

	public boolean deleteUser(int id) throws SQLException {
		boolean rowDeleted;
		try (Connection connection = getConnection();
				PreparedStatement statement = connection.prepareStatement(DELETE_USERS_SQL);) {
			statement.setInt(1, id);
			rowDeleted = statement.executeUpdate() > 0;
		}
		return rowDeleted;
	}

	public boolean updateUser(User user) throws SQLException {
		boolean rowUpdated;
		try (Connection connection = getConnection();
				PreparedStatement statement = connection.prepareStatement(UPDATE_USERS_SQL);) {
			statement.setString(1, user.getName());
			statement.setString(2, user.getEmail());
			statement.setString(3, user.getCountry());
			statement.setInt(4, user.getId());

			rowUpdated = statement.executeUpdate() > 0;
		}
		return rowUpdated;
	}

	private void printSQLException(SQLException ex) {
		for (Throwable e : ex) {
			if (e instanceof SQLException) {
				e.printStackTrace(System.err);
				System.err.println("SQLState: " + ((SQLException) e).getSQLState());
				System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
				System.err.println("Message: " + e.getMessage());
				Throwable t = ex.getCause();
				while (t != null) {
					System.out.println("Cause: " + t);
					t = t.getCause();
				}
			}
		}
	}

}

7.创建一个UserServlet.java 现在，让我们创建UserServlet作为页面控制器来处理来自客户端的所有请求。我们先来看代码：

package net.javaguides.usermanagement.web;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.RequestDispatcher;
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 net.javaguides.usermanagement.dao.UserDAO;
import net.javaguides.usermanagement.model.User;



@WebServlet("/")
public class UserServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	private UserDAO userDAO;
	
	public void init() {
		userDAO = new UserDAO();
	}

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

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String action = request.getServletPath();

		try {
			switch (action) {
			case "/new":
				showNewForm(request, response);
				break;
			case "/insert":
				insertUser(request, response);
				break;
			case "/delete":
				deleteUser(request, response);
				break;
			case "/edit":
				showEditForm(request, response);
				break;
			case "/update":
				updateUser(request, response);
				break;
			default:
				listUser(request, response);
				break;
			}
		} catch (SQLException ex) {
			throw new ServletException(ex);
		}
	}

	private void listUser(HttpServletRequest request, HttpServletResponse response)
			throws SQLException, IOException, ServletException {
		List listUser = userDAO.selectAllUsers();
		request.setAttribute("listUser", listUser);
		RequestDispatcher dispatcher = request.getRequestDispatcher("user-list.jsp");
		dispatcher.forward(request, response);
	}

	private void showNewForm(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		RequestDispatcher dispatcher = request.getRequestDispatcher("user-form.jsp");
		dispatcher.forward(request, response);
	}

	private void showEditForm(HttpServletRequest request, HttpServletResponse response)
			throws SQLException, ServletException, IOException {
		int id = Integer.parseInt(request.getParameter("id"));
		User existingUser = userDAO.selectUser(id);
		RequestDispatcher dispatcher = request.getRequestDispatcher("user-form.jsp");
		request.setAttribute("user", existingUser);
		dispatcher.forward(request, response);

	}

	private void insertUser(HttpServletRequest request, HttpServletResponse response) 
			throws SQLException, IOException {
		String name = request.getParameter("name");
		String email = request.getParameter("email");
		String country = request.getParameter("country");
		User newUser = new User(name, email, country);
		userDAO.insertUser(newUser);
		response.sendRedirect("list");
	}

	private void updateUser(HttpServletRequest request, HttpServletResponse response) 
			throws SQLException, IOException {
		int id = Integer.parseInt(request.getParameter("id"));
		String name = request.getParameter("name");
		String email = request.getParameter("email");
		String country = request.getParameter("country");

		User book = new User(id, name, email, country);
		userDAO.updateUser(book);
		response.sendRedirect("list");
	}

	private void deleteUser(HttpServletRequest request, HttpServletResponse response) 
			throws SQLException, IOException {
		int id = Integer.parseInt(request.getParameter("id"));
		userDAO.deleteUser(id);
		response.sendRedirect("list");

	}

}

8. 创建用户列表 JSP 页面 - user-list.jsp 接下来，创建一个 JSP 页面来显示数据库中的所有用户。让我们在项目的WebContent目录下创建一个 list-user.jsp页面，代码如下：

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>







	

		
	
	


	
		{message}} -->

		
			
List of Users
			

			

				/new" >Add
					New User
			
			

			

						

				

					

						
ID
						
Name
						
Email
						
Country
						
Actions
					
				
				

					
					

							

							

							

							

							
">Edit
								     ">Delete
						
					
					
				

			
		
	

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

9. 创建用户表单 JSP 页面 - user-form.jsp 接下来，我们创建一个 JSP 页面来创建一个名为user-form.jsp的新用户。这是它的完整源代码：

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>







	

		
	
	

	
		
			
				
					

				
				
					
				

				
					
						
            			Edit User
            		
						
            			Add New User
            		
					
				

				
					
				

				

					User Name 
				

				

					User Email 
				

				

					User Country 
				

				Save
				
			
		
	

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

10. 创建错误 JSP 页面 这是Error.jsp页面 的代码，它只显示异常消息：

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" isErrorPage="true" %>






	
		Error
		<%=exception.getMessage() %>
 
		

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。 附：中文乱码过滤器

import java.io.IOException;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;

public class CharaterEncodingFilter implements Filter {

    public void init(FilterConfig filterConfig) throws ServletException {

    }

    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");
        //response.setContentType("text/html; charset=UTF-8");

        chain.doFilter(request, response);
    }

    public void destroy() {
    }
}

    User Management Web Application

    
        java.lang.Exception
        /Error.jsp
    
    
        CharacterEncodingFilter
        CharaterEncodingFilter
    
    
        CharacterEncodingFilter
        /*