JAVA_HOME、Path）mysql-connector-java-8.0.33.jar 或 Maven 坐標）
CREATE DATABASE jdbc_demo CHARSET utf8mb4;
USE jdbc_demo;
CREATE TABLE t_user (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100)
);
INSERT INTO t_user(username, email) VALUES ('alice','alice@example.com'),('bob','bob@example.com');
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.0.33</version>
</dependency>
非 Maven 專案：將 JAR 放入 WEB-INF/lib 或 Build Path。
import java.sql.*;
import java.util.*;
public class JdbcTemplate {
// 配置常量
private static final String URL = "jdbc:mysql://localhost:3306/jdbc_demo?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8";
private static final String USER = "root";
private static final String PASS = "123456";
// 靜態塊載入驅動（Java 6+ SPI 機制可省略，保留兼容舊版本）
static {
try { Class.forName("com.mysql.cj.jdbc.Driver"); }
catch (ClassNotFoundException e) { throw new ExceptionInInitializerError(e); }
}
// 獲取連接（生產環境請用連接池 HikariCP/Druid）
private static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USER, PASS);
}
// ============ 查詢 ============
public static List<Map<String, Object>> queryList(String sql, Object... params) {
List<Map<String, Object>> result = new ArrayList<>();
try (Connection conn = getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
setParams(ps, params);
try (ResultSet rs = ps.executeQuery()) {
ResultSetMetaData md = rs.getMetaData();
int cols = md.getColumnCount();
while (rs.next()) {
Map<String, Object> row = new LinkedHashMap<>();
for (int i = 1; i <= cols; i++) {
row.put(md.getColumnLabel(i), rs.getObject(i));
}
result.add(row);
}
}
} catch (SQLException e) { throw new RuntimeException(e); }
return result;
}
public static Map<String, Object> queryOne(String sql, Object... params) {
List<Map<String, Object>> list = queryList(sql, params);
return list.isEmpty() ? null : list.get(0);
}
// ============ 增刪改 ============
public static int executeUpdate(String sql, Object... params) {
try (Connection conn = getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
setParams(ps, params);
return ps.executeUpdate();
} catch (SQLException e) { throw new RuntimeException(e); }
}
// 批量執行
public static int[] batchUpdate(String sql, List<Object[]> paramList) {
try (Connection conn = getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
conn.setAutoCommit(false);
for (Object[] params : paramList) {
setParams(ps, params);
ps.addBatch();
}
int[] counts = ps.executeBatch();
conn.commit();
return counts;
} catch (SQLException e) { throw new RuntimeException(e); }
}
// ============ 工具方法 ============
private static void setParams(PreparedStatement ps, Object... params) throws SQLException {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
// ============ Demo ============
public static void main(String[] args) {
// 查詢所有
System.out.println("=== 所有用戶 ===");
queryList("SELECT * FROM t_user").forEach(System.out::println);
// 條件查詢
System.out.println("\n=== ID=1 ===");
System.out.println(queryOne("SELECT * FROM t_user WHERE id=?", 1));
// 新增
int inserted = executeUpdate("INSERT INTO t_user(username,email) VALUES(?,?)", "charlie", "charlie@test.com");
System.out.println("\n新增行數：" + inserted);
// 修改
int updated = executeUpdate("UPDATE t_user SET email=? WHERE username=?", "new@alice.com", "alice");
System.out.println("修改行數：" + updated);
// 刪除
int deleted = executeUpdate("DELETE FROM t_user WHERE username=?", "bob");
System.out.println("刪除行數：" + deleted);
}
}
| 實踐 | 理由 |
|---|---|
| PreparedStatement 防注入 | 參數化查詢，杜絕 SQL 拼接 |
| try-with-resources 自動關閉 | Connection/Statement/ResultSet 實現 AutoCloseable，無需 finally |
| 生產必上連接池 | 頻繁創建銷毀連接極其昂貴，HikariCP 為首選 |
| 事務控制 | conn.setAutoCommit(false) → 操作 → commit()/rollback() |
| 結果集映射 | 手寫 RowMapper 或用 DbUtils/MyBatis 簡化 |
| 時區統一 | URL 帶 serverTimezone=Asia/Shanghai，避免時區跳坑 |
| 異常 | 原因 | 解決 |
|---|---|---|
ClassNotFoundException: com.mysql.cj.jdbc.Driver | 驅動 JAR 未在 Classpath | Maven 依賴 / Build Path 添加 JAR |
Communications link failure | IP/端口/防火牆/MySQL 未啟用遠程 | 檢查 bind-address=0.0.0.0、安全組、FLUSH PRIVILEGES |
Public Key Retrieval is not allowed | MySQL 8 默認 caching_sha2_password | URL 加 allowPublicKeyRetrieval=true 或用戶改 mysql_native_password |
SSL connection error | 驅動默認要求 SSL | URL 加 useSSL=false（開發環境） |
HikariConfig config = new HikariConfig();
config.setJdbcUrl(URL);
config.setUsername(USER);
config.setPassword(PASS);
config.setMaximumPoolSize(20);
DataSource ds = new HikariDataSource(config);
// 後續直接 ds.getConnection()，其餘邏輯不變
JDBC 核心四步：**載入驅動 → 獲取連接 → PreparedStatement 執行 → 釋放資源**。掌握 PreparedStatement 防注入、try-with-resources 自動關閉、**連接池** 這三大支柱，即可編寫健壯、高效的數據訪問代碼。生產項目請直接上 **MyBatis / MyBatis-Plus / JdbcTemplate** 等成熟框架，專注業務邏輯而非樣板代碼。
