萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> 程式語言綜合

Java 連接 SQL Server 數據庫完整代碼模板(JDBC 最佳實踐)

Java 通過 JDBC 連接 SQL Server,推薦使用微軟官方驅動 mssql-jdbc(原 sqljdbc4)。本文提供生產級連接工具類,含連接池、增刪改查封裝、資源自動關閉。

1. Maven 依賴

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

2. 連接工具類(單例 + HikariCP 連接池)

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

public final class SqlServerPool {
    private static final String JDBC_URL = "jdbc:sqlserver://127.0.0.1:1433;"
            + "databaseName=YourDB;"
            + "encrypt=true;trustServerCertificate=true;"  // SSL 加密連接
            + "loginTimeout=30;socketTimeout=30;";
    private static final String USER = "sa";
    private static final String PASS = "YourStrongPassword";

    private static final HikariDataSource DS;

    static {
        HikariConfig cfg = new HikariConfig();
        cfg.setJdbcUrl(JDBC_URL);
        cfg.setUsername(USER);
        cfg.setPassword(PASS);
        cfg.setMaximumPoolSize(20);
        cfg.setMinimumIdle(5);
        cfg.setConnectionTimeout(30_000);
        cfg.setIdleTimeout(600_000);
        cfg.setMaxLifetime(1_800_000);
        cfg.addDataSourceProperty("cachePrepStmts", "true");
        cfg.addDataSourceProperty("prepStmtCacheSize", "250");
        cfg.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        DS = new HikariDataSource(cfg);
    }

    private SqlServerPool() {}

    public static DataSource getDataSource() { return DS; }

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

    // 關閉池(應用關閉時調用)
    public static void close() { DS.close(); }
}

3. 通用增刪改查封裝

public class JdbcTemplate {
    private final DataSource ds;

    public JdbcTemplate(DataSource ds) { this.ds = ds; }

    // 更新操作(INSERT/UPDATE/DELETE)
    public int update(String sql, Object... params) {
        try (Connection conn = ds.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            bindParams(ps, params);
            return ps.executeUpdate();
        } catch (SQLException e) { throw new RuntimeException(e); }
    }

    // 查詢單行映射到對象
    public <T> T queryForObject(String sql, RowMapper<T> mapper, Object... params) {
        try (Connection conn = ds.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            bindParams(ps, params);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) return mapper.mapRow(rs);
                return null;
            }
        } catch (SQLException e) { throw new RuntimeException(e); }
    }

    // 查詢列表
    public <T> List<T> query(String sql, RowMapper<T> mapper, Object... params) {
        List<T> list = new ArrayList<>();
        try (Connection conn = ds.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            bindParams(ps, params);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) list.add(mapper.mapRow(rs));
            }
        } catch (SQLException e) { throw new RuntimeException(e); }
        return list;
    }

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

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

4. 業務代碼調用示例

public class UserDao {
    private final JdbcTemplate jdbc = new JdbcTemplate(SqlServerPool.getDataSource());

    public User findById(int id) {
        return jdbc.queryForObject(
            "SELECT id, username, email FROM users WHERE id = ?",
            rs -> new User(rs.getInt("id"), rs.getString("username"), rs.getString("email")),
            id
        );
    }

    public List<User> findByDept(String dept) {
        return jdbc.query(
            "SELECT id, username, email FROM users WHERE dept = ?",
            rs -> new User(rs.getInt("id"), rs.getString("username"), rs.getString("email")),
            dept
        );
    }

    public int insert(User u) {
        return jdbc.update(
            "INSERT INTO users (username, email, dept) VALUES (?, ?, ?)",
            u.getUsername(), u.getEmail(), u.getDept()
        );
    }
}

5. 關鍵最佳實踐

避坑指南

  • 驅動類名:舊版 com.microsoft.sqlserver.jdbc.SQLServerDriver,新版 無需 Class.forName(JDBC 4.0+ SPI 自動發現)。
  • 實例名連接:jdbc:sqlserver://host\\instance;databaseName=DB(雙反斜杠轉義)。
  • 整合認證:Windows 域環境用 integratedSecurity=true;authenticationScheme=NTLM 需放置 mssql-jdbc_auth-.dll 到 java.library.path。
  • 大數據類型:varchar(max)/varbinary(max) 用 getBytes()/getCharacterStream() 讀取。

採用連接池 + PreparedStatement + 函數式 RowMapper 模式,即可編寫出高性能、安全、可維護的 SQL Server 數據訪問層。

copyright © 萬盛學電腦網 all rights reserved
實踐理由
使用連接池 (HikariCP)避免頻繁創建/銷毀連接,複用連接,顯著提升吞吐
PreparedStatement + 參數綁定防 SQL 注入、執行計劃復用、類型安全
try-with-resources 自動關閉保證 Connection/Statement/ResultSet 及時釋放,防洩漏
SSL 加密連接encrypt=true;trustServerCertificate=true 保護傳輸安全
設置 socketTimeout/loginTimeout防止網絡故障導致線程無限阻塞
批量操作用 addBatch/executeBatch減少網絡往返,大幅提升寫入性能