您现在的位置：程式碼之家 >> 編程語言 > >> Java 程式設計 >> Java 相關知識

JDBC 核心 CRUD 完整實戰：從連線池到 PreparedStatement 的最佳實務

編輯：Java 相關知識

前言

JDBC (Java Database Connectivity) 是 Java 操作關聯式資料庫的基礎 API。雖然實務上常使用 Spring JdbcTemplate、MyBatis、JPA 等框架，但深入理解 JDBC 核心機制（連線管理、Statement 類型、結果集處理、資源釋放）是排查框架底層問題、優化效能的關鍵。本文示範標準化的 JDBC CRUD 完整實作。

核心架構圖

應用程式
    │
    ▼
┌─────────────────┐
│  DataSource     │ ◄── 連線池 (HikariCP / Druid / DBCP)
│  (介面)         │
└────────┬────────┘
         │ getConnection()
         ▼
┌─────────────────┐
│  Connection     │ ◄── 代表資料庫會話、事務邊界
└────────┬────────┘
         │ createStatement() / prepareStatement()
         ▼
┌─────────────────┐
│  Statement      │ ◄── SQL 載體
│  PreparedStatement (防注入、預編譯、效能優)
└────────┬────────┘
         │ executeQuery() / executeUpdate()
         ▼
┌─────────────────┐
│  ResultSet      │ ◄── 查詢結果游標
└─────────────────┘

工具類封裝：JdbcUtils

package cn.com.jdbc;

import javax.sql.DataSource;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.*;

public final class JdbcUtils {
    private static final DataSource DATA_SOURCE;

    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/jdbc_demo?useSSL=false&serverTimezone=UTC");
        config.setUsername("root");
        config.setPassword("123456");
        config.setDriverClassName("com.mysql.cj.jdbc.Driver");
        // 連線池核心參數
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        config.setPoolName("JdbcDemoPool");
        DATA_SOURCE = new HikariDataSource(config);
    }

    public static Connection getConnection() throws SQLException {
        return DATA_SOURCE.getConnection();
    }

    /** 靜默關閉資源，避免 finally 區塊碎片化 */
    public static void closeQuietly(AutoCloseable... resources) {
        for (AutoCloseable res : resources) {
            if (res != null) {
                try { res.close(); } catch (Exception ignored) {}
            }
        }
    }

    /** 取得連線池狀態 (監控用) */
    public static String getPoolStats() {
        if (DATA_SOURCE instanceof HikariDataSource) {
            HikariDataSource ds = (HikariDataSource) DATA_SOURCE;
            return String.format("活躍:%d 空閒:%d 總數:%d 等待:%d",
                ds.getHikariPoolMXBean().getActiveConnections(),
                ds.getHikariPoolMXBean().getIdleConnections(),
                ds.getHikariPoolMXBean().getTotalConnections(),
                ds.getHikariPoolMXBean().getThreadsAwaitingConnection());
        }
        return "N/A";
    }
}

CRUD 完整實作

package cn.com.jdbc;

import java.sql.*;
import java.time.LocalDate;

