java jdbc servlet增删改查

### Java JDBC Servlet 实现 CRUD 操作 示例代码 以下是基于 `JSP`、`Servlet` 和 `JDBC` 的简单用户管理系统示例，该系统实现了基本的增删改查（CRUD）功能。 #### 1. 数据库表结构 假设我们有一个名为 `users` 的表，其结构如下： ```sql CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100) ); ``` --- #### 2. 用户实体类 (`User.java`) 定义一个表示用户的 Java 类。 ```java public class User { private int id; private String name; private String email; public User() {} public User(int id, String name, String email) { this.id = id; this.name = name; this.email = email; } // Getters and Setters 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; } } ``` [^3] --- #### 3. 数据访问对象 （DAO） 创建用于执行数据库操作的 DAO 层。 ```java import java.sql.*; import java.util.ArrayList; import java.util.List; public class UserDAO { private Connection getConnection() throws SQLException { return DBUtil.getConnection(); // 使用DBUtil获取连接 } public List<User> getAllUsers() { List<User> userList = new ArrayList<>(); try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users")) { ResultSet rs = stmt.executeQuery(); while (rs.next()) { User user = new User( rs.getInt("id"), rs.getString("name"), rs.getString("email")); userList.add(user); } } catch (SQLException e) { e.printStackTrace(); } return userList; } public boolean addUser(User user) { try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement("INSERT INTO users(name, email) VALUES (?, ?)")) { stmt.setString(1, user.getName()); stmt.setString(2, user.getEmail()); return stmt.executeUpdate() > 0; } catch (SQLException e) { e.printStackTrace(); return false; } } public boolean updateUser(User user) { try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement("UPDATE users SET name=?, email=? WHERE id=?")) { stmt.setString(1, user.getName()); stmt.setString(2, user.getEmail()); stmt.setInt(3, user.getId()); return stmt.executeUpdate() > 0; } catch (SQLException e) { e.printStackTrace(); return false; } } public boolean deleteUser(int userId) { try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement("DELETE FROM users WHERE id=?")) { stmt.setInt(1, userId); return stmt.executeUpdate() > 0; } catch (SQLException e) { e.printStackTrace(); return false; } } } ``` [^1] --- #### 4. Servlet 控制器 编写处理请求并调用 DAO 方法的 Servlet。 ##### 列出所有用户 (`ListUserServlet.java`) ```java import jakarta.servlet.ServletException; import jakarta.servlet.annotation.WebServlet; import jakarta.servlet.http.HttpServlet; import jakarta.servlet.http.HttpServletRequest; import jakarta.servlet.http.HttpServletResponse; import java.io.IOException; import java.util.List; @WebServlet("/list") public class ListUserServlet extends HttpServlet { protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { UserDAO dao = new UserDAO(); List<User> users = dao.getAllUsers(); request.setAttribute("users", users); request.getRequestDispatcher("user-list.jsp").forward(request, response); } } ``` [^2] ##### 添加新用户 (`AddUserServlet.java`) ```java import jakarta.servlet.ServletException; import jakarta.servlet.annotation.WebServlet; import jakarta.servlet.http.HttpServlet; import jakarta.servlet.http.HttpServletRequest; import jakarta.servlet.http.HttpServletResponse; import java.io.IOException; @WebServlet("/add") public class AddUserServlet extends HttpServlet { protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String name = request.getParameter("name"); String email = request.getParameter("email"); User user = new User(0, name, email); // ID will be auto-generated by the database. UserDAO dao = new UserDAO(); if (dao.addUser(user)) { response.sendRedirect("list"); // Redirect to list page after adding a user. } else { response.getWriter().println("Failed to add user."); } } } ``` --- #### 5. 前端页面 (`user-list.jsp`) 展示用户列表的 JSP 页面。 ```html <%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head><title>User List</title></head> <body> <h1>User List</h1> <table border="1"> <tr><th>ID</th><th>Name</th><th>Email</th><th>Action</th></tr> <% for (com.example.model.User u : (List<com.example.model.User>)request.getAttribute("users")) { %> <tr> <td><%=u.getId()%></td> <td><%=u.getName()%></td> <td><%=u.getEmail()%></td> <td> <a href="/edit?id=<%=u.getId()%>">Edit</a> | <a href="/delete?id=<%=u.getId()%>" onclick="return confirm('Are you sure?')">Delete</a> </td> </tr> <% } %> </table> <h2>Add New User</h2> <form action="/add" method="post"> Name: <input type="text" name="name"><br> Email: <input type="text" name="email"><br> <button type="submit">Save</button> </form> </body> </html> ``` [^2] --- ### 总结 以上是一个完整的基于 `JSP`、`Servlet` 和 `JDBC` 的 CRUD 示例。通过这些组件可以轻松实现 Web 应用中的数据持久化和管理功能。