萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> JSP 程式設計

JSP 連接 MySQL 實現數據插入完整實戰:從表單提交到參數化查詢防注入

項目結構

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" %> <-- 純渲染片段 -->
copyright © 萬盛學電腦網 all rights reserved