# 外部库 JDBC 封装指南
适用场景：接收
HikariDataSource（或javax.sql.DataSource），硬编码拼接 SQL 做增删改查。
目标：消除重复的 try-catch / 资源关闭样板，不引入动态数据源、不抽象 SQL 构建。
源码位置：
| 类 | 路径 |
|---|---|
JdbcExecutionUtil |
src/main/java/com/liang/learn/util/JdbcExecutionUtil.java |
ExternalHikariPoolHolder |
src/main/java/com/liang/learn/service/jdbc/ExternalHikariPoolHolder.java |
1. 设计原则
抽什么 / 不抽什么
| 抽 | 不抽 |
|---|---|
Connection / PreparedStatement / ResultSet 生命周期 |
SQL 字符串（仍在业务方法里拼接） |
catch → 可选 scene log → BizException.wrap |
通用 CRUD 基类、反射实体映射 |
| 可选的外部库 Hikari 池缓存 | @DS / AbstractRoutingDataSource |
与项目异常规范对齐
- 中间层：只
throw BizException.wrap(e)，不打堆栈。 - 场景 log：一行
log.warn，不带堆栈；scene传null时跳过（复合编排内层使用）。 - 明确业务校验：
new BizException(ErrorCode, msg)无 cause；JdbcExecutionUtil会原样透传，不重复打 scene log。 - 详细堆栈：只在
GlobalExceptionHandler/BizExceptionLogUtil.logFailure打一次。
架构
flowchart TB
subgraph biz [业务层 XxxJdbcDao]
SQL["硬编码 SQL + 参数绑定"]
end
subgraph util [JdbcExecutionUtil]
RES["资源关闭"]
EX["异常统一 wrap"]
end
subgraph pool [ExternalHikariPoolHolder]
HIK["按 dataSourceId 缓存 HikariDataSource"]
end
biz --> util
biz --> pool
pool --> DS["HikariDataSource"]
2. 审查修复清单
| 问题 | 修复 |
|---|---|
Function<ResultSet,T> 无法传播 SQLException |
新增 RowMapper<T>，声明 throws SQLException |
query(DataSource) 嵌套双层 scene / 双 wrap |
抽取 query0/update0/execute0，DataSource 入口只包一层 try-catch |
| 复合任务内层 + 外层重复 scene log | scene 允许 null；内层传 null，外层统一打 log |
无返回值场景 return null 可读性差 |
新增 run() / runOnConnection() |
| 缺少 DataSource overload | 补 queryList / queryForLong / execute 的 DataSource 重载 |
| 池配置更新后仍用旧池 | 文档约定 + evict(id)；删除数据源时调用 |
| 应用关闭连接池泄漏 | @PreDestroy destroy() 关闭全部池 |
| disabled 数据源仍可建池 | assertActive() 校验 status |
| 无用 import、参数 NPE | 去掉 Hikari import；Objects.requireNonNull 关键参数 |
3. JdbcExecutionUtil 完整实现
package com.liang.learn.util;
import com.liang.learn.exception.BizException;
import lombok.extern.slf4j.Slf4j;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
/**
* JDBC 执行模板：统一资源关闭与 {@link BizException#wrap(Throwable)}。
* SQL 仍由调用方硬编码拼接；本类只消除 try-catch 与 Statement/ResultSet 关闭样板。
*/
@Slf4j
public final class JdbcExecutionUtil {
private JdbcExecutionUtil() {
}
@FunctionalInterface
public interface StatementBinder {
void bind(PreparedStatement ps) throws SQLException;
}
@FunctionalInterface
public interface ResultExtractor<T> {
T extract(ResultSet rs) throws SQLException;
}
@FunctionalInterface
public interface RowMapper<T> {
T mapRow(ResultSet rs) throws SQLException;
}
@FunctionalInterface
public interface ConnectionAction<T> {
T execute(Connection connection) throws SQLException;
}
@FunctionalInterface
public interface ConnectionVoidAction {
void execute(Connection connection) throws SQLException;
}
// ---------- 连接入口 ----------
public static <T> T withConnection(DataSource dataSource, String scene, ConnectionAction<T> action) {
Objects.requireNonNull(dataSource, "dataSource");
Objects.requireNonNull(action, "action");
try (Connection connection = dataSource.getConnection()) {
return action.execute(connection);
} catch (Exception e) {
return rethrow(scene, e);
}
}
public static void run(DataSource dataSource, String scene, ConnectionVoidAction action) {
withConnection(dataSource, scene, conn -> {
action.execute(conn);
return null;
});
}
public static <T> T onConnection(Connection connection, String scene, ConnectionAction<T> action) {
Objects.requireNonNull(connection, "connection");
Objects.requireNonNull(action, "action");
try {
return action.execute(connection);
} catch (Exception e) {
return rethrow(scene, e);
}
}
public static void runOnConnection(Connection connection, String scene, ConnectionVoidAction action) {
onConnection(connection, scene, conn -> {
action.execute(conn);
return null;
});
}
// ---------- 查询 ----------
public static <T> T query(Connection connection, String sql, String scene,
StatementBinder binder, ResultExtractor<T> extractor) {
return onConnection(connection, scene, conn -> query0(conn, sql, binder, extractor));
}
public static <T> T query(DataSource dataSource, String sql, String scene,
StatementBinder binder, ResultExtractor<T> extractor) {
Objects.requireNonNull(dataSource, "dataSource");
try (Connection connection = dataSource.getConnection()) {
return query0(connection, sql, binder, extractor);
} catch (Exception e) {
return rethrow(scene, e);
}
}
public static <T> List<T> queryList(Connection connection, String sql, String scene,
StatementBinder binder, RowMapper<T> rowMapper) {
Objects.requireNonNull(rowMapper, "rowMapper");
return query(connection, sql, scene, binder, rs -> {
List<T> rows = new ArrayList<>();
while (rs.next()) {
rows.add(rowMapper.mapRow(rs));
}
return rows;
});
}
public static <T> List<T> queryList(DataSource dataSource, String sql, String scene,
StatementBinder binder, RowMapper<T> rowMapper) {
return withConnection(dataSource, scene,
conn -> queryList(conn, sql, null, binder, rowMapper));
}
public static long queryForLong(Connection connection, String sql, String scene, StatementBinder binder) {
return query(connection, sql, scene, binder, rs -> rs.next() ? rs.getLong(1) : 0L);
}
public static long queryForLong(DataSource dataSource, String sql, String scene, StatementBinder binder) {
return query(dataSource, sql, scene, binder, rs -> rs.next() ? rs.getLong(1) : 0L);
}
// ---------- 增删改 / execute ----------
public static int update(Connection connection, String sql, String scene, StatementBinder binder) {
return onConnection(connection, scene, conn -> update0(conn, sql, binder));
}
public static int update(DataSource dataSource, String sql, String scene, StatementBinder binder) {
return withConnection(dataSource, scene, conn -> update0(conn, sql, binder));
}
public static boolean execute(Connection connection, String sql, String scene, StatementBinder binder) {
return onConnection(connection, scene, conn -> execute0(conn, sql, binder));
}
public static boolean execute(DataSource dataSource, String sql, String scene, StatementBinder binder) {
return withConnection(dataSource, scene, conn -> execute0(conn, sql, binder));
}
// ---------- 无 try-catch 核心 ----------
private static <T> T query0(Connection connection, String sql,
StatementBinder binder, ResultExtractor<T> extractor) throws SQLException {
Objects.requireNonNull(sql, "sql");
Objects.requireNonNull(extractor, "extractor");
try (PreparedStatement ps = connection.prepareStatement(sql)) {
if (binder != null) {
binder.bind(ps);
}
try (ResultSet rs = ps.executeQuery()) {
return extractor.extract(rs);
}
}
}
private static int update0(Connection connection, String sql, StatementBinder binder) throws SQLException {
Objects.requireNonNull(sql, "sql");
try (PreparedStatement ps = connection.prepareStatement(sql)) {
if (binder != null) {
binder.bind(ps);
}
return ps.executeUpdate();
}
}
private static boolean execute0(Connection connection, String sql, StatementBinder binder) throws SQLException {
Objects.requireNonNull(sql, "sql");
try (PreparedStatement ps = connection.prepareStatement(sql)) {
if (binder != null) {
binder.bind(ps);
}
return ps.execute();
}
}
private static <T> T rethrow(String scene, Exception e) {
if (scene != null && !(e instanceof BizException)) {
log.warn(scene);
}
throw BizException.wrap(e);
}
}
API 速查
| 方法 | 连接来源 | 是否关闭连接 | 典型用途 |
|---|---|---|---|
withConnection |
DataSource | 自动关闭 | 单次任务 |
run |
DataSource | 自动关闭 | 无返回值单次任务 |
onConnection |
外部 Connection | 不关闭 | 长连接多句 SQL |
runOnConnection |
外部 Connection | 不关闭 | 无返回值长连接 |
query / queryList / queryForLong |
两者均有重载 | 见上 | SELECT |
update |
两者均有重载 | 见上 | INSERT/UPDATE/DELETE |
execute |
两者均有重载 | 见上 | DDL 等 |
scene 约定
- 最外层业务方法：传可读场景，如
"订单行数统计失败"。 - 被编排方法内层调用：传
null，避免与外层重复 warn。 - 明确业务校验在 util 之外抛出
BizException，util 不会重复 log。
4. ExternalHikariPoolHolder 完整实现
package com.liang.learn.service.jdbc;
import com.liang.learn.exception.BizException;
import com.liang.learn.model.entity.DataSourceDO;
import com.liang.learn.model.enums.DataSourceStatusEnum;
import com.liang.learn.model.enums.DataSourceTypeEnum;
import com.liang.learn.model.enums.ErrorCode;
import com.liang.learn.service.DataSourceService;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import jakarta.annotation.PreDestroy;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import java.util.Map;
import java.util.Objects;
import java.util.concurrent.ConcurrentHashMap;
@Slf4j
@Component
@RequiredArgsConstructor
public class ExternalHikariPoolHolder {
private static final int DEFAULT_MAX_POOL_SIZE = 5;
private static final int DEFAULT_MIN_IDLE = 1;
private static final long CONNECTION_TIMEOUT_MS = 10_000L;
private final DataSourceService dataSourceService;
private final Map<Long, HikariDataSource> pools = new ConcurrentHashMap<>();
public HikariDataSource getPool(DataSourceDO dataSource) {
Objects.requireNonNull(dataSource, "dataSource");
Objects.requireNonNull(dataSource.getId(), "dataSource.id");
assertActive(dataSource);
return pools.computeIfAbsent(dataSource.getId(), id -> createPool(dataSource));
}
public void evict(Long dataSourceId) {
if (dataSourceId == null) {
return;
}
HikariDataSource removed = pools.remove(dataSourceId);
if (removed != null) {
log.info("外部库连接池已驱逐, dataSourceId={}", dataSourceId);
removed.close();
}
}
@PreDestroy
public void destroy() {
pools.forEach((id, pool) -> {
log.info("关闭外部库连接池, dataSourceId={}", id);
pool.close();
});
pools.clear();
}
private void assertActive(DataSourceDO dataSource) {
String status = dataSource.getStatus();
if (status == null || DataSourceStatusEnum.ACTIVE.getCode().equalsIgnoreCase(status)) {
return;
}
throw new BizException(ErrorCode.BUSINESS_ERROR, "数据源不可用: " + dataSource.getDataSourceCode());
}
private HikariDataSource createPool(DataSourceDO dataSource) {
DataSourceTypeEnum type = DataSourceTypeEnum.fromCode(dataSource.getDataSourceType());
HikariConfig config = new HikariConfig();
config.setPoolName("ext-" + dataSource.getDataSourceCode());
config.setDriverClassName(type.resolveDriverClassName(dataSource.getDriverClassName()));
config.setJdbcUrl(type.buildJdbcUrl(
dataSource.getHost(), dataSource.getPort(),
dataSource.getDatabaseName(), dataSource.getSchema()));
config.setUsername(dataSource.getUsername());
config.setPassword(dataSourceService.getDecryptedPassword(dataSource.getId()));
config.setMaximumPoolSize(DEFAULT_MAX_POOL_SIZE);
config.setMinimumIdle(DEFAULT_MIN_IDLE);
config.setConnectionTimeout(CONNECTION_TIMEOUT_MS);
return new HikariDataSource(config);
}
}
池生命周期集成点
删除数据源时驱逐池（待接入，示例）：
// DataSourceServiceImpl.delete 成功落库后
poolHolder.evict(id);
若后续增加「更新连接信息」接口，更新成功后同样须 evict(id)，否则会继续使用旧密码/主机。
5. 用法示例
5.1 重构前后：行数统计
重构前（ExternalJdbcExecutor 现有写法）：
public long countRows(Connection connection, String sql, String startTime, String endTime) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setTimestamp(1, Timestamp.valueOf(startTime));
ps.setTimestamp(2, Timestamp.valueOf(endTime));
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? rs.getLong(1) : 0L;
}
} catch (Exception e) {
log.warn("行数统计失败");
throw BizException.wrap(e);
}
}
重构后：
public long countRows(Connection connection, String sql, String startTime, String endTime) {
return JdbcExecutionUtil.queryForLong(connection, sql, "行数统计失败", ps -> {
ps.setTimestamp(1, Timestamp.valueOf(startTime));
ps.setTimestamp(2, Timestamp.valueOf(endTime));
});
}
5.2 业务 Dao：单次查询（DataSource 入口）
@Component
@RequiredArgsConstructor
public class OrderSnapshotJdbcDao {
private final ExternalHikariPoolHolder poolHolder;
/**
* 按时间窗统计订单数。
*/
public long countByTimeRange(DataSourceDO ds, String startTime, String endTime) {
HikariDataSource pool = poolHolder.getPool(ds);
String sql = """
SELECT COUNT(1) FROM "order".t_order
WHERE create_time >= ? AND create_time < ?
""";
return JdbcExecutionUtil.queryForLong(pool, sql, "订单行数统计失败", ps -> {
ps.setTimestamp(1, Timestamp.valueOf(startTime));
ps.setTimestamp(2, Timestamp.valueOf(endTime));
});
}
}
5.3 多行查询 + 硬编码 SQL
public List<Map<String, Object>> fetchByIds(DataSourceDO ds, String schema,
String table, List<String> ids) {
if (ids.isEmpty()) {
return List.of();
}
// schema/table 须在上层校验，防注入
String sql = "SELECT * FROM \"" + schema.toLowerCase() + "\".\"" + table.toLowerCase()
+ "\" WHERE id::text = ANY(?)";
return JdbcExecutionUtil.queryList(poolHolder.getPool(ds), sql, "订单全行查询失败", ps ->
ps.setArray(1, ps.getConnection().createArrayOf("varchar", ids.toArray())), rs -> {
Map<String, Object> row = new LinkedHashMap<>();
int cols = rs.getMetaData().getColumnCount();
for (int i = 1; i <= cols; i++) {
row.put(rs.getMetaData().getColumnLabel(i), rs.getObject(i));
}
return row;
});
}
注意：
setArray需要Connection时，优先在withConnection内绑定，或使用conn.createArrayOf：
return JdbcExecutionUtil.withConnection(poolHolder.getPool(ds), "订单全行查询失败", conn ->
JdbcExecutionUtil.queryList(conn, sql, null, ps ->
ps.setArray(1, conn.createArrayOf("varchar", ids.toArray())), rowMapper));
5.4 增删改
public int softDelete(DataSourceDO ds, Long id, String operator) {
String sql = """
UPDATE "manage".t_config
SET deleted = 1, update_user = ?, update_time = CURRENT_TIMESTAMP
WHERE id = ? AND deleted = 0
""";
return JdbcExecutionUtil.update(
poolHolder.getPool(ds), sql, "配置软删除失败", ps -> {
ps.setString(1, operator);
ps.setLong(2, id);
});
}
5.5 业务校验 + JDBC 异常分离
对齐 TableMetadataLoader 模式：
public List<ColumnMeta> loadColumns(Connection conn, String schema, String table) {
String sql = """
SELECT column_name, ordinal_position, udt_name
FROM information_schema.columns
WHERE table_schema = ? AND table_name = ?
ORDER BY ordinal_position
""";
List<ColumnMeta> columns = JdbcExecutionUtil.queryList(
conn, sql, "读取表结构失败, schema=" + schema + ", table=" + table,
ps -> {
ps.setString(1, schema);
ps.setString(2, table.toLowerCase());
},
rs -> new ColumnMeta(
rs.getString("column_name"),
rs.getInt("ordinal_position"),
rs.getString("udt_name")));
if (columns.isEmpty()) {
log.warn("读取表结构失败：表不存在或无列, schema={}, table={}", schema, table);
throw new BizException(ErrorCode.NOT_FOUND, "表不存在或无列: " + table);
}
return columns;
}
5.6 长连接复合任务（避免重复 scene log）
public void compareOnce(DataSourceDO pgDs, DataSourceDO ogDs, String table) {
HikariDataSource pgPool = poolHolder.getPool(pgDs);
HikariDataSource ogPool = poolHolder.getPool(ogDs);
try (Connection pgConn = pgPool.getConnection();
Connection ogConn = ogPool.getConnection()) {
long pgCount = countOn(pgConn, table); // 内层 scene=null
long ogCount = countOn(ogConn, table);
// ... 后续多条 SQL 复用 pgConn / ogConn
} catch (Exception e) {
if (!(e instanceof BizException)) {
log.warn("[COMPARE] 比对失败, table={}", table);
}
throw BizException.wrap(e);
}
}
/** 内层被编排方法：scene 传 null，由 compareOnce 统一打 log */
private long countOn(Connection conn, String table) {
String sql = "SELECT COUNT(1) FROM \"" + table + "\"";
return JdbcExecutionUtil.queryForLong(conn, sql, null, null);
}
5.7 小事务（不额外抽象 TransactionTemplate）
public void transfer(DataSourceDO ds, long fromId, long toId, BigDecimal amount) {
JdbcExecutionUtil.run(poolHolder.getPool(ds), "转账失败", conn -> {
boolean oldAuto = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
int debited = JdbcExecutionUtil.update(conn,
"UPDATE account SET balance = balance - ? WHERE id = ? AND deleted = 0",
null, ps -> {
ps.setBigDecimal(1, amount);
ps.setLong(2, fromId);
});
if (debited != 1) {
throw new BizException(ErrorCode.NOT_FOUND, "转出账户不存在");
}
int credited = JdbcExecutionUtil.update(conn,
"UPDATE account SET balance = balance + ? WHERE id = ? AND deleted = 0",
null, ps -> {
ps.setBigDecimal(1, amount);
ps.setLong(2, toId);
});
if (credited != 1) {
throw new BizException(ErrorCode.NOT_FOUND, "转入账户不存在");
}
conn.commit();
} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException rollbackEx) {
e.addSuppressed(rollbackEx);
}
throw e;
} finally {
conn.setAutoCommit(oldAuto);
}
});
}
6. 目录与职责
com.liang.learn.util
└── JdbcExecutionUtil # 纯模板，无 Spring
com.liang.learn.service.jdbc
└── ExternalHikariPoolHolder # 外部库 Hikari 池（可选）
com.liang.learn.service.xxx
└── XxxJdbcDao # 硬编码 SQL，方法名表达业务语义
- 一个外部库领域一个 Dao/Executor，方法名用业务语义（
countByTimeRange），不用selectOne这类通用名。 - 现有
ExternalJdbcExecutor可逐步迁移；新代码直接使用本封装。
7. 刻意不做
| 不做 | 原因 |
|---|---|
通用 BaseJdbcDao<T> + 反射 |
过度封装，SQL 隐蔽 |
| SQL Builder DSL | 与「硬编码 SQL」前提冲突 |
| Spring 动态数据源路由 | 用户明确要求不使用 |
util 内 log.error(..., e) |
堆栈只在 Handler/异步边界打一次 |
| 在 util 内拼接对外 error message | 违反 BizException 安全文案约定 |
8. 何时仍手写 try-catch
| 场景 | 建议 |
|---|---|
| 连接测试需固定文案 | BizException.wrap(ErrorCode.CONNECTION_FAILED, msg, e)，见 ConnectionTester |
| 异步 Runnable 最外层 | BizExceptionLogUtil.logFailure(e, scene, ...) |
| 打开双连接编排 | 最外层一个 try-catch；内层 SQL 走 util 且 scene=null |
浙公网安备 33010602011771号