JDBC 核心七步曲與最佳實踐：從 DriverManager 到連線池的進化之路

標準 JDBC 七步驟

1. 載入驅動：Class.forName("com.mysql.cj.jdbc.Driver") (JDBC 4.0+ 可省略，SPI 自動載入)
2. 建立連線：DriverManager.getConnection(url, user, pwd)
3. 創建 Statement：PreparedStatement 防注入、預編譯優化
4. 綁定參數：ps.setInt(1, id)、ps.setString(2, name)
5. 執行 SQL：executeQuery()/executeUpdate()
6. 處理結果集：while(rs.next()) { rs.getInt(1); }
7. 釋放資源：try-with-resources 自動關閉 (JDK 7+)

現代化封裝範例

public class DBHelper implements AutoCloseable {
    private static final String URL = "jdbc:mysql://localhost:3306/mybatis?useSSL=false&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASS = "123456";
    
    private final Connection conn;
    private final PreparedStatement ps;
    
    public DBHelper(String sql) throws SQLException {
        this.conn = DriverManager.getConnection(URL, USER, PASS);
        this.ps = conn.prepareStatement(sql);
    }
    
    public PreparedStatement getStatement() { return ps; }
    public ResultSet executeQuery() throws SQLException { return ps.executeQuery(); }
    public int executeUpdate() throws SQLException { return ps.executeUpdate(); }
    
    @Override
    public void close() {
        try { if (ps != null) ps.close(); } catch (SQLException ignore) {}
        try { if (conn != null) conn.close(); } catch (SQLException ignore) {}
    }
}

// 使用範例
try (DBHelper db = new DBHelper("SELECT * FROM user WHERE age > ?")) {
    db.getStatement().setInt(1, 18);
    try (ResultSet rs = db.executeQuery()) {
        while (rs.next()) {
            System.out.printf("ID:%d Name:%s Age:%d%n", 
                rs.getInt("id"), rs.getString("name"), rs.getInt("age"));
        }
    }
}

進階必知：連線池

生產環境嚴禁逐請求建立連線。主流選擇：

• HikariCP：Spring Boot 2.x 預設，效能最強
• Druid：阿里出品，監控功能豐富
• DBCP2 / c3p0：較舊專案常見

# HikariCP 核心配置 (application.yml)
spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      idle-timeout: 300000
      connection-timeout: 30000
      max-lifetime: 1800000
      connection-test-query: SELECT 1

常見坑位避雷