Spring 事务：JDBC 异常转换、批处理与生成键
业务代码里看不到 SQLException，批处理失败的语义因驱动而异，生成键什么时候能读——这三个问题都在 spring-jdbc 层。本页固定基线 Framework v6.1.12 / Java 17 API / H2 2.3.232，并在一次性隔离 MySQL 9.7.1（Homebrew，Innovation 发行版）+ Connector/J 9.2.0 实例上实测同一行为的差异——H2 的观察不能外推到生产库，这正是本票把真实数据库拉进实验的原因。事务侧（同步器、事件、编程式、传播矩阵、保存点、挂起、隔离与回滚规则）的权威讲解在同步与传播矩阵页，代理入口与 rollback-only 在事务正文，MyBatis 批处理与会话绑定的主归属在MyBatis 页（细节归 U42）。
异常转换：为什么业务代码看不到 SQLException
JdbcTemplate 的所有数据访问方法声明的是 DataAccessException 家族，把 SQLException 翻译掉的是一条责任链：先问自定义 translator，再问自己的 doTranslate，都不认就交给 fallback translator。JdbcTemplate 默认装配 SQLErrorCodeSQLExceptionTranslator，它的 fallback 是 SQLExceptionSubclassTranslator，再往下是 SQLStateSQLExceptionTranslator——链上每一层都允许返回 null 表示"我不认识，下一个来"。
原码：AbstractFallbackSQLExceptionTranslator.java，v6.1.12，连续 95–120 行，仅去除共同缩进。固定来源。
原码java
public DataAccessException translate(String task, @Nullable String sql, SQLException ex) {
Assert.notNull(ex, "Cannot translate a null SQLException");
SQLExceptionTranslator custom = getCustomTranslator();
if (custom != null) {
DataAccessException dae = custom.translate(task, sql, ex);
if (dae != null) {
// Custom exception match found.
return dae;
}
}
DataAccessException dae = doTranslate(task, sql, ex);
if (dae != null) {
// Specific exception match found.
return dae;
}
// Looking for a fallback...
SQLExceptionTranslator fallback = getFallbackTranslator();
if (fallback != null) {
return fallback.translate(task, sql, ex);
}
return null;
}SQLErrorCodeSQLExceptionTranslator 的错误码表按 DataSource 的产品名取自 SQLErrorCodesFactory（classpath 的 sql-error-codes.xml，用户可覆盖同名 bean）：H2 的 duplicateKeyCodes 是 23001,23505，MySQL 是 1062。子类翻译器走 JDBC 4 的异常类型。
原码：SQLExceptionSubclassTranslator.java，v6.1.12，连续 63–115 行，仅去除共同缩进。固定来源。
原码java
public class SQLExceptionSubclassTranslator extends AbstractFallbackSQLExceptionTranslator {
public SQLExceptionSubclassTranslator() {
setFallbackTranslator(new SQLStateSQLExceptionTranslator());
}
@Override
@Nullable
protected DataAccessException doTranslate(String task, @Nullable String sql, SQLException ex) {
if (ex instanceof SQLTransientException) {
if (ex instanceof SQLTransientConnectionException) {
return new TransientDataAccessResourceException(buildMessage(task, sql, ex), ex);
}
if (ex instanceof SQLTransactionRollbackException) {
if (SQLStateSQLExceptionTranslator.indicatesCannotAcquireLock(ex.getSQLState())) {
return new CannotAcquireLockException(buildMessage(task, sql, ex), ex);
}
return new PessimisticLockingFailureException(buildMessage(task, sql, ex), ex);
}
if (ex instanceof SQLTimeoutException) {
return new QueryTimeoutException(buildMessage(task, sql, ex), ex);
}
}
else if (ex instanceof SQLNonTransientException) {
if (ex instanceof SQLNonTransientConnectionException) {
return new DataAccessResourceFailureException(buildMessage(task, sql, ex), ex);
}
if (ex instanceof SQLDataException) {
return new DataIntegrityViolationException(buildMessage(task, sql, ex), ex);
}
if (ex instanceof SQLIntegrityConstraintViolationException) {
if (SQLStateSQLExceptionTranslator.indicatesDuplicateKey(ex.getSQLState(), ex.getErrorCode())) {
return new DuplicateKeyException(buildMessage(task, sql, ex), ex);
}
return new DataIntegrityViolationException(buildMessage(task, sql, ex), ex);
}
if (ex instanceof SQLInvalidAuthorizationSpecException) {
return new PermissionDeniedDataAccessException(buildMessage(task, sql, ex), ex);
}
if (ex instanceof SQLSyntaxErrorException) {
return new BadSqlGrammarException(task, (sql != null ? sql : ""), ex);
}
if (ex instanceof SQLFeatureNotSupportedException) {
return new InvalidDataAccessApiUsageException(buildMessage(task, sql, ex), ex);
}
}
else if (ex instanceof SQLRecoverableException) {
return new RecoverableDataAccessException(buildMessage(task, sql, ex), ex);
}
// Fallback to Spring's own SQL state translation...
return null;
}实测把两条真实路径拆开了（translate-h2 与 translate-mysql 模式，对同一条重复主键 insert）：H2 2.3.232 抛 org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException（SQLState 23505、vendor code 23505），错误码表和子类翻译器都能命中 DuplicateKeyException；MySQL Connector/J 9.2.0 抛 java.sql.SQLException 的标准子类 java.sql.SQLIntegrityConstraintViolationException（SQLState 23000、vendor code 1062），错误码表按 1062、子类翻译器按 SQLIntegrityConstraintViolationException + indicatesDuplicateKey("23000", 1062)——两条不同的链路收敛到同一个 DuplicateKeyException。未识别语法错误走 badSqlGrammarCodes：H2 42000 系、MySQL 1146 都映射 BadSqlGrammarException（实测 select * from missing_table_u18）。这段的价值不在"能拿到 DuplicateKeyException"，而在：异常类型是 Spring 契约，触发它的 vendor code/SQLState 是数据库方言——换库不换业务代码的前提是这张翻译表覆盖了你的错误，覆盖不到就落到 UncategorizedSQLException。
连接与事务感知：复用主归属的边界
DataSourceUtils 如何按线程 holder 复用连接、doBegin 何时切 autoCommit、清理时如何恢复——完整机制与三处 v6.1.12 摘录的主归属在事务正文的"JDBC 连接怎样开始、暂停与恢复"，本页不重写。这里只补三个适用面边界：TransactionAwareDataSourceProxy 是"裸 DataSource 想参与事务"的显式适配（getConnection 返回感知同步的代理，close 即归还），新代码应优先走 JdbcTemplate/DataSourceUtils；LazyConnectionDataSourceProxy 把取连接推迟到第一条语句，才让 readOnly/隔离设置真正落到物理连接；SimpleDriverDataSource/SingleConnectionDataSource 是测试替身，不是池。判断"我的代码拿到的连接是不是当前事务的"用 DataSourceUtils.isConnectionTransactional(con, dataSource)，不看 URL。
批处理：batchUpdate 的两种路径与失败语义
JdbcTemplate.batchUpdate 内部先问 JdbcUtils.supportsBatchUpdates(connection)：支持就走 addBatch + executeBatch（Spring 层仍包成回调，见下），不支持退化为逐条 executeUpdate——批语义因此依赖驱动能力。带 KeyHolder 的批量插入也在这条回调里，executeBatch 之后再读生成键。
原码：JdbcTemplate.java，v6.1.12，连续 1591–1633 行，仅去除共同缩进。固定来源。
原码java
private PreparedStatementCallback<int[]> getPreparedStatementCallback(BatchPreparedStatementSetter pss,
@Nullable KeyHolder generatedKeyHolder) {
return ps -> {
try {
int batchSize = pss.getBatchSize();
InterruptibleBatchPreparedStatementSetter ipss =
(pss instanceof InterruptibleBatchPreparedStatementSetter ibpss ? ibpss : null);
if (generatedKeyHolder != null) {
generatedKeyHolder.getKeyList().clear();
}
if (JdbcUtils.supportsBatchUpdates(ps.getConnection())) {
for (int i = 0; i < batchSize; i++) {
pss.setValues(ps, i);
if (ipss != null && ipss.isBatchExhausted(i)) {
break;
}
ps.addBatch();
}
int[] results = ps.executeBatch();
if (generatedKeyHolder != null) {
storeGeneratedKeys(generatedKeyHolder, ps, batchSize);
}
return results;
}
else {
List<Integer> rowsAffected = new ArrayList<>();
for (int i = 0; i < batchSize; i++) {
pss.setValues(ps, i);
if (ipss != null && ipss.isBatchExhausted(i)) {
break;
}
rowsAffected.add(ps.executeUpdate());
if (generatedKeyHolder != null) {
storeGeneratedKeys(generatedKeyHolder, ps, 1);
}
}
int[] rowsAffectedArray = new int[rowsAffected.size()];
for (int i = 0; i < rowsAffectedArray.length; i++) {
rowsAffectedArray[i] = rowsAffected.get(i);
}
return rowsAffectedArray;
}
}失败语义拆成三层说。第一层 Spring：executeBatch 抛出的 SQLException 会被异常翻译链包成 DataAccessException，cause 通常是 BatchUpdateException。第二层驱动——这是 H2 与 MySQL 的第一处分歧（实测同一条 4 项批、第 3 项重复主键）：H2 2.3.232 抛 BatchUpdateException 且 updateCounts=[1, 1, -3, 1]（Statement.EXECUTE_FAILED 占位，失败后仍执行了第 4 条）；MySQL Connector/J 9.2.0 默认 URL 抛裸 SQLIntegrityConstraintViolationException（没有 updateCounts）但批内后续语句同样执行了——异常形态、是否带计数是驱动行为，不是 Spring 契约。第三层 URL：MySQL rewriteBatchedStatements=true 把整批改写成一条多值 INSERT，第 3 行重复使整条语句失败，批内一行都没落（实测 rows=[9]，只剩 catch 后写入的第 9 行）。
事务边界决定"部分执行"的最终命运，实测两布局（batch-h2）：catch 放在同一个事务回调内部，没有任何参与型边界被打上 rollback-only，提交后已执行部分连同 catch 后的写入一起持久（H2 rows=[1,2,3,9]）；同一批失败放进参与型内层事务（嵌套 REQUIRED 模板），参与者失败把共享事务标记 rollback-only，外层 catch 后提交时抛 UnexpectedRollbackException，全部回滚。把 catch 写在 TransactionTemplate 外面等于第二个布局——回调抛错时模板已经回滚，catch 只是事后记录（开发期实测 rows=[9] 的教训）。批量大小还受参数数量、max_allowed_packet、锁持有时间约束，MyBatis BATCH 的同型边界在MyBatis 页有独立实验。
生成键：KeyHolder 的读取时点与失败形态
自增主键要么再发一条查询（有竞态），要么在执行 INSERT 的同一个 PreparedStatement 上请求 RETURN_GENERATED_KEYS 并立即读取。Spring 侧的封装是 KeyHolder/GeneratedKeyHolder：JdbcTemplate.update(psc, keyHolder) 在 executeUpdate 之后 storeGeneratedKeys，键值以 Map 列表存放。
原码：GeneratedKeyHolder.java，v6.1.12，连续 64–94 行，仅去除共同缩进。固定来源。
原码java
public Number getKey() throws InvalidDataAccessApiUsageException, DataRetrievalFailureException {
return getKeyAs(Number.class);
}
@Override
@Nullable
public <T> T getKeyAs(Class<T> keyType) throws InvalidDataAccessApiUsageException, DataRetrievalFailureException {
if (this.keyList.isEmpty()) {
return null;
}
if (this.keyList.size() > 1 || this.keyList.get(0).size() > 1) {
throw new InvalidDataAccessApiUsageException(
"The getKey method should only be used when a single key is returned. " +
"The current key entry contains multiple keys: " + this.keyList);
}
Iterator<Object> keyIter = this.keyList.get(0).values().iterator();
if (keyIter.hasNext()) {
Object key = keyIter.next();
if (key == null || !(keyType.isAssignableFrom(key.getClass()))) {
throw new DataRetrievalFailureException(
"The generated key type is not supported. " +
"Unable to cast [" + (key != null ? key.getClass().getName() : null) +
"] to [" + keyType.getName() + "].");
}
return keyType.cast(key);
}
else {
throw new DataRetrievalFailureException("Unable to retrieve the generated key. " +
"Check that the table has an identity column enabled.");
}
}getKey 的三种失败形态都在这段：键列表空（表没有 identity 列，驱动也没报错）→ 返回 null——静默，最容易漏检；多行键或多列键 → InvalidDataAccessApiUsageException（响亮）；键类型不能转成 Number → DataRetrievalFailureException（响亮）。实测（keys-h2 模式）：identity 表单条插入 getKey()=1；批量 3 条 batchUpdate 后 keyList 恰有 3 个键，再调 getKey() 抛 InvalidDataAccessApiUsageException；无 identity 表插入返回空列表、getKey() 为 null。MySQL（keys-mysql 模式，AUTO_INCREMENT 列）单条同样 getKey()=1，批量 3 条在 rewrite 两档下都取回 3 个键（Connector/J 9.2.0 观察值）——批量生成键的可用性因驱动和 URL 而异，跨驱动契约不能从这两个组合外推。读取时点的边界：键只在物理 INSERT 执行后可读，事务回滚会连同键一起作废；IDENTITY 依赖数据库自增列，序列型数据库（Oracle/PG 序列）要用 sequence 机制，Spring 的抽象不替你跨过这个差异。
实验：H2 与 MySQL 的可验证差异
从仓库根运行 JAVA_HOME=$(/usr/libexec/java_home -v17) bash labs/spring-senior/run.sh jdbc（H2 四模式）；run.sh jdbc-mysql 需要本机 mysqld 二进制，由 run.sh 在 mktemp -d 唯一数据目录 + 随机高端口上启动一次性隔离实例，跑完 shutdown 并删除数据目录，不触碰本机既有 MySQL。环境见实验说明。
| 输入 | H2 2.3.232 观察 | MySQL 9.7.1 + Connector/J 9.2.0 观察 |
|---|---|---|
| 重复主键异常 | JdbcSQLIntegrityConstraintViolationException(23505) | SQLIntegrityConstraintViolationException(23000/1062) |
| 重复主键经 Spring | 两条翻译链都收敛 DuplicateKeyException | 同左（错误码表与子类两条路） |
| 未知表 1146/42000 | BadSqlGrammarException | BadSqlGrammarException |
| 批 4 项第 3 项重复，同事务 catch | updateCounts=[1,1,EXECUTE_FAILED,1]，越过失败点继续，rows=[1,2,3,9] | 默认 URL 裸约束异常无 counts，批尾仍执行，rows=[1,2,3,9]；rewrite=true 整条失败 rows=[9] |
| 参与型内层批失败 | 外层提交抛 UnexpectedRollbackException，全回滚 | 同左 |
| 单条 KeyHolder | getKey()=1 | getKey()=1 |
| 批量 KeyHolder（3 条） | 3 个键；多键 getKey() 抛异常 | rewrite 两档均 3 个键（观察值） |
| 无 identity 表 KeyHolder | getKey()=null（静默） | 同型（AUTO_INCREMENT 表则正常） |
| readOnly=true + INSERT | 提示不拦，rows=[1] | 驱动侧拒绝："Connection is read-only..." |
| READ_UNCOMMITTED | 请求被接受、原样回显，无脏读可复现 | 会话实际 READ-UNCOMMITTED，实测脏读、写方回滚后消失 |
后三行是"不能用 H2 推断生产库"的直接证据：readOnly 与隔离级别的真实语义差异、批处理失败形态的驱动分歧，都只有真实数据库才能揭示。MySQL 9.7.1 是 Innovation 发行版，以上观察限定该组合；生产 LTS 版本按官方手册推导并标注。
实验代码java
package study.spring;
import java.sql.*;
import java.util.*;
import java.util.Collections;
import com.zaxxer.hikari.*;
import org.springframework.dao.*;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.*;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.UnexpectedRollbackException;
import org.springframework.transaction.support.*;
import org.springframework.jdbc.BadSqlGrammarException;
/**
* U18 lab: JDBC exception translation, batching and generated keys, with the
* H2-vs-MySQL contrast that the PRD requires. H2 modes run standalone; mysql-*
* modes are gated behind U18_MYSQL_URL, which run.sh's jdbc-mysql group provides
* after booting an isolated, throwaway MySQL instance (unique port + data dir).
*/
public class JdbcLab {
static final String MYSQL_URL_ENV = "U18_MYSQL_URL";
public static void main(String[] args) {
String mode = args.length == 0 ? "translate-h2" : args[0];
switch (mode) {
case "translate-h2" -> translateH2();
case "batch-h2" -> batchH2();
case "keys-h2" -> keysH2();
case "readonly-h2" -> readonlyH2();
case "translate-mysql" -> translateMysql();
case "batch-mysql" -> batchMysql(false);
case "batch-mysql-rewrite" -> batchMysql(true);
case "keys-mysql" -> keysMysql();
case "dirtyread-mysql" -> dirtyReadMysql();
default -> throw new IllegalArgumentException(mode);
}
}
static HikariDataSource pool(String url, int maxPool) {
var cfg = new HikariConfig();
cfg.setJdbcUrl(url);
cfg.setMaximumPoolSize(maxPool);
cfg.setConnectionTimeout(3000);
return new HikariDataSource(cfg);
}
static String requireMysqlUrl() {
String url = System.getenv(MYSQL_URL_ENV);
if (url == null || url.isBlank())
throw new IllegalStateException(MYSQL_URL_ENV + " not set: mysql-* modes must run via run.sh jdbc-mysql (isolated instance)");
return url;
}
static void translateH2() {
try (var ds = pool("jdbc:h2:mem:u18trh2;DB_CLOSE_DELAY=-1", 2)) {
var jdbc = new JdbcTemplate(ds);
jdbc.execute("create table item(id int primary key)");
jdbc.update("insert into item values(1)");
// Raw JDBC first: H2 2.x throws plain java.sql.SQLException (no JDBC-4 subclass)
String sqlExClass;
try (Connection c = ds.getConnection(); PreparedStatement ps = c.prepareStatement("insert into item values(1)")) {
ps.executeUpdate();
throw new AssertionError("expected duplicate key failure");
} catch (SQLException raw) {
sqlExClass = raw.getClass().getName();
var errorCodes = new SQLErrorCodeSQLExceptionTranslator(ds);
var subclassOnly = new SQLExceptionSubclassTranslator();
DataAccessException viaErrorCode = errorCodes.translate("lab", "insert into item values(1)", raw);
DataAccessException viaSubclass = subclassOnly.translate("lab", "insert into item values(1)", raw);
if (!(viaErrorCode instanceof DuplicateKeyException))
throw new AssertionError("H2 23505 via error-code table must be DuplicateKeyException, got " + viaErrorCode);
System.out.println("translate-h2 raw=" + sqlExClass
+ " sqlState=" + raw.getSQLState() + " vendorCode=" + raw.getErrorCode()
+ " viaErrorCode=" + viaErrorCode.getClass().getSimpleName()
+ " viaSubclassOnly=" + (viaSubclass == null ? "null" : viaSubclass.getClass().getSimpleName()));
}
// End-to-end through JdbcTemplate
try {
jdbc.update("insert into item values(1)");
throw new AssertionError("expected DuplicateKeyException");
} catch (DuplicateKeyException expected) { }
try {
jdbc.execute("selec * from item");
throw new AssertionError("expected BadSqlGrammarException");
} catch (BadSqlGrammarException expected) { }
System.out.println("PASS jdbc-u18 translate-h2 duplicateKeyException=true badSqlGrammar=true");
}
}
static void batchH2() {
try (var ds = pool("jdbc:h2:mem:u18bh2;DB_CLOSE_DELAY=-1", 2)) {
var jdbc = new JdbcTemplate(ds);
jdbc.execute("create table item(id int primary key)");
var tm = new org.springframework.jdbc.datasource.DataSourceTransactionManager(ds);
var tt = new TransactionTemplate(tm);
// Layout A: same physical tx, batch failure caught INSIDE the same template callback.
// No participant boundary is crossed and nothing marks rollback-only, so Spring commits
// the successfully executed portion together with the post-catch write.
int[][] updateCounts = new int[1][];
String[] batchMessage = new String[1];
tt.executeWithoutResult(s -> {
try {
jdbc.batchUpdate("insert into item values(?)", List.<Object[]>of(new Object[]{1}, new Object[]{2}, new Object[]{1}, new Object[]{3}));
throw new AssertionError("batch failure must surface");
} catch (DataAccessException caught) {
Throwable root = caught; while (root.getCause() != null) root = root.getCause();
if (!(root instanceof BatchUpdateException bue))
throw new AssertionError("root cause must be BatchUpdateException, got " + root);
updateCounts[0] = bue.getUpdateCounts();
batchMessage[0] = root.getMessage();
}
jdbc.update("insert into item values(9)");
});
var rows = jdbc.queryForList("select id from item order by id", Integer.class);
// Teaching point: same-tx catch commits the executed portion — WHICH rows executed past
// the failure is driver behavior (H2 executed the whole batch despite the duplicate),
// not a Spring guarantee. MySQL observation recorded in batch-mysql.
if (!rows.equals(List.of(1, 2, 3, 9)))
throw new AssertionError("layout A: observed H2 behavior changed (rows=" + rows + "), update "
+ Arrays.toString(updateCounts[0]) + " msg=" + batchMessage[0]);
// Layout B: batch failure inside a participating inner template marks the shared tx rollback-only
int[][] innerCounts = new int[1][];
try {
tt.executeWithoutResult(s -> {
var inner = new TransactionTemplate(tm);
try {
inner.executeWithoutResult(i ->
jdbc.batchUpdate("insert into item values(?)", List.<Object[]>of(new Object[]{4}, new Object[]{4}, new Object[]{5})));
throw new AssertionError("inner batch failure must surface");
} catch (DataAccessException caught) {
Throwable root = caught; while (root.getCause() != null) root = root.getCause();
innerCounts[0] = ((BatchUpdateException) root).getUpdateCounts();
}
jdbc.update("insert into item values(6)");
});
throw new AssertionError("expected UnexpectedRollbackException at outer commit");
} catch (UnexpectedRollbackException expected) { }
var rows2 = jdbc.queryForList("select id from item order by id", Integer.class);
if (!rows2.equals(List.of(1, 2, 3, 9)))
throw new AssertionError("layout B: participant failure must roll the whole tx back, rows=" + rows2);
System.out.println("PASS jdbc-u18 batch-h2 partialCommitSameTx=" + rows
+ " updateCounts=" + Arrays.toString(updateCounts[0])
+ " participantRollbackAll=rows[1,2,3,9] innerCounts=" + Arrays.toString(innerCounts[0]));
}
}
static void keysH2() {
try (var ds = pool("jdbc:h2:mem:u18kh2;DB_CLOSE_DELAY=-1", 2)) {
var jdbc = new JdbcTemplate(ds);
jdbc.execute("create table t2(id bigint generated by default as identity primary key, tag varchar(20))");
var holder = new GeneratedKeyHolder();
jdbc.update(con -> {
PreparedStatement ps = con.prepareStatement("insert into t2(tag) values(?)", Statement.RETURN_GENERATED_KEYS);
ps.setString(1, "single");
return ps;
}, holder);
Number key = holder.getKey();
if (key == null || key.longValue() != 1) throw new AssertionError("single generated key, got " + holder.getKeyList());
// batch path: executeBatch first, keys stored afterwards
var batchHolder = new GeneratedKeyHolder();
var psc = new PreparedStatementCreator() {
@Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
return con.prepareStatement("insert into t2(tag) values(?)", Statement.RETURN_GENERATED_KEYS);
}
};
jdbc.batchUpdate(psc, new BatchPreparedStatementSetter() {
@Override public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setString(1, "row-" + i); }
@Override public int getBatchSize() { return 3; }
}, batchHolder);
if (batchHolder.getKeyList().size() != 3)
throw new AssertionError("H2 batch keys expected 3 rows, got " + batchHolder.getKeyList());
// getKey on multiple keys must fail loudly
try {
batchHolder.getKey();
throw new AssertionError("getKey with 3 keys must throw");
} catch (InvalidDataAccessApiUsageException expected) { }
// table without identity: empty key list -> getKey() is null (silent), not an exception
jdbc.execute("create table plain(v int)");
var emptyHolder = new GeneratedKeyHolder();
jdbc.update(con -> con.prepareStatement("insert into plain values(1)", Statement.RETURN_GENERATED_KEYS), emptyHolder);
if (emptyHolder.getKey() != null)
throw new AssertionError("no identity column must yield null key, got " + emptyHolder.getKeyList());
System.out.println("PASS jdbc-u18 keys-h2 singleKey=" + key.longValue()
+ " batchKeys=3 multiKeyGetKey=InvalidDataAccessApiUsageException noIdentity=null");
}
}
static void readonlyH2() {
try (var ds = pool("jdbc:h2:mem:u18roh2;DB_CLOSE_DELAY=-1", 2)) {
var jdbc = new JdbcTemplate(ds);
jdbc.execute("create table item(id int primary key)");
var tm = new org.springframework.jdbc.datasource.DataSourceTransactionManager(ds);
var tt = new TransactionTemplate(tm);
tt.setReadOnly(true);
tt.executeWithoutResult(s -> jdbc.update("insert into item values(1)"));
var rows = jdbc.queryForList("select id from item", Integer.class);
if (!rows.equals(List.of(1)))
throw new AssertionError("H2 treats readOnly as a hint: insert must persist, rows=" + rows);
System.out.println("PASS jdbc-u18 readonly-h2 readOnlyEnforced=false rows=[1]");
}
}
static void translateMysql() {
String url = requireMysqlUrl();
try (var ds = pool(url, 2)) {
var jdbc = new JdbcTemplate(ds);
jdbc.execute("drop table if exists item");
jdbc.execute("create table item(id int primary key)");
jdbc.update("insert into item values(1)");
try (Connection c = ds.getConnection(); PreparedStatement ps = c.prepareStatement("insert into item values(1)")) {
ps.executeUpdate();
throw new AssertionError("expected duplicate key failure");
} catch (SQLException raw) {
var errorCodes = new SQLErrorCodeSQLExceptionTranslator(ds);
var subclassOnly = new SQLExceptionSubclassTranslator();
DataAccessException viaErrorCode = errorCodes.translate("lab", "insert into item values(1)", raw);
DataAccessException viaSubclass = subclassOnly.translate("lab", "insert into item values(1)", raw);
if (!(viaErrorCode instanceof DuplicateKeyException))
throw new AssertionError("MySQL 1062 must map to DuplicateKeyException, got " + viaErrorCode);
if (!(viaSubclass instanceof DuplicateKeyException))
throw new AssertionError("MySQL throws a JDBC-4 subclass: subclass translator must map it too, got " + viaSubclass);
System.out.println("translate-mysql raw=" + raw.getClass().getName()
+ " sqlState=" + raw.getSQLState() + " vendorCode=" + raw.getErrorCode()
+ " viaErrorCode=" + viaErrorCode.getClass().getSimpleName()
+ " viaSubclassOnly=" + viaSubclass.getClass().getSimpleName());
}
try {
jdbc.update("insert into item values(1)");
throw new AssertionError("expected DuplicateKeyException");
} catch (DuplicateKeyException expected) { }
try {
jdbc.execute("select * from missing_table_u18");
throw new AssertionError("expected BadSqlGrammarException");
} catch (BadSqlGrammarException expected) { }
System.out.println("PASS jdbc-u18 translate-mysql duplicateKeyException=true badSqlGrammar1146=true");
}
}
static String truncateMsg(String s) {
if (s == null) return "null";
int nl = s.indexOf('\n');
String line = nl > 0 ? s.substring(0, nl) : s;
return line.length() <= 160 ? line : line.substring(0, 160) + "...";
}
static void batchMysql(boolean rewrite) {
String url = requireMysqlUrl() + (rewrite ? "&rewriteBatchedStatements=true" : "");
try (var ds = pool(url, 2)) {
var jdbc = new JdbcTemplate(ds);
jdbc.execute("drop table if exists item");
jdbc.execute("create table item(id int primary key)");
var tm = new org.springframework.jdbc.datasource.DataSourceTransactionManager(ds);
var tt = new TransactionTemplate(tm);
// Same physical tx, failure caught INSIDE the same template callback (mirror of batch-h2
// layout A): no participant boundary is crossed, so Spring commits whatever the driver
// actually executed plus the post-catch write. Connector/J 9.2.0 surfaces the raw
// constraint violation (no BatchUpdateException, no per-statement counts) yet executes
// the batch past the failure; rewriteBatchedStatements=true merges the batch into one
// multi-value statement whose failure leaves nothing from the batch. Exception shape and
// committed rows are driver/URL behavior — recorded with the observed values.
String[] failureShape = new String[1];
tt.executeWithoutResult(s -> {
try {
jdbc.batchUpdate("insert into item values(?)", List.<Object[]>of(new Object[]{1}, new Object[]{2}, new Object[]{1}, new Object[]{3}));
throw new AssertionError("batch failure must surface");
} catch (DataAccessException caught) {
Throwable root = caught; while (root.getCause() != null) root = root.getCause();
failureShape[0] = root instanceof BatchUpdateException bue
? "BatchUpdateException counts=" + Arrays.toString(bue.getUpdateCounts())
: root.getClass().getSimpleName() + ": " + truncateMsg(root.getMessage());
System.out.println("batch-mysql rewrite=" + rewrite + " failure=" + failureShape[0]);
}
jdbc.update("insert into item values(9)");
});
var rows = jdbc.queryForList("select id from item order by id", Integer.class);
if (!rows.contains(9))
throw new AssertionError("layout A: post-catch write must commit, rows=" + rows);
if (rewrite && !rows.equals(List.of(9)))
throw new AssertionError("rewrite=true must fail the whole multi-value statement, rows=" + rows);
if (!rewrite && (rows.size() < 3 || Collections.frequency(rows, 1) != 1 || !rows.contains(2)))
throw new AssertionError("default URL: statements before/after the failure must commit once each, rows=" + rows);
System.out.println("PASS jdbc-u18 batch-mysql rewrite=" + rewrite + " partialCommitSameTx=" + rows
+ " failure=" + failureShape[0] + " (observed, driver/URL dependent)");
}
}
static void keysMysql() {
String url = requireMysqlUrl();
try (var ds = pool(url, 2)) {
var jdbc = new JdbcTemplate(ds);
jdbc.execute("drop table if exists t2");
jdbc.execute("create table t2(id bigint auto_increment primary key, tag varchar(20))");
var holder = new GeneratedKeyHolder();
jdbc.update(con -> {
PreparedStatement ps = con.prepareStatement("insert into t2(tag) values(?)", Statement.RETURN_GENERATED_KEYS);
ps.setString(1, "single");
return ps;
}, holder);
Number key = holder.getKey();
if (key == null || key.longValue() != 1) throw new AssertionError("single generated key, got " + holder.getKeyList());
var batchHolder = new GeneratedKeyHolder();
jdbc.batchUpdate(con -> con.prepareStatement("insert into t2(tag) values(?)", Statement.RETURN_GENERATED_KEYS),
new BatchPreparedStatementSetter() {
@Override public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setString(1, "row-" + i); }
@Override public int getBatchSize() { return 3; }
}, batchHolder);
int batchKeyCount = batchHolder.getKeyList().size();
System.out.println("keys-mysql rewrite=false singleKey=" + key.longValue() + " batchKeyCount=" + batchKeyCount);
// rewrite variant changes how the driver reports batch keys (observation, not a Spring contract)
String rewriteUrl = url + "&rewriteBatchedStatements=true";
try (var ds2 = pool(rewriteUrl, 2)) {
var jdbc2 = new JdbcTemplate(ds2);
jdbc2.update("truncate table t2");
var h2 = new GeneratedKeyHolder();
jdbc2.batchUpdate(con -> con.prepareStatement("insert into t2(tag) values(?)", Statement.RETURN_GENERATED_KEYS),
new BatchPreparedStatementSetter() {
@Override public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setString(1, "rw-" + i); }
@Override public int getBatchSize() { return 3; }
}, h2);
System.out.println("keys-mysql rewrite=true batchKeyCount=" + h2.getKeyList().size()
+ " keys=" + h2.getKeyList());
}
System.out.println("PASS jdbc-u18 keys-mysql singleKey=true batchKeysObserved=true");
}
}
static void dirtyReadMysql() {
String url = requireMysqlUrl();
try (var ds = pool(url, 2)) {
var jdbc = new JdbcTemplate(ds);
jdbc.execute("drop table if exists t3");
jdbc.execute("create table t3(id int primary key, v int)");
var tm = new org.springframework.jdbc.datasource.DataSourceTransactionManager(ds);
var tt = new TransactionTemplate(tm);
tt.setIsolationLevel(TransactionDefinition.ISOLATION_READ_UNCOMMITTED);
String[] sessionLevel = new String[1];
final Connection[] readerRef = new Connection[1];
try {
readerRef[0] = ds.getConnection();
final Connection reader = readerRef[0];
reader.setAutoCommit(false);
reader.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
var writer = new TransactionTemplate(tm);
writer.setIsolationLevel(TransactionDefinition.ISOLATION_READ_UNCOMMITTED);
writer.executeWithoutResult(s -> {
jdbc.update("insert into t3 values(1, 100)");
try (Statement st = reader.createStatement();
ResultSet rs = st.executeQuery("select count(*) from t3")) {
rs.next();
if (rs.getInt(1) != 1)
throw new AssertionError("MySQL READ-UNCOMMITTED reader must see the uncommitted row");
} catch (SQLException e) { throw new IllegalStateException(e); }
try (Statement st = DataSourceUtils.getConnection(ds).createStatement();
ResultSet rs = st.executeQuery("select @@session.transaction_isolation")) {
rs.next();
sessionLevel[0] = rs.getString(1);
} catch (SQLException e) { throw new IllegalStateException(e); }
s.setRollbackOnly(); // dirty read observed, then the writer transaction is rolled back
});
} catch (SQLException e) {
throw new IllegalStateException(e);
} finally {
if (readerRef[0] != null) { try { readerRef[0].rollback(); readerRef[0].close(); } catch (SQLException ignored) { } }
}
// reader saw the uncommitted row inside the writer tx; after writer rollback the row is gone
long after = jdbc.queryForObject("select count(*) from t3", Long.class);
if (after != 0) throw new AssertionError("writer rollback must erase the dirty row, rows=" + after);
// readOnly=true must actually block writes on MySQL (session read-only via Connector/J)
var ro = new TransactionTemplate(tm);
ro.setReadOnly(true);
String readOnlyError = null;
try {
ro.executeWithoutResult(s -> jdbc.update("insert into t3 values(2, 200)"));
} catch (DataAccessException e) {
Throwable root = e; while (root.getCause() != null) root = root.getCause();
readOnlyError = root.getMessage();
}
long rows = jdbc.queryForObject("select count(*) from t3", Long.class);
if (readOnlyError == null || rows != 0)
throw new AssertionError("MySQL readOnly must block the insert; error=" + readOnlyError + " rows=" + rows);
System.out.println("PASS jdbc-u18 dirtyread-mysql dirtyRead=seenByReader springIsolationApplied="
+ sessionLevel[0] + " afterWriterRollback=0 readOnlyInsertRejected=true rejectedWith="
+ truncate(readOnlyError, 120));
}
}
static String truncate(String s, int max) {
return s == null ? "null" : s.length() <= max ? s : s.substring(0, max) + "...";
}
}高级答法与架构师变化条件
高级答案应贯通："异常翻译是按产品名取错误码表、按 JDBC 4 子类、按 SQLState 的三级责任链，DuplicateKeyException 的 H2 路径和 MySQL 路径不同但收敛一致；批处理失败要区分 Spring 层（DataAccessException 包装）、驱动层（计数有无、是否越过失败点）、URL 层（rewrite 改变失败粒度）三层，事务边界决定部分执行的命运；生成键在 executeUpdate 后立即可读，空键列表静默为 null。"
日终导入从 1 万行涨到 500 万行：方案一单事务大批（batchSize=5000）——内存可控但长事务持锁、失败全部重来；方案二分片提交（每 5 万行一个事务 + 业务键幂等 + 批次游标持久化）——可断点续跑，代价是中间状态可见且不再是整体原子，配合对账与恢复兜底。锁等待敏感的库（生产 MySQL）还要估算单批持锁窗口，标注为推演非实测。评审清单：错误码表覆盖度（哪些 vendor code 会落进 Uncategorized）、批失败后业务方拿到的是部分成功还是整体失败、生成键消费方是否假设了"批量必返回全键"。三轮追问：为什么抓 SQLException 是坏味道（翻译链在 JdbcTemplate 内部、不同库异常形态不同）；批失败后 updateCounts 为什么不能当业务真相（EXECUTE_FAILED 占位与驱动差异）；rewriteBatchedStatements 上生产前要验证什么（失败粒度变化、生成键行为、SQL 日志变形）。
闭卷验收
本页 8 道闭卷题收进了下方自测面板：先回忆，再展开参考答案与评分点，最后给三档自评。