查看 使用 JSP、Servlet、JDBC 和 MySQL 构建 Todo 应用程序。
我们将在我们的用户管理Web 应用程序中开发以下简单的基本功能:
- 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)
- );
-
- package net.javaguides.usermanagement.model;
-
- /**
- * User.java
- * This is a model class represents a User entity
- * @author Ramesh Fadatare
- *
- */
- 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;
- }
- }
- 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;
-
- /**
- * AbstractDAO.java This DAO class provides CRUD database operations for the
- * table users in the database.
- *
- * @author Ramesh Fadatare
- *
- */
- 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();
- }
- }
- }
- }
-
- }
- 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;
-
- /**
- * ControllerServlet.java
- * This servlet acts as a page controller for the application, handling all
- * requests from the user.
- * @email Ramesh Fadatare
- */
-
- @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");
-
- }
-
- }
- <%@ page language="java" contentType="text/html; charset=UTF-8"
- pageEncoding="UTF-8"%>
- <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
- <html>
- <head>
- <title>User Management Applicationtitle>
- <link rel="stylesheet"
- href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
- integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T"
- crossorigin="anonymous">
- head>
- <body>
-
- <header>
- <nav class="navbar navbar-expand-md navbar-dark"
- style="background-color: tomato">
- <div>
- <a href="https://www.javaguides.net" class="navbar-brand"> User
- Management App a>
- div>
-
- <ul class="navbar-nav">
- <li><a href="<%=request.getContextPath()%>/list"
- class="nav-link">Usersa>li>
- ul>
- nav>
- header>
- <br>
-
- <div class="row">
-
-
- <div class="container">
- <h3 class="text-center">List of Usersh3>
- <hr>
- <div class="container text-left">
-
- <a href="<%=request.getContextPath()%>/new" class="btn btn-success">Add
- New Usera>
- div>
- <br>
- <table class="table table-bordered">
- <thead>
- <tr>
- <th>IDth>
- <th>Nameth>
- <th>Emailth>
- <th>Countryth>
- <th>Actionsth>
- tr>
- thead>
- <tbody>
-
- <c:forEach var="user" items="${listUser}">
-
- <tr>
- <td><c:out value="${user.id}" />td>
- <td><c:out value="${user.name}" />td>
- <td><c:out value="${user.email}" />td>
- <td><c:out value="${user.country}" />td>
- <td><a href="edit?id=
">Edita> - <a
- href="delete?id=
">Deletea>td> - tr>
- c:forEach>
-
- tbody>
-
- table>
- div>
- div>
- body>
- html>
- <%@ page language="java" contentType="text/html; charset=UTF-8"
- pageEncoding="UTF-8"%>
- <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
- <html>
- <head>
- <title>User Management Applicationtitle>
- <link rel="stylesheet"
- href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
- integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T"
- crossorigin="anonymous">
- head>
- <body>
-
- <header>
- <nav class="navbar navbar-expand-md navbar-dark"
- style="background-color: tomato">
- <div>
- <a href="https://www.javaguides.net" class="navbar-brand"> User Management App a>
- div>
-
- <ul class="navbar-nav">
- <li><a href="<%=request.getContextPath()%>/list"
- class="nav-link">Usersa>li>
- ul>
- nav>
- header>
- <br>
- <div class="container col-md-5">
- <div class="card">
- <div class="card-body">
- <c:if test="${user != null}">
- <form action="update" method="post">
- c:if>
- <c:if test="${user == null}">
- <form action="insert" method="post">
- c:if>
-
- <caption>
- <h2>
- <c:if test="${user != null}">
- Edit User
- c:if>
- <c:if test="${user == null}">
- Add New User
- c:if>
- h2>
- caption>
-
- <c:if test="${user != null}">
- <input type="hidden" name="id" value="
" /> - c:if>
-
- <fieldset class="form-group">
- <label>User Namelabel> <input type="text"
- value="
" class="form-control" - name="name" required="required">
- fieldset>
-
- <fieldset class="form-group">
- <label>User Emaillabel> <input type="text"
- value="
" class="form-control" - name="email">
- fieldset>
-
- <fieldset class="form-group">
- <label>User Countrylabel> <input type="text"
- value="
" class="form-control" - name="country">
- fieldset>
-
- <button type="submit" class="btn btn-success">Savebutton>
- form>
- div>
- div>
- div>
- body>
- html>
- <%@ page language="java" contentType="text/html; charset=UTF-8"
- pageEncoding="UTF-8" isErrorPage="true" %>
- html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
- "http://www.w3.org/TR/html4/loose.dtd">
- <html>
- <head>
- <title>Errortitle>
- head>
- <body>
- <center>
- <h1>Errorh1>
- <h2><%=exception.getMessage() %><br/> h2>
- center>
- body>
- html>
-
- 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() {
- }
- }
- <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
- xmlns="http://xmlns.jcp.org/xml/ns/javaee"
- xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee
- http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
- id="WebApp_ID" version="3.1">
- <display-name>User Management Web Applicationdisplay-name>
-
- <error-page>
- <exception-type>java.lang.Exceptionexception-type>
- <location>/Error.jsplocation>
- error-page>
- <filter>
- <filter-name>CharacterEncodingFilterfilter-name>
- <filter-class>CharaterEncodingFilterfilter-class>
- filter>
- <filter-mapping>
- <filter-name>CharacterEncodingFilterfilter-name>
- <url-pattern>/*url-pattern>
- filter-mapping>
- web-app>