mybatis大批量插入PreparedStatement can have at most 65535 parameters/out-of-range integer as a 2-byte处理思路
mybatis大批量插入PreparedStatement can have at most 65535 parameters/out-of-range integer as a 2-byte处理思路
前言
公司项目用mybatis作为ORM框架,用内部代码生成框架生成的mybatis批量操作语句,在大批量场景如果不做处理会报PreparedStatement can have at most 65535 parameters,下文为在个人PC上复现场景:
建表语句:
-- 文档主表
CREATE TABLE t_document_m (
doc_m_id varchar(32) COLLATE pg_catalog.default NOT NULL,
doc_m_name varchar(100) COLLATE pg_catalog.default,
relate_biz_id varchar(32) COLLATE pg_catalog.default,
create_time timestamp(0),
create_by_uuid varchar(32) COLLATE pg_catalog.default,
create_by_account varchar(32) COLLATE pg_catalog.default,
create_by_name varchar(100) COLLATE pg_catalog.default,
last_update_time timestamp(0),
last_update_uuid varchar(32) COLLATE pg_catalog.default,
last_update_account varchar(32) COLLATE pg_catalog.default,
last_update_name varchar(100) COLLATE pg_catalog.default,
CONSTRAINT t_document_m_pkey PRIMARY KEY (doc_m_id)
)
;
-- 文档明细表
CREATE TABLE t_document_d (
doc_d_id varchar(32) COLLATE pg_catalog.default NOT NULL,
doc_m_id varchar(32) COLLATE pg_catalog.default,
doc_d_name varchar(255) COLLATE pg_catalog.default,
file_id varchar(32) COLLATE pg_catalog.default,
file_name varchar(100) COLLATE pg_catalog.default,
file_suffix varchar(10) COLLATE pg_catalog.default,
file_size varchar(10) COLLATE pg_catalog.default,
create_time timestamp(0),
create_by_uuid varchar(32) COLLATE pg_catalog.default,
create_by_account varchar(32) COLLATE pg_catalog.default,
create_by_name varchar(100) COLLATE pg_catalog.default,
last_update_time timestamp(0),
last_update_uuid varchar(32) COLLATE pg_catalog.default,
last_update_account varchar(32) COLLATE pg_catalog.default,
last_update_name varchar(100) COLLATE pg_catalog.default,
ext_attribute1 varchar(255) COLLATE pg_catalog.default,
ext_attribute2 varchar(255) COLLATE pg_catalog.default,
ext_attribute3 varchar(255) COLLATE pg_catalog.default,
ext_attribute4 varchar(255) COLLATE pg_catalog.default,
ext_attribute5 varchar(255) COLLATE pg_catalog.default,
ext_attribute6 varchar(255) COLLATE pg_catalog.default,
ext_attribute7 varchar(255) COLLATE pg_catalog.default,
ext_attribute8 varchar(255) COLLATE pg_catalog.default,
ext_attribute9 varchar(255) COLLATE pg_catalog.default,
ext_attribute10 varchar(255) COLLATE pg_catalog.default,
ext_attribute11 varchar(255) COLLATE pg_catalog.default,
ext_attribute12 varchar(255) COLLATE pg_catalog.default,
ext_attribute13 varchar(255) COLLATE pg_catalog.default,
ext_attribute14 varchar(255) COLLATE pg_catalog.default,
ext_attribute15 varchar(255) COLLATE pg_catalog.default,
ext_attribute16 varchar(255) COLLATE pg_catalog.default,
ext_attribute17 varchar(255) COLLATE pg_catalog.default,
ext_attribute18 varchar(255) COLLATE pg_catalog.default,
ext_attribute19 varchar(255) COLLATE pg_catalog.default,
ext_attribute20 varchar(255) COLLATE pg_catalog.default,
ext_attribute21 varchar(255) COLLATE pg_catalog.default,
ext_attribute22 varchar(255) COLLATE pg_catalog.default,
ext_attribute23 varchar(255) COLLATE pg_catalog.default,
ext_attribute24 varchar(255) COLLATE pg_catalog.default,
ext_attribute25 varchar(255) COLLATE pg_catalog.default,
ext_attribute26 varchar(255) COLLATE pg_catalog.default,
ext_attribute27 varchar(255) COLLATE pg_catalog.default,
ext_attribute28 varchar(255) COLLATE pg_catalog.default,
ext_attribute29 varchar(255) COLLATE pg_catalog.default,
ext_attribute30 varchar(255) COLLATE pg_catalog.default,
ext_attribute31 varchar(255) COLLATE pg_catalog.default,
ext_attribute32 varchar(255) COLLATE pg_catalog.default,
ext_attribute33 varchar(255) COLLATE pg_catalog.default,
ext_attribute34 varchar(255) COLLATE pg_catalog.default,
ext_attribute35 varchar(255) COLLATE pg_catalog.default,
ext_attribute36 varchar(255) COLLATE pg_catalog.default,
ext_attribute37 varchar(255) COLLATE pg_catalog.default,
ext_attribute38 varchar(255) COLLATE pg_catalog.default,
ext_attribute39 varchar(255) COLLATE pg_catalog.default,
ext_attribute40 varchar(255) COLLATE pg_catalog.default,
ext_attribute41 varchar(255) COLLATE pg_catalog.default,
ext_attribute42 varchar(255) COLLATE pg_catalog.default,
ext_attribute43 varchar(255) COLLATE pg_catalog.default,
ext_attribute44 varchar(255) COLLATE pg_catalog.default,
ext_attribute45 varchar(255) COLLATE pg_catalog.default,
ext_attribute46 varchar(255) COLLATE pg_catalog.default,
ext_attribute47 varchar(255) COLLATE pg_catalog.default,
ext_attribute48 varchar(255) COLLATE pg_catalog.default,
ext_attribute49 varchar(255) COLLATE pg_catalog.default,
ext_attribute50 varchar(255) COLLATE pg_catalog.default,
ext_attribute51 varchar(255) COLLATE pg_catalog.default,
ext_attribute52 varchar(255) COLLATE pg_catalog.default,
ext_attribute53 varchar(255) COLLATE pg_catalog.default,
ext_attribute54 varchar(255) COLLATE pg_catalog.default,
ext_attribute55 varchar(255) COLLATE pg_catalog.default,
ext_attribute56 varchar(255) COLLATE pg_catalog.default,
ext_attribute57 varchar(255) COLLATE pg_catalog.default,
ext_attribute58 varchar(255) COLLATE pg_catalog.default,
ext_attribute59 varchar(255) COLLATE pg_catalog.default,
ext_attribute60 varchar(255) COLLATE pg_catalog.default,
CONSTRAINT t_document_d_pkey PRIMARY KEY (doc_d_id)
)
;
COMMENT ON COLUMN t_document_d.doc_d_id IS '文档明细ID';
COMMENT ON COLUMN t_document_d.doc_m_id IS '文档主表ID';
COMMENT ON COLUMN t_document_d.doc_d_name IS '文档主表名称';
COMMENT ON COLUMN t_document_d.file_id IS '文件业务ID';
COMMENT ON COLUMN t_document_d.file_name IS '文件名称';
COMMENT ON COLUMN t_document_d.file_suffix IS '文件后缀';
COMMENT ON COLUMN t_document_d.file_size IS '单位kb';
COMMENT ON COLUMN t_document_d.create_time IS '创建时间';
COMMENT ON COLUMN t_document_d.create_by_uuid IS '创建人uuid';
COMMENT ON COLUMN t_document_d.create_by_account IS '创建人账号';
COMMENT ON COLUMN t_document_d.create_by_name IS '创建人名称';
COMMENT ON COLUMN t_document_d.last_update_time IS '最后更新时间';
COMMENT ON COLUMN t_document_d.last_update_uuid IS '最后跟新人uuid';
COMMENT ON COLUMN t_document_d.last_update_account IS '最后更新人账号';
COMMENT ON COLUMN t_document_d.last_update_name IS '最后更新人名称';
COMMENT ON COLUMN t_document_d.ext_attribute1 IS '拓展属性1';
COMMENT ON COLUMN t_document_d.ext_attribute2 IS '拓展属性2';
COMMENT ON COLUMN t_document_d.ext_attribute3 IS '拓展属性3';
COMMENT ON COLUMN t_document_d.ext_attribute4 IS '拓展属性4';
COMMENT ON COLUMN t_document_d.ext_attribute5 IS '拓展属性5';
COMMENT ON COLUMN t_document_d.ext_attribute6 IS '拓展属性6';
COMMENT ON COLUMN t_document_d.ext_attribute7 IS '拓展属性7';
COMMENT ON COLUMN t_document_d.ext_attribute8 IS '拓展属性8';
COMMENT ON COLUMN t_document_d.ext_attribute9 IS '拓展属性9';
COMMENT ON COLUMN t_document_d.ext_attribute10 IS '拓展属性10';
COMMENT ON COLUMN t_document_d.ext_attribute11 IS '拓展属性11';
COMMENT ON COLUMN t_document_d.ext_attribute12 IS '拓展属性12';
COMMENT ON COLUMN t_document_d.ext_attribute13 IS '拓展属性13';
COMMENT ON COLUMN t_document_d.ext_attribute14 IS '拓展属性14';
COMMENT ON COLUMN t_document_d.ext_attribute15 IS '拓展属性15';
COMMENT ON COLUMN t_document_d.ext_attribute16 IS '拓展属性16';
COMMENT ON COLUMN t_document_d.ext_attribute17 IS '拓展属性17';
COMMENT ON COLUMN t_document_d.ext_attribute18 IS '拓展属性18';
COMMENT ON COLUMN t_document_d.ext_attribute19 IS '拓展属性19';
COMMENT ON COLUMN t_document_d.ext_attribute20 IS '拓展属性20';
COMMENT ON COLUMN t_document_d.ext_attribute21 IS '拓展属性21';
COMMENT ON COLUMN t_document_d.ext_attribute22 IS '拓展属性22';
COMMENT ON COLUMN t_document_d.ext_attribute23 IS '拓展属性23';
COMMENT ON COLUMN t_document_d.ext_attribute24 IS '拓展属性24';
COMMENT ON COLUMN t_document_d.ext_attribute25 IS '拓展属性25';
COMMENT ON COLUMN t_document_d.ext_attribute26 IS '拓展属性26';
COMMENT ON COLUMN t_document_d.ext_attribute27 IS '拓展属性27';
COMMENT ON COLUMN t_document_d.ext_attribute28 IS '拓展属性28';
COMMENT ON COLUMN t_document_d.ext_attribute29 IS '拓展属性29';
COMMENT ON COLUMN t_document_d.ext_attribute30 IS '拓展属性30';
COMMENT ON COLUMN t_document_d.ext_attribute31 IS '拓展属性31';
COMMENT ON COLUMN t_document_d.ext_attribute32 IS '拓展属性32';
COMMENT ON COLUMN t_document_d.ext_attribute33 IS '拓展属性33';
COMMENT ON COLUMN t_document_d.ext_attribute34 IS '拓展属性34';
COMMENT ON COLUMN t_document_d.ext_attribute35 IS '拓展属性35';
COMMENT ON COLUMN t_document_d.ext_attribute36 IS '拓展属性36';
COMMENT ON COLUMN t_document_d.ext_attribute37 IS '拓展属性37';
COMMENT ON COLUMN t_document_d.ext_attribute38 IS '拓展属性38';
COMMENT ON COLUMN t_document_d.ext_attribute39 IS '拓展属性39';
COMMENT ON COLUMN t_document_d.ext_attribute40 IS '拓展属性40';
COMMENT ON COLUMN t_document_d.ext_attribute41 IS '拓展属性41';
COMMENT ON COLUMN t_document_d.ext_attribute42 IS '拓展属性42';
COMMENT ON COLUMN t_document_d.ext_attribute43 IS '拓展属性43';
COMMENT ON COLUMN t_document_d.ext_attribute44 IS '拓展属性44';
COMMENT ON COLUMN t_document_d.ext_attribute45 IS '拓展属性45';
COMMENT ON COLUMN t_document_d.ext_attribute46 IS '拓展属性46';
COMMENT ON COLUMN t_document_d.ext_attribute47 IS '拓展属性47';
COMMENT ON COLUMN t_document_d.ext_attribute48 IS '拓展属性48';
COMMENT ON COLUMN t_document_d.ext_attribute49 IS '拓展属性49';
COMMENT ON COLUMN t_document_d.ext_attribute50 IS '拓展属性50';
COMMENT ON COLUMN t_document_d.ext_attribute51 IS '拓展属性51';
COMMENT ON COLUMN t_document_d.ext_attribute52 IS '拓展属性52';
COMMENT ON COLUMN t_document_d.ext_attribute53 IS '拓展属性53';
COMMENT ON COLUMN t_document_d.ext_attribute54 IS '拓展属性54';
COMMENT ON COLUMN t_document_d.ext_attribute55 IS '拓展属性55';
COMMENT ON COLUMN t_document_d.ext_attribute56 IS '拓展属性56';
COMMENT ON COLUMN t_document_d.ext_attribute57 IS '拓展属性57';
COMMENT ON COLUMN t_document_d.ext_attribute58 IS '拓展属性58';
COMMENT ON COLUMN t_document_d.ext_attribute59 IS '拓展属性59';
COMMENT ON COLUMN t_document_d.ext_attribute60 IS '拓展属性60';
COMMENT ON TABLE t_document_d IS '文档明细表';
批量新增语句
<insert id="batchInsert" parameterType="java.util.List">
INSERT INTO t_document_d (
doc_d_id, doc_m_id, doc_d_name, file_id, file_name, file_suffix, file_size,
create_time, create_by_uuid, create_by_account, create_by_name,
last_update_time, last_update_uuid, last_update_account, last_update_name,
ext_attribute1, ext_attribute2, ext_attribute3, ext_attribute4, ext_attribute5,
ext_attribute6, ext_attribute7, ext_attribute8, ext_attribute9, ext_attribute10,
ext_attribute11, ext_attribute12, ext_attribute13, ext_attribute14, ext_attribute15,
ext_attribute16, ext_attribute17, ext_attribute18, ext_attribute19, ext_attribute20,
ext_attribute21, ext_attribute22, ext_attribute23, ext_attribute24, ext_attribute25,
ext_attribute26, ext_attribute27, ext_attribute28, ext_attribute29, ext_attribute30,
ext_attribute31, ext_attribute32, ext_attribute33, ext_attribute34, ext_attribute35,
ext_attribute36, ext_attribute37, ext_attribute38, ext_attribute39, ext_attribute40,
ext_attribute41, ext_attribute42, ext_attribute43, ext_attribute44, ext_attribute45,
ext_attribute46, ext_attribute47, ext_attribute48, ext_attribute49, ext_attribute50,
ext_attribute51, ext_attribute52, ext_attribute53, ext_attribute54, ext_attribute55,
ext_attribute56, ext_attribute57, ext_attribute58, ext_attribute59, ext_attribute60
) VALUES
<foreach collection="list" item="item" separator=",">
(
#{item.docDId}, #{item.docMId}, #{item.docDName}, #{item.fileId}, #{item.fileName}, #{item.fileSuffix}, #{item.fileSize},
#{item.createTime}, #{item.createByUuid}, #{item.createByAccount}, #{item.createByName},
#{item.lastUpdateTime}, #{item.lastUpdateUuid}, #{item.lastUpdateAccount}, #{item.lastUpdateName},
#{item.extAttribute1}, #{item.extAttribute2}, #{item.extAttribute3}, #{item.extAttribute4}, #{item.extAttribute5},
#{item.extAttribute6}, #{item.extAttribute7}, #{item.extAttribute8}, #{item.extAttribute9}, #{item.extAttribute10},
#{item.extAttribute11}, #{item.extAttribute12}, #{item.extAttribute13}, #{item.extAttribute14}, #{item.extAttribute15},
#{item.extAttribute16}, #{item.extAttribute17}, #{item.extAttribute18}, #{item.extAttribute19}, #{item.extAttribute20},
#{item.extAttribute21}, #{item.extAttribute22}, #{item.extAttribute23}, #{item.extAttribute24}, #{item.extAttribute25},
#{item.extAttribute26}, #{item.extAttribute27}, #{item.extAttribute28}, #{item.extAttribute29}, #{item.extAttribute30},
#{item.extAttribute31}, #{item.extAttribute32}, #{item.extAttribute33}, #{item.extAttribute34}, #{item.extAttribute35},
#{item.extAttribute36}, #{item.extAttribute37}, #{item.extAttribute38}, #{item.extAttribute39}, #{item.extAttribute40},
#{item.extAttribute41}, #{item.extAttribute42}, #{item.extAttribute43}, #{item.extAttribute44}, #{item.extAttribute45},
#{item.extAttribute46}, #{item.extAttribute47}, #{item.extAttribute48}, #{item.extAttribute49}, #{item.extAttribute50},
#{item.extAttribute51}, #{item.extAttribute52}, #{item.extAttribute53}, #{item.extAttribute54}, #{item.extAttribute55},
#{item.extAttribute56}, #{item.extAttribute57}, #{item.extAttribute58}, #{item.extAttribute59}, #{item.extAttribute60}
)
</foreach>
</insert>
问题复现
执行批量新增测试代码,对宽表t_document_d(75个字段)一次性插入500条(总共37500)
@Test
@Transactional(rollbackFor = Exception.class)
void batchInsert() {
// 插入主表
DocumentM documentM = new DocumentM();
documentM.setDocMName("大溪地芒果种植");
documentM.setCreateTime(LocalDateTime.now());
documentM.setCreateByUuid("pine_tree_uuid");
documentM.setCreateByName("pine_tree_name");
documentM.setCreateByAccount("pine_tree_account");
documentM.setLastUpdateTime(LocalDateTime.now());
documentM.setLastUpdateUuid("pine_tree_uuid");
documentM.setLastUpdateName("pine_tree_name");
documentM.setLastUpdateAccount("pine_tree_account");
documentM.setRelateBizId("good_biz_id");
documentMMapper.insert(documentM);
// 插入明细表
String docMId = documentM.getDocMId();
List<DocumentD> dList = new ArrayList<>();
for (int i = 0; i < 500; i++) {
DocumentD documentD = new DocumentD();
documentD.setDocMId(docMId);
documentD.setDocDName("明细名称-" + i);
documentD.setFileId(UUID.randomUUID().toString().replace("-", ""));
documentD.setFileName("fileName-" + i);
documentD.setFileSuffix(".png");
documentD.setFileSuffix("200kb");
documentD.setCreateTime(LocalDateTime.now());
documentD.setCreateByUuid("pine_tree_uuid");
documentD.setCreateByName("pine_tree_name");
documentD.setCreateByAccount("pine_tree_account");
documentD.setLastUpdateTime(LocalDateTime.now());
documentD.setLastUpdateUuid("pine_tree_uuid");
documentD.setLastUpdateName("pine_tree_name");
documentD.setLastUpdateAccount("pine_tree_account");
dList.add(documentD);
}
documentDMapper.batchInsert(dList);
}
不同版本的PG依赖提示信息会有所差异,当前提示Caused by: java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: xxx,有的版本会提示PreparedStatement can have at most 65535 parameters
解决思路
从需求上解决
对于宽表进行大批量新增/更新操作并不是一个常见的场景,也许只是在sa进行业务设计时未考虑到单次执行上限,可与业务进行探讨,是否需要加入明细行单次处理上限,在业务层和前端加上校验即可。
限制每批数量
如果确实需要对宽表进行大批量新增/更新操作,如定时任务批量新增/刷新数w笔数据,为了在进行批量操作时不触发超出参数限制异常,可以考虑分批处理。
此处使用Lists.partition实现分批,需引入依赖
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>32.0.1-android</version>
</dependency>
Lists.partition(dList, 200).forEach(sub -> documentMMapper.batchInsert(sub));
可知500行按照没批次200行调用了3次batchInsert