public class UserCrudDemo {
    public static void main(String[] args) {
        try {
            // createUser("王小明", LocalDate.of(1990, 5, 15), 50000);
            // updateUserSalary(1, 55000);
            // deleteUser(2);
            queryAllUsers();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /** Create - 使用 PreparedStatement 防 SQL 注入 */
    public static void createUser(String name, LocalDate birthday, double salary) throws SQLException {
        String sql = "INSERT INTO users (name, birthday, salary) VALUES (?, ?, ?)";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet generatedKeys = null;
        try {
            conn = JdbcUtils.getConnection();
            ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, name);
            ps.setDate(2, Date.valueOf(birthday));
            ps.setDouble(3, salary);
            int affected = ps.executeUpdate();
            System.out.println("插入成功，影響行數：" + affected);

            // 取得自增主鍵
            generatedKeys = ps.getGeneratedKeys();
            if (generatedKeys.next()) {
                System.out.println("生成的 ID：" + generatedKeys.getLong(1));
            }
        } finally {
            JdbcUtils.closeQuietly(generatedKeys, ps, conn);
        }
    }

    /** Read - 結果集處理 */
    public static void queryAllUsers() throws SQLException {
        String sql = "SELECT id, name, birthday, salary FROM users ORDER BY id";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            
            System.out.printf("%-5s %-10s %-12s %s%n", "ID", "姓名", "生日", "薪資");
            System.out.println("------------------------------------");
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                Date birthday = rs.getDate("birthday");
                double salary = rs.getDouble("salary");
                System.out.printf("%-5d %-10s %-12s %,.0f%n", id, name, birthday, salary);
            }
        } finally {
            JdbcUtils.closeQuietly(rs, ps, conn);
        }
    }

    /** Update - 參數化更新 */
    public static void updateUserSalary(int id, double newSalary) throws SQLException {
        String sql = "UPDATE users SET salary = ? WHERE id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JdbcUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setDouble(1, newSalary);
            ps.setInt(2, id);
            int affected = ps.executeUpdate();
            System.out.println("更新" + (affected > 0 ? "成功" : "失敗(無此ID)") + "，影響行數：" + affected);
        } finally {
            JdbcUtils.closeQuietly(ps, conn);
        }
    }

    /** Delete - 安全刪除 */
    public static void deleteUser(int id) throws SQLException {
        String sql = "DELETE FROM users WHERE id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JdbcUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            int affected = ps.executeUpdate();
            System.out.println("刪除" + (affected > 0 ? "成功" : "失敗(無此ID)") + "，影響行數：" + affected);
        } finally {
            JdbcUtils.closeQuietly(ps, conn);
        }
    }
}

關鍵最佳實務總結

1. 必須使用 PreparedStatement

防 SQL 注入：參數綁定而非字串拼接
效能優化：資料庫端預編譯、執行計劃快取重用
型別安全：setInt/setString/setDate 避免隱性轉換錯誤

2. 資源釋放順序與保證

// 正確順序：ResultSet → Statement → Connection
// 使用 try-with-resources (Java 7+) 自動關閉
try (Connection conn = ds.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql);
     ResultSet rs = ps.executeQuery()) {
    // 業務邏輯
} // 自動按相反順序 close()

3. 連線池必要性

指標	無連線池	HikariCP
獲取連線耗時	50-200ms	< 1ms (池中取)
併發支援	極差	優秀 (配置上限)
洩漏偵測	無	內建 leakDetectionThreshold

4. 事務控制

Connection conn = null;
try {
    conn = ds.getConnection();
    conn.setAutoCommit(false); // 開啟事務
    
    // 多條 SQL...
    ps1.executeUpdate();
    ps2.executeUpdate();
    
    conn.commit(); // 提交
} catch (SQLException e) {
    if (conn != null) conn.rollback(); // 回滾
    throw e;
} finally {
    if (conn != null) {
        conn.setAutoCommit(true); // 恢復預設
        conn.close();
    }
}

5. 批次處理大量資料

String sql = "INSERT INTO logs (msg, created_at) VALUES (?, ?)";
try (Connection conn = ds.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql)) {
    conn.setAutoCommit(false);
    for (int i = 0; i < 10000; i++) {
        ps.setString(1, "Log " + i);
        ps.setTimestamp(2, Timestamp.valueOf(LocalDateTime.now()));
        ps.addBatch();
        if (i % 1000 == 0) ps.executeBatch(); // 分批執行避免記憶體暴漲
    }
    ps.executeBatch();
    conn.commit();
}

常見錯誤與除錯技巧

Communications link failure：檢查資料庫服務、防火牆、連線字串 host/port
No suitable driver found：JDBC 驅動 Jar 未在 Classpath、驅動類名錯誤
Data truncation：欄位長度不足、數值超出範圍、日期格式不符
Lock wait timeout exceeded：長事務、缺少索引導致全表掃描鎖定、死鎖

建表腳本 (MySQL)

CREATE TABLE `users` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `birthday` DATE NULL,
    `salary` DECIMAL(10,2) DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;