Java 通過 JDBC 連接 SQL Server,推薦使用微軟官方驅動 mssql-jdbc(原 sqljdbc4)。本文提供生產級連接工具類,含連接池、增刪改查封裝、資源自動關閉。
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>12.4.2.jre11</version> <!-- 根據 JDK 版本選擇 -->
</dependency>
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(); }
}
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; }
}
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()
);
}
}
| 實踐 | 理由 |
|---|---|
| 使用連接池 (HikariCP) | 避免頻繁創建/銷毀連接,複用連接,顯著提升吞吐 |
| PreparedStatement + 參數綁定 | 防 SQL 注入、執行計劃復用、類型安全 |
| try-with-resources 自動關閉 | 保證 Connection/Statement/ResultSet 及時釋放,防洩漏 |
| SSL 加密連接 | encrypt=true;trustServerCertificate=true 保護傳輸安全 |
| 設置 socketTimeout/loginTimeout | 防止網絡故障導致線程無限阻塞 |
| 批量操作用 addBatch/executeBatch | 減少網絡往返,大幅提升寫入性能 |