JSP 連接 MySQL 是 Java Web 開發的入門必修課。本文從驅動安裝、連線池配置、DAO 封裝、事務管理、防注入最佳實踐五個維度,構建生產可用的資料存取層。採用 **MySQL Connector/J 8.0+** (官方 JDBC 驅動) + **HikariCP** (高效能連線池) + **DAO 模式** 現代化技術棧。
<dependencies>
<!-- MySQL 官方 JDBC 驅動 8.0+ -->
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.2.0</version>
</dependency>
<!-- HikariCP 高效能連線池 -->
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>5.1.0</version>
</dependency>
<!-- Servlet API (provided 由容器提供) -->
<dependency>
<groupId>jakarta.servlet</groupId>
<artifactId>jakarta.servlet-api</artifactId>
<version>6.0.0</version>
<scope>provided</scope>
</dependency>
</dependencies>
舊版專案若用mysql-connector-java(5.1.x/8.0.x) 群組 ID 為mysql,8.0+ 統一遷移至com.mysql。
| 版本 | Driver Class | URL 格式 | 關鍵參數 |
|---|---|---|---|
| 5.1.x | com.mysql.jdbc.Driver | jdbc:mysql://host:3306/db | useSSL=false, characterEncoding=utf8 |
| 8.0+ | com.mysql.cj.jdbc.Driver | jdbc:mysql://host:3306/db | useSSL=false, serverTimezone=Asia/Taipei, allowPublicKeyRetrieval=true |
生產建議 URL:
jdbc:mysql://127.0.0.1:3306/myapp?useSSL=false&serverTimezone=Asia/Taipei&allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=utf8mb4&autoReconnect=true&failOverReadOnly=false&maxReconnects=3
package com.example.config;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import jakarta.servlet.ServletContextEvent;
import jakarta.servlet.ServletContextListener;
import jakarta.servlet.annotation.WebListener;
@WebListener
public class DataSourceInitializer implements ServletContextListener {
private static HikariDataSource dataSource;
@Override
public void contextInitialized(ServletContextEvent sce) {
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/myapp?useSSL=false&serverTimezone=Asia/Taipei&allowPublicKeyRetrieval=true&characterEncoding=utf8mb4");
config.setUsername("app_user");
config.setPassword("secure_password");
// 核心池大小 = CPU 核心數 * 2 + 1 (經驗值)
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000); // 30秒取得連線逾時
config.setIdleTimeout(600000); // 10分鐘閒置回收
config.setMaxLifetime(1800000); // 30分鐘強制換新連線
config.setConnectionTestQuery("SELECT 1"); // 心跳檢查
config.setPoolName("MyApp-HikariCP");
// 效能優化參數
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
config.addDataSourceProperty("useServerPrepStmts", "true");
dataSource = new HikariDataSource(config);
sce.getServletContext().setAttribute("dataSource", dataSource);
}
@Override
public void contextDestroyed(ServletContextEvent sce) {
if (dataSource != null && !dataSource.isClosed()) {
dataSource.close(); // 關鍵:優雅關閉釋放資源
}
}
public static HikariDataSource getDataSource() { return dataSource; }
}
package com.example.dao;
import com.example.config.DataSourceInitializer;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.function.Function;
public abstract class BaseDAO {
protected final HikariDataSource ds = DataSourceInitializer.getDataSource();
// 通用查詢模板
protected List query(String sql, Function mapper, Object... params) {
List results = new ArrayList<>();
try (Connection conn = ds.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
bindParams(ps, params);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) results.add(mapper.apply(rs));
}
} catch (SQLException e) {
throw new RuntimeException("Query failed: " + sql, e);
}
return results;
}
// 通用更新模板 (INSERT/UPDATE/DELETE)
protected int update(String sql, Object... params) {
try (Connection conn = ds.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
bindParams(ps, params);
return ps.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException("Update failed: " + sql, e);
}
}
// 取得自增主鍵
protected long insertAndGetKey(String sql, Object... params) {
try (Connection conn = ds.getConnection();
PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
bindParams(ps, params);
ps.executeUpdate();
try (ResultSet rs = ps.getGeneratedKeys()) {
return rs.next() ? rs.getLong(1) : -1;
}
} catch (SQLException e) {
throw new RuntimeException("Insert failed: " + sql, e);
}
}
private void bindParams(PreparedStatement ps, Object[] params) throws SQLException {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
}
// 實體類 (POJO / JavaBean)
public class User {
private Long id;
private String username;
private String email;
private String passwordHash; // 僅存雜湊,不存明文
private java.time.LocalDateTime createdAt;
// Getter/Setter/Builder/toString 略
}
// DAO 實作
public class UserDAO extends BaseDAO {
private static final String TABLE = "users";
private static final String COLUMNS = "username, email, password_hash, created_at";
// RowMapper 邏輯內聚
private User mapRow(ResultSet rs) throws SQLException {
User u = new User();
u.setId(rs.getLong("id"));
u.setUsername(rs.getString("username"));
u.setEmail(rs.getString("email"));
u.setPasswordHash(rs.getString("password_hash"));
u.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
return u;
}
public User findById(Long id) {
return query("SELECT * FROM " + TABLE + " WHERE id = ?", this::mapRow, id).stream().findFirst().orElse(null);
}
public User findByUsername(String username) {
return query("SELECT * FROM " + TABLE + " WHERE username = ?", this::mapRow, username).stream().findFirst().orElse(null);
}
public List findAll(int offset, int limit) {
return query("SELECT * FROM " + TABLE + " ORDER BY created_at DESC LIMIT ? OFFSET ?", this::mapRow, limit, offset);
}
public long insert(User user) {
return insertAndGetKey("INSERT INTO " + TABLE + " (" + COLUMNS + ") VALUES (?, ?, ?, ?)",
user.getUsername(), user.getEmail(), user.getPasswordHash(), user.getCreatedAt());
}
public boolean updateEmail(Long id, String newEmail) {
return update("UPDATE " + TABLE + " SET email = ? WHERE id = ?", newEmail, id) > 0;
}
}
<%-- userList.jsp --%>
<%@ page import="com.example.dao.*, com.example.model.*, java.util.List" %>
<%@ taglib prefix="c" uri="jakarta.tags.core" %>
<%
UserDAO dao = new UserDAO();
List users = dao.findAll(0, 20);
request.setAttribute("users", users);
%>
<table>
<c:forEach items="${users}" var="u">
<tr><td>${u.id}</td><td>${u.username}</td><td>${u.email}</td></tr>
</c:forEach>
</table>
cachePrepStmts)。ServletContextListener 生命週期管理,避免重複建立池或關閉遺漏。-Duser.timezone=Asia/Taipei、連線 URL serverTimezone=Asia/Taipei、資料庫 time_zone、Java 使用 LocalDateTime/ZonedDateTime。Connection.setAutoCommit(false) + commit/rollback。從「JSP 內寫 JDBC 代碼」進化到「連線池 + DAO 模式 + 參數化查詢 + 異常統一處理」,是 Java Web 資料存取層工程化的標準路徑。掌握此模式,再引入 MyBatis/JPA/Spring Data JPA 等 ORM 框架時,即能理解其底層原理與配置意圖,不再是單純的 API 調用者。