附加问题
在测试过程中,控制台执行成功,但是一直写不进数据,最终发现是由于@Test和@Transactional(rollbackFor = Exception.class)同时使用会回滚数据库操作,注释掉@Transactional(rollbackFor = Exception.class)即可

为什么@Test和@Transactional(rollbackFor = Exception.class)同时使用会回滚数据库操作
借助AI大法:
使用 @Test 和 @Transactional(rollbackFor = Exception.class) 注解时,数据库操作会被回滚,这是由 Spring 的事务管理机制和测试框架的行为共同决定的。以下是详细的原因和机制:
1. Spring 的事务管理机制
@Transactional 是 Spring 提供的事务管理注解。它的作用是:
在方法执行前开启一个事务。
在方法执行成功后提交事务。
在方法抛出异常时回滚事务。
rollbackFor = Exception.class 表示当方法抛出 Exception 或其子类异常时,事务会回滚。
2. Spring 测试框架的行为
在 Spring 的测试框架中,@Test 注解标记的方法默认会在测试完成后回滚事务。这是为了确保测试不会对数据库产生副作用,保持测试环境的干净。
具体行为如下:
测试方法执行前,Spring 会开启一个事务。
测试方法执行后,Spring 会回滚事务,即使测试方法没有抛出异常。
3. 为什么测试方法会回滚事务
Spring 测试框架默认回滚事务的原因是为了:
隔离测试环境:确保每个测试方法都在一个干净的环境中运行,不受其他测试方法的影响。
避免污染数据库:测试数据不会被保存到数据库中,避免对生产数据或后续测试产生影响。
提高测试效率:回滚事务比删除测试数据更快。
4. 如何避免回滚
如果你希望在测试方法中提交事务(即数据真正插入到数据库中),可以通过以下方式实现:
方法 1:禁用测试方法的事务回滚
在 @Test 方法上添加 @Rollback(false) 注解,明确告诉 Spring 不要回滚事务。
@Test
@Transactional
@Rollback(false) // 禁用回滚
void batchInsert() {
// 插入操作
userMapper.insert(new User("John", 25));
}
方法 2:手动提交事务
在测试方法中手动提交事务。
@Test
@Transactional
void batchInsert() {
// 插入操作
userMapper.insert(new User("John", 25));
// 手动提交事务
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly(false);
}
方法 3:不使用 @Transactional
如果不需要事务管理,可以直接去掉 @Transactional 注解。
@Test
void batchInsert() {
// 插入操作
userMapper.insert(new User("John", 25));
}
5. 测试方法的事务边界
需要注意的是,@Transactional 注解在测试方法中的行为与在普通服务方法中的行为略有不同:
在普通服务方法中,@Transactional 默认会在方法执行成功后提交事务。
在测试方法中,@Transactional 默认会在方法执行完成后回滚事务。
这种行为差异是由 Spring 测试框架的设计决定的。
6. 总结
使用 @Test 和 @Transactional(rollbackFor = Exception.class) 时,数据库操作会被回滚的原因如下:
Spring 测试框架默认会回滚测试方法的事务,以保持测试环境的干净。
@Transactional 注解会开启事务,并在测试方法执行完成后回滚事务。
如果你希望测试方法提交事务,可以通过以下方式实现:
使用 @Rollback(false) 注解。
手动提交事务。
去掉 @Transactional 注解。
通过理解 Spring 的事务管理和测试框架的行为,可以更好地控制测试方法中的事务边界。
总结
如果用mybatis这种批量语句,在数据量较大的场景均需要考虑单批次处理的最大数量,但是我在使用mybatis-plus时并没有触发超过最大参数的异常,后续可能会对mybatis-plus的处理机制进行分析…
魔乐社区(Modelers.cn) 是一个中立、公益的人工智能社区,提供人工智能工具、模型、数据的托管、展示与应用协同服务,为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作,由全产业链共同建设、共同运营、共同享有,推动国产AI生态繁荣发展。
更多推荐


所有评论(0)