前言

公司项目用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的处理机制进行分析…

Logo

魔乐社区(Modelers.cn) 是一个中立、公益的人工智能社区,提供人工智能工具、模型、数据的托管、展示与应用协同服务,为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作,由全产业链共同建设、共同运营、共同享有,推动国产AI生态繁荣发展。

更多推荐