JSP 連接 MySQL 數據庫完整代碼示例與最佳實踐

準備工作

1. MySQL 8.0+ 已安裝並運行
2. 下載 mysql-connector-j-8.0.x.jar 放入 WEB-INF/lib/ 或 Tomcat lib/
3. 創建測試數據庫表

數據庫腳本

CREATE DATABASE IF NOT EXISTS jsp_demo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE jsp_demo;

CREATE TABLE IF NOT EXISTS users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO users (username, email, password_hash) VALUES 
('zhangsan', 'zhang@example.com', 'hash1'),
('lisi', 'li@example.com', 'hash2');

基礎版：JSP 直接操作 JDBC (教學/原型用)

警告：生產環境嚴禁在 JSP 中寫 JDBC 代碼！應使用 DAO/Service 層 + 連接池。此處僅示範 API。

<%@ page import="java.sql.*" contentType="text/html;charset=UTF-8" pageEncoding="UTF-8" %>
<%
    // 1. 數據庫連接參數 (生產環境應讀取配置文件)
    String url = "jdbc:mysql://localhost:3306/jsp_demo?" +
                 "useSSL=false&" +
                 "serverTimezone=Asia/Shanghai&" +
                 "allowPublicKeyRetrieval=true&" +
                 "characterEncoding=utf8&" +
                 "useUnicode=true";
    String dbUser = "root";
    String dbPwd = "your_password";

    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {
        // 2. 註冊驅動 (JDBC 4.0+ SPI 自動加載，可省略)
        // Class.forName("com.mysql.cj.jdbc.Driver");

        // 3. 獲取連接
        conn = DriverManager.getConnection(url, dbUser, dbPwd);

        // 4. 預編譯 SQL (防 SQL 注入)
        String sql = "SELECT id, username, email, created_at FROM users WHERE username = ?";
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, "zhangsan"); // 參數綁定

        // 5. 執行查詢
        rs = pstmt.executeQuery();

        // 6. 處理結果集
%>
<table border="1" cellpadding="5">
    <tr><th>ID</th><th>用戶名</th><th>郵箱</th><th>創建時間</th></tr>
    <%
        while (rs.next()) {
    %>
    <tr>
        <td><%= rs.getLong("id") %></td>
        <td><%= rs.getString("username") %></td>
        <td><%= rs.getString("email") %></td>
        <td><%= rs.getTimestamp("created_at") %></td>
    </tr>
    <% } %>
</table>
<%
    } catch (SQLException e) {
        e.printStackTrace();
        out.println("<p style='color:red'>數據庫錯誤: " + e.getMessage() + "</p>");
    } finally {
        // 7. 逆序關閉資源 (關鍵！防連接洩漏)
        try { if (rs != null) rs.close(); } catch (SQLException ignored) {}
        try { if (pstmt != null) pstmt.close(); } catch (SQLException ignored) {}
        try { if (conn != null) conn.close(); } catch (SQLException ignored) {}
    }
%>

進階版：DAO 模式 + 連接池 (生產標準)

1. 連接池配置 (Tomcat context.xml + HikariCP)

<!-- $CATALINA_HOME/conf/context.xml 或 META-INF/context.xml -->
<Resource name="jdbc/jspDemoDS"
          auth="Container"
          type="javax.sql.DataSource"
          factory="com.zaxxer.hikari.HikariJNDIFactory"
          driverClassName="com.mysql.cj.jdbc.Driver"
          jdbcUrl="jdbc:mysql://localhost:3306/jsp_demo?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&characterEncoding=utf8"
          username="root"
          password="your_password"
          maximumPoolSize="20"
          minimumIdle="5"
          connectionTimeout="30000"
          idleTimeout="600000"
          maxLifetime="1800000"
          connectionTestQuery="SELECT 1"
/>

依賴：HikariCP.jar + mysql-connector-j.jar 放入 Tomcat lib/。

2. DAO 類

package com.example.dao;

import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    private DataSource dataSource;

    public UserDao() {
        try {
            InitialContext ctx = new InitialContext();
            dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/jspDemoDS");
        } catch (Exception e) {
            throw new RuntimeException("DataSource 初始化失敗", e);
        }
    }

    public List findAll() {
        String sql = "SELECT id, username, email, created_at FROM users";
        List list = new ArrayList<>();

        // Try-with-resources 自動關閉 (Java 7+)
        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                User u = new User();
                u.setId(rs.getLong("id"));
                u.setUsername(rs.getString("username"));
                u.setEmail(rs.getString("email"));
                u.setCreatedAt(rs.getTimestamp("created_at"));
                list.add(u);
            }
        } catch (SQLException e) {
            throw new RuntimeException("查詢用戶列表失敗", e);
        }
        return list;
    }

    public void insert(User user) {
        String sql = "INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getEmail());
            pstmt.setString(3, user.getPasswordHash());
            pstmt.executeUpdate();

            try (ResultSet keys = pstmt.getGeneratedKeys()) {
                if (keys.next()) user.setId(keys.getLong(1));
            }
        } catch (SQLException e) {
            throw new RuntimeException("插入用戶失敗", e);
        }
    }
}

3. 實體類

package com.example.model;

import java.sql.Timestamp;

public class User {
    private Long id;
    private String username;
    private String email;
    private String passwordHash;
    private Timestamp createdAt;
    // Getter/Setter/ToString 省略
}

4. JSP 使用 DAO (極簡)

<%@ page import="com.example.dao.UserDao, com.example.model.User, java.util.List" 
         contentType="text/html;charset=UTF-8" pageEncoding="UTF-8" %>
<%
    UserDao dao = new UserDao();
    List<User> users = dao.findAll();
    request.setAttribute("users", users); // 供 EL 使用
%>
<%@ taglib prefix="c" uri="jakarta.tags.core" %>
<table border="1">
    <tr><th>ID</th><th>用戶名</th><th>郵箱</th><th>創建時間</th></tr>
    <c:forEach items="${users}" var="u">
    <tr>
        <td>${u.id}</td>
        <td><c:out value="${u.username}"/></td>
        <td><c:out value="${u.email}"/></td>
        <td>${u.createdAt}</td>
    </tr>
    </c:forEach>
</table>

關鍵最佳實踐總結

常見錯誤排查