JDBC (Java Database Connectivity) 是 Java 訪問關係型數據庫的標準 API。本文系統講解 DriverManager、Connection、Statement/PreparedStatement/CallableStatement、ResultSet、事務控制、連接池（HikariCP/Druid）六大核心模塊，附完整 CRUD 示例、SQL 注入防範、資源管理模式與生產級配置建議。
// 1. 加載驅動 (JDK 6+ SPI 機制自動加載，可省略)
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. 建立連接
String url = "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC";
String user = "root", pwd = "123456";
Connection conn = DriverManager.getConnection(url, user, pwd);
// 3. 執行 SQL
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM users");
// 4. 處理結果 / 釋放資源
while (rs.next()) { System.out.println(rs.getString("name")); }
rs.close(); stmt.close(); conn.close();
String sql = "INSERT INTO users(name, email, age) VALUES(?, ?, ?)";
try (Connection conn = getConnection();
PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
ps.setString(1, "張三"); // 索引從 1 開始
ps.setString(2, "zhang@example.com");
ps.setInt(3, 25);
int affected = ps.executeUpdate();
System.out.println("插入行數: " + affected);
// 獲取自增主鍵
try (ResultSet keys = ps.getGeneratedKeys()) {
if (keys.next()) System.out.println("新 ID: " + keys.getLong(1));
}
}
核心優勢：
setInt/setString/setTimestamp/setNull 等強類型方法。addBatch()/executeBatch() 大幅提升批量寫入性能。| 方法 | 說明 |
|---|---|
next() | 移動到下一行，返回 boolean |
getXxx(int colIndex) / getXxx(String colName) | 提取列值：getString/getInt/getLong/getDouble/getTimestamp/getBoolean |
wasNull() | 判斷上次讀取是否為 SQL NULL |
findColumn(String name) | 列名轉索引（避免重複調用） |
absolute(row)/relative(rows)/beforeFirst()/afterLast() | 可滾動結果集導航（需 TYPE_SCROLL_INSENSITIVE） |
updateXxx()/insertRow()/deleteRow()/refreshRow() | 可更新結果集操作（需 CONCUR_UPDATABLE） |
Connection conn = null;
try {
conn = getConnection();
conn.setAutoCommit(false); // 開啟事務
// 業務邏輯：多條 SQL 原子執行
transfer(conn, fromId, toId, amount); // 扣款
transfer(conn, toId, fromId, amount); // 入款
conn.commit(); // 提交事務
} catch (Exception e) {
if (conn != null) conn.rollback(); // 回滾
throw new RuntimeException("轉賬失敗", e);
} finally {
if (conn != null) {
try { conn.setAutoCommit(true); } catch (SQLException ignored) {}
close(conn);
}
}
隔離級別：conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED) — 解決臟讀/不可重複讀/幻讀權衡。
為什麼需要：創建連接耗時 (建立 TCP、握手、認證)，頻繁開關導致性能差、數據庫連接數耗盡。
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb?useSSL=false");
config.setUsername("root");
config.setPassword("123456");
config.setMaximumPoolSize(20); // 最大連接數
config.setMinimumIdle(5); // 最小空閒
config.setIdleTimeout(300000); // 空閒超時 5 分
config.setMaxLifetime(1800000); // 連接最大壽命 30 分
config.setConnectionTimeout(30000); // 獲取連接超時 30 秒
config.setPoolName("MyApp-Pool");
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
DataSource ds = new HikariDataSource(config);
// 使用
try (Connection conn = ds.getConnection()) { ... }
DruidDataSource ds = new DruidDataSource();
ds.setUrl("jdbc:mysql://localhost:3306/mydb");
ds.setUsername("root");
ds.setPassword("123456");
ds.setInitialSize(5);
ds.setMaxActive(20);
ds.setMinIdle(5);
ds.setMaxWait(60000);
ds.setValidationQuery("SELECT 1");
ds.setTestOnBorrow(false);
ds.setTestOnReturn(false);
ds.setTestWhileIdle(true);
ds.setTimeBetweenEvictionRunsMillis(60000);
// 內建 StatViewServlet 監控頁面：/druid/login.html
public class UserDao {
private final DataSource ds;
public UserDao(DataSource ds) { this.ds = ds; }
public long insert(User u) {
String sql = "INSERT INTO users(name,email,age) VALUES(?,?,?)";
try (Connection conn = ds.getConnection();
PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
ps.setString(1, u.getName());
ps.setString(2, u.getEmail());
ps.setInt(3, u.getAge());
ps.executeUpdate();
try (ResultSet keys = ps.getGeneratedKeys()) {
return keys.next() ? keys.getLong(1) : -1;
}
} catch (SQLException e) { throw new RuntimeException(e); }
}
public Optional findById(long id) {
String sql = "SELECT id,name,email,age FROM users WHERE id=?";
try (Connection conn = ds.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setLong(1, id);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? Optional.of(mapUser(rs)) : Optional.empty();
}
} catch (SQLException e) { throw new RuntimeException(e); }
}
public List findAll() {
String sql = "SELECT id,name,email,age FROM users";
try (Connection conn = ds.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
List list = new ArrayList<>();
while (rs.next()) list.add(mapUser(rs));
return list;
} catch (SQLException e) { throw new RuntimeException(e); }
}
public int update(User u) {
String sql = "UPDATE users SET name=?,email=?,age=? WHERE id=?";
try (Connection conn = ds.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, u.getName());
ps.setString(2, u.getEmail());
ps.setInt(3, u.getAge());
ps.setLong(4, u.getId());
return ps.executeUpdate();
} catch (SQLException e) { throw new RuntimeException(e); }
}
public int deleteById(long id) {
String sql = "DELETE FROM users WHERE id=?";
try (Connection conn = ds.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setLong(1, id);
return ps.executeUpdate();
} catch (SQLException e) { throw new RuntimeException(e); }
}
private User mapUser(ResultSet rs) throws SQLException {
User u = new User();
u.setId(rs.getLong("id"));
u.setName(rs.getString("name"));
u.setEmail(rs.getString("email"));
u.setAge(rs.getInt("age"));
return u;
}
}
throw new RuntimeException("業務含義", e)。active/idle/usage 指標。slowQueryThresholdMs / P6Spy。SELECT id,name 而非 SELECT *，減少網絡/內存/映射開銷。@Transactional(readOnly=true) 或 conn.setReadOnly(true)。JDBC 雖底層，但掌握其核心 API、事務邊界、連接池調優與資源管理模式，是上層 ORM (MyBatis/JPA/Hibernate) 高效使用的基石。建議手寫一遍本文 UserDao，體會 try-with-resources 與 PreparedStatement 的協作之美。
