項目結構
webapp/
├── index.jsp # 數據錄入表單 (UTF-8)
├── mysql_insert.jsp # 接收表單、入庫、回顯列表
└── WEB-INF/
├── lib/
│ └── mysql-connector-java-8.0.x.jar
└── web.xml
1. 表單頁 index.jsp (UTF-8 全鏈路)
<%@ page language="java" contentType="text/html;charset=UTF-8" pageEncoding="UTF-8" %>
<%
// 雙重保險:Filter 未生效時兜底
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>用戶信息錄入</title>
<style>
body {font-family: sans-serif; max-width: 500px; margin: 2rem auto;}
.form-group {margin-bottom: 1rem;}
label {display: inline-block; width: 80px; text-align: right; margin-right: 10px;}
input, select {padding: 8px; border: 1px solid #ddd; border-radius: 4px;}
button {padding: 10px 20px; background: #28a745; color: white; border: none; border-radius: 4px; cursor: pointer;}
</style>
</head>
<body>
<h2>新增用戶</h2>
<form action="mysql_insert.jsp" method="post">
<div class="form-group">
<label>ID:</label>
<input type="text" name="id" value="0" required/>
</div>
<div class="form-group">
<label>姓名:</label>
<input type="text" name="name" value="" required/>
</div>
<div class="form-group">
<label>性別:</label>
<select name="sex">
<option value="male">男</option>
<option value="female">女</option>
</select>
</div>
<div class="form-group">
<label>年齡:</label>
<input type="number" name="age" value="20" min="1" max="120"/>
</div>
<button type="submit">提交並查看列表</button>
</form>
</body></html>
2. 處理頁 mysql_insert.jsp (核心邏輯)
<%@ page language="java" contentType="text/html;charset=UTF-8" pageEncoding="UTF-8" %>
<%@ page import="java.sql.*, javax.sql.*, java.util.*" %>
<%
request.setCharacterEncoding("UTF-8"); // 兜底
// 1. 獲取參數
String id = request.getParameter("id");
String name = request.getParameter("name");
String sex = request.getParameter("sex");
String ageStr = request.getParameter("age");
// 2. 基礎校驗
if (name == null || name.trim().isEmpty()) {
out.println("<div style='color:red'>姓名不能為空</div>");
return;
}
int age = 0;
try { age = Integer.parseInt(ageStr); }
catch (NumberFormatException e) { out.println("<div style='color:red'>年齡格式錯誤</div>"); return; }
// 3. 數據庫操作 (try-with-resources 自動關閉資源)
String url = "jdbc:mysql://localhost:3306/html_db?"
+ "useUnicode=true&characterEncoding=utf8mb4&serverTimezone=Asia/Shanghai";
String user = "root", pass = "123456";
String insertSql = "INSERT INTO person_tb (id, name, sex, age) VALUES (?, ?, ?, ?)";
String querySql = "SELECT id, name, sex, age FROM person_tb ORDER BY id DESC LIMIT 20";
try (Connection conn = DriverManager.getConnection(url, user, pass);
PreparedStatement psInsert = conn.prepareStatement(insertSql);
PreparedStatement psQuery = conn.prepareStatement(querySql)) {
// 參數化設置 (防 SQL 注入 + 自動編碼處理)
psInsert.setString(1, id);
psInsert.setString(2, name);
psInsert.setString(3, sex);
psInsert.setInt(4, age);
psInsert.executeUpdate();
// 4. 查詢回顯
%>
<h2>最新數據列表</h2>
<table border="1" cellpadding="8" cellspacing="0" style="border-collapse:collapse; width:100%;">
<thead style="background:#f0f0f0;">
<tr><th>ID</th><th>姓名</th><th>性別</th><th>年齡</th></tr>
</thead>
<tbody>
<%
try (ResultSet rs = psQuery.executeQuery()) {
while (rs.next()) {
%>
<tr>
<td><%= rs.getString("id") %></td>
<td><%= rs.getString("name") %></td>
<td><%= rs.getString("sex") %></td>
<td><%= rs.getInt("age") %></td>
</tr>
<%
}
}
%>
</tbody>
</table>
<%
} catch (SQLException e) {
e.printStackTrace();
out.println("<div style='color:red'>數據庫錯誤: " + e.getMessage() + "</div>");
}
%>
<p><a href="index.jsp">返回繼續新增</a></p>
</body></html>
3. 數據庫準備 (utf8mb4)
CREATE DATABASE IF NOT EXISTS html_db
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE html_db;
CREATE TABLE IF NOT EXISTS person_tb (
id VARCHAR(20) PRIMARY KEY,
name VARCHAR(50) NOT NULL,
sex VARCHAR(10),
age INT,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4. 關鍵最佳實踐總結
| 環節 | 錯誤做法 | 正確做法 | 理由 |
| 編碼 | 頁面 GB2312、處理頁 UTF-8、DB latin1 | **全鏈路 UTF-8 (utf8mb4)** | 避免多次轉碼丟失信息 |
| 獲取參數 | 直接 `getParameter()` | **Filter 統一 `setCharacterEncoding("UTF-8")`** | POST Body 解碼正確 |
| SQL 執行 | 字符串拼接 `VALUES ('"+name+"')` | **PreparedStatement 參數化 `?`** | 防 SQL 注入、自動處理特殊字符、性能更好 |
| 資源管理 | 手動 close、異常路徑漏關 | **try-with-resources (Java 7+)** | 保證連接/語句/結果集 100% 關閉 |
| 連接管理 | 每請求新建 Connection | **連接池 (HikariCP/Druid/DBPool)** | 性能、穩定、可監控 |
| 業務邏輯 | JSP 中寫 SQL、事務 | **DAO/Service 分層,JSP 只渲染** | 可測試、可復用、易維護 |
5. 進階:引入連接池 (HikariCP) + DAO 模式
<!-- web.xml 配置 DataSource (Tomcat JNDI) -->
<Resource name="jdbc/html_db" auth="Container" type="javax.sql.DataSource"
driverClassName="com.mysql.cj.jdbc.Driver"
url="jdbc:mysql://localhost:3306/html_db?useUnicode=true&characterEncoding=utf8mb4&serverTimezone=Asia/Shanghai"
username="root" password="123456"
maxTotal="20" maxIdle="10" maxWaitMillis="5000"/>
// Dao 類
public class UserDao {
private DataSource ds;
public UserDao() {
try { ds = (DataSource) new InitialContext().lookup("java:comp/env/jdbc/html_db"); }
catch (NamingException e) { throw new RuntimeException(e); }
}
public void insert(User u) {
String sql = "INSERT INTO person_tb (id, name, sex, age) VALUES (?, ?, ?, ?)";
try (Connection conn = ds.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, u.getId());
ps.setString(2, u.getName());
ps.setString(3, u.getSex());
ps.setInt(4, u.getAge());
ps.executeUpdate();
} catch (SQLException e) { throw new RuntimeException(e); }
}
public List findLatest(int limit) {
String sql = "SELECT * FROM person_tb ORDER BY id DESC LIMIT ?";
try (Connection conn = ds.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, limit);
try (ResultSet rs = ps.executeQuery()) {
List list = new ArrayList<>();
while (rs.next()) list.add(mapRow(rs));
return list;
}
} catch (SQLException e) { throw new RuntimeException(e); }
}
private User mapRow(ResultSet rs) throws SQLException { /* ... */ }
}
<%-- mysql_insert.jsp 簡化版 --%>
<%@ page import="com.example.dao.UserDao, com.example.model.User" %>
<%
UserDao dao = new UserDao();
User u = new User(id, name, sex, age);
dao.insert(u);
request.setAttribute("users", dao.findLatest(20));
%>
<%@ include file="/WEB-INF/views/user/list.jspf" %> <-- 純渲染片段 -->