Java 連接 SQL Server 2008/2012/2016/2019 完整指南：JDBC 驅動、連線池、常見錯誤排查

Microsoft JDBC Driver 版本對應表

Maven 依賴配置 (推薦)

    com.microsoft.sqlserver
    mssql-jdbc
    12.4.2.jre11 




    com.microsoft.sqlserver
    mssql-jdbc
    11.2.3.jre8

SQL Server 端必要配置

1. 啟用 TCP/IP 協議：
   • SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for MSSQLSERVER
   • 右鍵 TCP/IP → Enable
   • IP Addresses 頁籤 → IPAll → TCP Port 設為 1433
2. 啟用 SQL Server Authentication：
   • SSMS 連線 → 伺服器屬性 → Security → SQL Server and Windows Authentication mode
   • 重啟 SQL Server 服務生效
3. 防火牆開放 1433 埠 (Windows 防火牆/雲安全組)
4. 建立資料庫與登入帳號：

   
   CREATE DATABASE MyDB;
   CREATE LOGIN app_user WITH PASSWORD = 'StrongP@ssw0rd';
   USE MyDB;
   CREATE USER app_user FOR LOGIN app_user;
   ALTER ROLE db_owner ADD MEMBER app_user;
           

連線字串完整參數詳解

# 基礎連線 (不驗證憑證，開發適用)
jdbc:sqlserver://localhost:1433;databaseName=MyDB;encrypt=true;trustServerCertificate=true

# 生產環境 (驗證憑證)
jdbc:sqlserver://prod-db:1433;databaseName=MyDB;encrypt=true;trustServerCertificate=false;hostNameInCertificate=prod-db.example.com

# 完整參數範例
jdbc:sqlserver://host:1433
    ;databaseName=MyDB
    ;user=app_user
    ;password=StrongP@ssw0rd
    ;encrypt=true
    ;trustServerCertificate=true
    ;loginTimeout=30
    ;socketTimeout=30
    ;applicationName=MyApp
    ;sendStringParametersAsUnicode=true
    ;selectMethod=cursor

Spring Boot 整合配置

application.yml

spring:
  datasource:
    url: jdbc:sqlserver://localhost:1433;databaseName=MyDB;encrypt=true;trustServerCertificate=true
    username: app_user
    password: StrongP@ssw0rd
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
      connection-test-query: SELECT 1
      initialization-fail-timeout: 0

  jpa:
    database-platform: org.hibernate.dialect.SQLServerDialect
    hibernate:
      ddl-auto: validate
    show-sql: false
    properties:
      hibernate:
        format_sql: true
        jdbc:
          batch_size: 50
          fetch_size: 100

多資料源配置範例

@Configuration
@MapperScan(basePackages = "com.app.mapper.db1", sqlSessionFactoryRef = "db1SqlSessionFactory")
public class Db1Config {
    @Bean @ConfigurationProperties("spring.datasource.db1")
    DataSource db1DataSource() { return DataSourceBuilder.create().build(); }

    @Bean SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource ds) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(ds);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/db1/*.xml"));
        return bean.getObject();
    }

    @Bean DataSourceTransactionManager db1TxManager(@Qualifier("db1DataSource") DataSource ds) {
        return new DataSourceTransactionManager(ds);
    }
}

原生 JDBC 連線測試代碼

import java.sql.*;

public class SqlServerConnectionTest {
    public static void main(String[] args) {
        String url = "jdbc:sqlserver://localhost:1433;databaseName=MyDB;encrypt=true;trustServerCertificate=true";
        String user = "app_user";
        String password = "StrongP@ssw0rd";

        // 1. 載入驅動 (JDBC 4.0+ 可選，SPI 自動載入)
        // Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            System.out.println("✅ 連線成功！");
            System.out.println("資料庫版本: " + conn.getMetaData().getDatabaseProductVersion());
            System.out.println("驅動版本: " + conn.getMetaData().getDriverVersion());
            System.out.println("隔離級別: " + conn.getTransactionIsolation());

            // 簡單查詢測試
            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery("SELECT @@VERSION AS version")) {
                if (rs.next()) {
                    System.out.println("版本詳情: " + rs.getString("version"));
                }
            }
        } catch (SQLException e) {
            System.err.println("❌ 連線失敗: " + e.getMessage());
            e.printStackTrace();
            // 常見錯誤碼分析
            analyzeError(e);
        }
    }

    static void analyzeError(SQLException e) {
        System.err.println("SQLState: " + e.getSQLState());
        System.err.println("ErrorCode: " + e.getErrorCode());
        switch (e.getErrorCode()) {
            case 18456 -> System.err.println("🔑 登入失敗：帳號/密碼錯誤、帳號鎖定、或非 SQL 認證模式");
            case 4060 -> System.err.println("🗄️ 資料庫不存在：databaseName 拼錯或未建立");
            case 17182 -> System.err.println("🔌 連線超時：主機/埠口錯誤、防火牆阻擋、SQL Server 服務未啟動、TCP/IP 未啟用");
            case 0, -1 -> System.err.println("🌐 網路不可達：主機名解析失敗、VPN/代理問題");
            default -> System.err.println("📋 請查閱 Microsoft 官方錯誤碼文檔");
        }
    }
}

常見問題排查清單

效能優化建議

1. 使用預編譯語句：PreparedStatement 防注入、計畫快取重用
2. 批次操作：addBatch() + executeBatch()，配合 hibernate.jdbc.batch_size
3. 適當索引：避免表掃描，使用 INCLUDE 欄覆蓋索引
4. 分頁查詢：SQL Server 2012+ OFFSET ... FETCH，舊版用 ROW_NUMBER()
5. 資料類型匹配：Java String 對應 NVARCHAR，BigDecimal 對應 DECIMAL，避免隱式轉換