Java JDBC 連線 MySQL 完整 CRUD 範例：連線池、PreparedStatement 與資源釋放最佳實踐

Java 連線 MySQL 實戰：標準 CRUD 操作、連線池與資源管理完整範例

JDBC 是 Java 操作關聯式資料庫的基礎 API。本文提供生產級的連線管理、增刪改查完整程式碼範例，強調 PreparedStatement 防 SQL 注入、try-with-resources 自動釋放資源、連線池 (HikariCP) 效能優化等最佳實踐。

一、資料庫連線工具類 (含連線池)

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

public class DataSourceUtil {
    private static final HikariDataSource dataSource;

    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC&characterEncoding=utf8");
        config.setUsername("root");
        config.setPassword("password");
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        dataSource = new HikariDataSource(config);
    }

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

    public static void close() {
        if (dataSource != null) dataSource.close();
    }
}

二、通用 CRUD 基礎類 (BaseDAO)

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public abstract class BaseDAO {
    // 通用查詢：回傳單一物件
    protected T queryForObject(String sql, RowMapper mapper, Object... params) {
        try (Connection conn = DataSourceUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            setParams(ps, params);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) return mapper.mapRow(rs);
            }
        } catch (SQLException e) { throw new RuntimeException(e); }
        return null;
    }

    // 通用查詢：回傳列表
    protected List queryForList(String sql, RowMapper mapper, Object... params) {
        List list = new ArrayList<>();
        try (Connection conn = DataSourceUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            setParams(ps, params);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) list.add(mapper.mapRow(rs));
            }
        } catch (SQLException e) { throw new RuntimeException(e); }
        return list;
    }

    // 通用增/刪/改：回傳影響行數
    protected int executeUpdate(String sql, Object... params) {
        try (Connection conn = DataSourceUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            setParams(ps, params);
            return ps.executeUpdate();
        } catch (SQLException e) { throw new RuntimeException(e); }
    }

    private void setParams(PreparedStatement ps, Object... params) throws SQLException {
        for (int i = 0; i < params.length; i++) {
            ps.setObject(i + 1, params[i]);
        }
    }

    @FunctionalInterface
    interface RowMapper {
        T mapRow(ResultSet rs) throws SQLException;
    }
}

三、具體業務 DAO 實作範例

public class UserDAO extends BaseDAO {
    // 新增
    public int insert(User user) {
        String sql = "INSERT INTO users(username, email, age) VALUES(?, ?, ?)";
        return executeUpdate(sql, user.getUsername(), user.getEmail(), user.getAge());
    }

    // 批次新增 (效能優化)
    public int[] batchInsert(List users) {
        String sql = "INSERT INTO users(username, email, age) VALUES(?, ?, ?)";
        try (Connection conn = DataSourceUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            for (User u : users) {
                ps.setString(1, u.getUsername());
                ps.setString(2, u.getEmail());
                ps.setInt(3, u.getAge());
                ps.addBatch();
            }
            return ps.executeBatch();
        } catch (SQLException e) { throw new RuntimeException(e); }
    }

    // 查詢單筆
    public User findById(int id) {
        String sql = "SELECT id, username, email, age FROM users WHERE id = ?";
        return queryForObject(sql, rs -> new User(
            rs.getInt("id"),
            rs.getString("username"),
            rs.getString("email"),
            rs.getInt("age")
        ), id);
    }

    // 更新
    public int update(User user) {
        String sql = "UPDATE users SET username=?, email=?, age=? WHERE id=?";
        return executeUpdate(sql, user.getUsername(), user.getEmail(), user.getAge(), user.getId());
    }

    // 刪除
    public int deleteById(int id) {
        String sql = "DELETE FROM users WHERE id = ?";
        return executeUpdate(sql, id);
    }
}

四、關鍵最佳實踐總結

五、Maven 依賴座標

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.0.1</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

進階建議： 實際專案建議使用 MyBatis/JPA 等 ORM 框架減少樣板碼，但理解 JDBC 底層機制對排查連線洩漏、死鎖、效能問題至關重要。