Java JDBC 連接 SQL Server 完整範例：單例連線池、CRUD 封裝、資源管理最佳實務

Maven 依賴

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>12.4.2.jre11</version> <!-- 依 JDK 版本選擇 -->
</dependency>

連線工具類 (單例 + HikariCP 連線池)

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

public final class JdbcUtil {
    private static final HikariDataSource ds;
    static {
        HikariConfig cfg = new HikariConfig();
        cfg.setJdbcUrl("jdbc:sqlserver://127.0.0.1:1433;databaseName=YourDB;encrypt=true;trustServerCertificate=true;");
        cfg.setUsername("sa");
        cfg.setPassword("YourStrongPwd");
        cfg.setMaximumPoolSize(20);
        cfg.setMinimumIdle(5);
        cfg.setConnectionTimeout(30000);
        cfg.setIdleTimeout(600000);
        cfg.setMaxLifetime(1800000);
        cfg.addDataSourceProperty("cachePrepStmts", "true");
        cfg.addDataSourceProperty("prepStmtCacheSize", "250");
        cfg.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        ds = new HikariDataSource(cfg);
    }
    private JdbcUtil() {}
    public static DataSource getDataSource() { return ds; }
    public static Connection getConnection() throws SQLException { return ds.getConnection(); }
    public static void closeQuietly(AutoCloseable... resources) {
        for (AutoCloseable r : resources) {
            if (r != null) try { r.close(); } catch (Exception ignored) {}
        }
    }
}

CRUD 封裝範例

public class UserDao {
    // 查詢單筆
    public User findById(int id) {
        String sql = "SELECT id, username, email FROM users WHERE id = ?";
        try (Connection conn = JdbcUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return new User(rs.getInt("id"), rs.getString("username"), rs.getString("email"));
                }
            }
        } catch (SQLException e) { throw new RuntimeException(e); }
        return null;
    }
    
    // 分頁查詢 (SQL Server 2012+ OFFSET/FETCH)
    public List findPage(int page, int size) {
        String sql = "SELECT id, username, email FROM users ORDER BY id OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
        List list = new ArrayList<>();
        try (Connection conn = JdbcUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, (page-1)*size);
            ps.setInt(2, size);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(new User(rs.getInt("id"), rs.getString("username"), rs.getString("email")));
                }
            }
        } catch (SQLException e) { throw new RuntimeException(e); }
        return list;
    }
    
    // 新增 (回傳自增 ID)
    public int insert(User u) {
        String sql = "INSERT INTO users (username, email) VALUES (?, ?)";
        try (Connection conn = JdbcUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, u.getUsername());
            ps.setString(2, u.getEmail());
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                if (keys.next()) return keys.getInt(1);
            }
        } catch (SQLException e) { throw new RuntimeException(e); }
        return -1;
    }
    
    // 交易範例：轉帳
    public void transfer(int fromId, int toId, double amount) {
        String sql1 = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
        String sql2 = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
        Connection conn = null;
        try {
            conn = JdbcUtil.getConnection();
            conn.setAutoCommit(false);
            try (PreparedStatement ps1 = conn.prepareStatement(sql1);
                 PreparedStatement ps2 = conn.prepareStatement(sql2)) {
                ps1.setDouble(1, amount); ps1.setInt(2, fromId);
                ps2.setDouble(1, amount); ps2.setInt(2, toId);
                ps1.executeUpdate(); ps2.executeUpdate();
            }
            conn.commit();
        } catch (SQLException e) {
            if (conn != null) try { conn.rollback(); } catch (SQLException ignored) {}
            throw new RuntimeException(e);
        } finally { JdbcUtil.closeQuietly(conn); }
    }
}

關鍵最佳實務

1. 連線池：生產環境**必須**使用連線池 (HikariCP/Druid)，避免頻繁建立物理連線。
2. PreparedStatement：防 SQL Injection、執行計畫重用、型別安全。
3. try-with-resources：Java 7+ 自動關閉 Connection/Statement/ResultSet，即使拋出例外。
4. 關閉順序：ResultSet → Statement → Connection (歸還池)。
5. 批次處理：大量寫入用 ps.addBatch() + ps.executeBatch()，配合 rewriteBatchedStatements=true (MySQL) 或 TVP (SQL Server)。
6. 非同步：JDBC 4.2+ CompletableFuture.supplyAsync(() -> dao.findAll()) 避免阻塞業務執行緒。