一文带你吃透JSP，增删改查实战案例详细解读
JavaServer Pages (JSP) 是一种基于Java的服务器端技术，常用于创建动态网页。JSP 的一种常见应用场景是与数据库进行交互，执行增删改查（CRUD）操作。本文将通过一个简单的例子来分步讲解如何在JSP中实现增、删、改、查功能。
1. 准备工作
首先，你需要设置一个简单的数据库和一些基本的表。假设我们使用 MySQL 数据库，并创建一个名为 users 的表，表结构如下：
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100)
);
2. 数据库连接
在 JSP 中，数据库连接通常使用 JDBC（Java Database Connectivity）。首先，我们创建一个名为 DBUtil.java 的工具类，用于获取数据库连接：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtil {
private static final String URL = "jdbc:mysql://localhost:3306/yourdb";
private static final String USER = "root";
private static final String PASSWORD = "password";
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USER, PASSWORD);
}
}
3. 展示数据（查询功能）
创建一个 list.jsp 文件来显示所有用户的信息：
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<%@ page import="path.to.DBUtil" %>
<html>
<head><title>User List</title></head>
<body>
<h1>User List</h1>
<a href="add.jsp">Add User</a>
<table border="1">
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Action</th>
</tr>
<%
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT * FROM users");
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
%>
<tr>
<td><%= id %></td>
<td><%= name %></td>
<td><%= email %></td>
<td>
<a href="edit.jsp?id=<%= id %>">Edit</a>
<a href="delete.jsp?id=<%= id %>">Delete</a>
</td>
</tr>
<%
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) try { rs.close(); } catch (SQLException e) {}
if (stmt != null) try { stmt.close(); } catch (SQLException e) {}
if (conn != null) try { conn.close(); } catch (SQLException e) {}
}
%>
</table>
</body>
</html>
4. 添加数据（创建功能）
创建一个 add.jsp 文件，用于添加新用户：
<%@ page import="java.sql.*" %>
<%@ page import="path.to.DBUtil" %>
<html>
<head><title>Add User</title></head>
<body>
<h1>Add User</h1>
<form action="save.jsp" method="post">
Name: <input type="text" name="name" required><br>
Email: <input type="email" name="email" required><br>
<input type="submit" value="Add User">
</form>
</body>
</html>
然后，在 save.jsp 文件中处理表单提交：
<%@ page import="java.sql.*" %>
<%@ page import="path.to.DBUtil" %>
<%
String name = request.getParameter("name");
String email = request.getParameter("email");
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBUtil.getConnection();
String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setString(2, email);
pstmt.executeUpdate();
response.sendRedirect("list.jsp");
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (pstmt != null) try { pstmt.close(); } catch (SQLException e) {}
if (conn != null) try { conn.close(); } catch (SQLException e) {}
}
%>
5. 编辑和更新数据（更新功能）
创建一个 edit.jsp 文件，用于显示用户信息并进行修改：
<%@ page import="java.sql.*" %>
<%@ page import="path.to.DBUtil" %>
<html>
<head><title>Edit User</title></head>
<body>
<%
int id = Integer.parseInt(request.getParameter("id"));
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String name = "";
String email = "";
try {
conn = DBUtil.getConnection();
String sql = "SELECT * FROM users WHERE id=?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
rs = pstmt.executeQuery();
if (rs.next()) {
name = rs.getString("name");
email = rs.getString("email");
}
%>
<h1>Edit User</h1>
<form action="update.jsp" method="post">
<input type="hidden" name="id" value="<%= id %>">
Name: <input type="text" name="name" value="<%= name %>" required><br>
Email: <input type="email" name="email" value="<%= email %>" required><br>
<input type="submit" value="Update User">
</form>
<%
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) try { rs.close(); } catch (SQLException e) {}
if (pstmt != null) try { pstmt.close(); } catch (SQLException e) {}
if (conn != null) try { conn.close(); } catch (SQLException e) {}
}
%>
</body>
</html>
接着创建 update.jsp 来处理更新：
<%@ page import="java.sql.*" %>
<%@ page import="path.to.DBUtil" %>
<%
int id = Integer.parseInt(request.getParameter("id"));
String name = request.getParameter("name");
String email = request.getParameter("email");
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBUtil.getConnection();
String sql = "UPDATE users SET name=?, email=? WHERE id=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setString(2, email);
pstmt.setInt(3, id);
pstmt.executeUpdate();
response.sendRedirect("list.jsp");
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (pstmt != null) try { pstmt.close(); } catch (SQLException e) {}
if (conn != null) try { conn.close(); } catch (SQLException e) {}
}
%>
6. 删除数据（删除功能）
最后，创建一个 delete.jsp 处理删除操作：
<%@ page import="java.sql.*" %>
<%@ page import="path.to.DBUtil" %>
<%
int id = Integer.parseInt(request.getParameter("id"));
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBUtil.getConnection();
String sql = "DELETE FROM users WHERE id=?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.executeUpdate();
response.sendRedirect("list.jsp");
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (pstmt != null) try { pstmt.close(); } catch (SQLException e) {}
if (conn != null) try { conn.close(); } catch (SQLException e) {}
}
%>
结尾
通过以上步骤，我们在 JSP 中实现了一个基本的用户管理系统，涵盖了增删改查功能。尽管本示例相对简单，但它提供了一个良好的起点，帮助你理解 JSP 与 数据库交互的基本概念。实际应用中，建议使用 MVC 设计模式来组织代码，并引入错误处理和输入校验，以提高系统的可维护性和安全性。
