基于MyBatis依次、批量、分页增删改查

我们知道处理数据有三种思路：依次、批量、分页，对应方法如下

• 依次处理：在 Java 里面写 for 循环，依次使用 SQL 语句，频繁连接断开数据库
• 批量处理：在 MyBatis 里面用 <foreach> 拼接成一条长 SQL 语句，仅连接断开一次数据库
• 分页处理：上面两种方案进行折中，每次将一部分批量写入

注意，若用 <foreach> 以 ; 分隔多条 SQL 语句发给数据库（需要在配置里添加 allowMultiQueries=true），这种处理虽然也可以算是某种程度上的“批量”，但其 SQL 语句并未在同一次提交中，因而本质上和依次处理没区别

选取哪种方法取决于你的数据量（记录数 × 字段数，也就是行数 × 列数）大小

• 当有一定的数据量后（千量级），依次处理非常慢，最好使用批量处理

• 而数据量过于庞大时（十万量级）如果还采取 <foreach> 进行批量处理，整个过程十分耗时，它处理时间和数据量的关系就像是倒过来的抛物线，如下图所示

• 通常需要进行折中，使用分页处理，根据笔者自己经验而言，将每次的数据量定在 12000 左右效果较好，不过这个具体要视情况而定

💬相关

博客文章《mybatis之foreach用法》

https://www.cnblogs.com/fnlingnzb-learner/p/10566452.html

博客文章《MyBatis批量插入几千条数据，请慎用foreach》

https://blog.csdn.net/SharingOfficer/article/details/121431154

以下给出一个示例场景

• 建立数据表 data_table，含有字段 id、field1 、 field2
• 建立 Java 类 Data，含有属性 id、attr1 、 attr2
• 将 Java 对象或含有对象的列表作为参数，将对应的数据在数据库增删改查

下面给出依次操作和批量操作的 MyBatis 实现，而分页操作在 MyBatis 的实现和批量操作是一样的，不一样的地方在 Java 代码中

数据库配置

配置文件 application.properties 常见配置

• serverTimezone：时区，如亚洲/上海时区 Asia/Shanghai
• useUnicode 和 characterEncoding ：编码方式，如 true 和 utf8
• allowMultiQueries ：是否支持”;"号分隔的多条 SQL 语句的执行，如 true
• autoReconnect ：是否超时重连（当一个连接的空闲时间超过 8 小时后，MySQL就会断开该连接），如 true
• useSSL：是否使用 SSL，如 true

spring.datasource.url=jdbc:mysql://<域名或IP地址>:<端口号>/<数据库名>?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=Asia/Shanghai

查询数据

对应 SQL 的 SELECT 语句

<select id="selectData" parameterType="String" resultType="Data">SELECT * FROM `data_table`WHERE `id` = #{id}
</select><select id="batchSelectData" resultType="Data">SELECT * FROM `data_table`WHERE `id` IN<foreach collection="list" item="item" open="(" close=")" separator=",">#{item}</foreach>
</select>

对应生成的 SQL 语句

SELECT * FROM `data_table`
WHERE `id` = ?

SELECT * FROM `data_table`
WHERE `id` IN (?,?,?)

插入数据

对应 SQL 的 INSERT INTO 语句

注意，若设置了主键，且表中存在相同主键的数据，则无法插入

    <insert id="insertData" parameterType="Data">INSERT INTO `data_table`(`id`,`field1`,`field2`)VALUES (#{id},#{attr1},#{attr2})</insert><insert id="batchInsertData" parameterType="java.util.List">INSERT INTO `data_table`(`id`,`field1`,`field2`)VALUES<foreach collection="list" item="item" index="index" separator="," >(#{item.id},#{item.attr1},#{item.attr2})</foreach></insert>

对应生成的 SQL 语句

INSERT INTO `data_table`(`id`,`field1`,`field2`)
VALUES (?,?,?)

INSERT INTO `data_table`(`id`,`field1`,`field2`)
VALUES(?,?,?),(?,?,?),(?,?,?)

更新数据

对应 SQL 的 UPDATE 语句

配合 SQL 中的 WHEN...THEN... 语句，这相当于其他编程语言中的 switch 语句

<update id="updateData" parameterType="Data">UPDATE `data_table`SET `field1` = #{attr1}, `field2` = #{attr2}WHERE `id` = #{id}
</update><update id="batchUpdateData" parameterType="java.util.List">UPDATE `data_table`<trim prefix="SET" suffixOverrides=","><trim prefix=" `field1` = CASE " suffix=" END, "><foreach collection="list" item="item">WHEN `id` = #{item.id} THEN #{item.attr1}</foreach></trim><trim prefix=" `field2` = CASE " suffix=" END, "><foreach collection="list" item="item">WHEN `id` = #{item.id} THEN #{item.attr2}</foreach></trim></trim>WHERE `id` IN<foreach collection="list" item="item" open="(" close=")" separator=",">#{item.id}</foreach>
</update>

对应生成的 SQL 语句

UPDATE `data_table`
SET `field1` = ?, `field2` = ?
WHERE `id` = ?

UPDATE `data_table`
SET `field1` =CASEWHEN `id` = ? THEN ?WHEN `id` = ? THEN ?WHEN `id` = ? THEN ?END,`field2` =CASEWHEN `id` = ? THEN ?WHEN `id` = ? THEN ?WHEN `id` = ? THEN ?END 
WHERE`id` IN (?,?,?)

插入或更新数据

仅 MySQL 支持，且需要设置主键

对应 MySQL 的REPLACE INTO 或 INSERT INTO... ON DUPLICATE KEY UPDATE ... 的语句

二者均是根据主键判断表中是否含有重复的数据，若无就直接插入，若有，前者是先删再插，后者是更新

从底层执行效率上来讲，REPLACE INTO 效率更高，但部分场景并不适合，需慎用

当然，你也可以先 DELETE 再 INSERT INTO 达到同样的效果，但显然没有上面二者高效

💬相关

文章《慎用mysql replace语句》

https://developer.aliyun.com/article/627744

由于 REPLACE INTO 和 INSERT INTO 的 MyBatis 实现是几乎一样的，此处就不再赘述了

<insert id="insertOrUpdateData" parameterType="Data">INSERT INTO `data_table` (`id`,`field1`,`field2`)VALUES (#{id},#{attr1},#{attr2})ON DUPLICATE KEY UPDATE`id` = VALUES(`id`),`field1` = VALUES(`field1`),`field2` = VALUES(`field2`)
</insert><insert id="batchInsertOrUpdateData" parameterType="java.util.List">INSERT INTO `data_table` (`id`,`field1`,`field2`)VALUES<foreach collection="list" item="item" separator=",">(#{item.id},#{item.attr1},#{item.attr2})</foreach>ON DUPLICATE KEY UPDATE`id` = VALUES(`id`),`field1` = VALUES(`field1`),`field2` = VALUES(`field2`)
</insert>

对应生成的 SQL 语句

INSERT INTO `data_table` (`id`,`field1`,`field2`)
VALUES (?,?,?)
ON DUPLICATE KEY UPDATE`id` = VALUES(`id`),`field1` = VALUES(`field1`),`field2` = VALUES(`field2`)

INSERT INTO `data_table`(`id`,`field1`,`field2`)
VALUES (?,?,?),(?,?,?),(?,?,?)
ON DUPLICATE KEY UPDATE`id` = VALUES(`id`),`field1` = VALUES(`field1`),`field2` = VALUES(`field2`)

删除数据

对应 SQL 的 DELETE 语句

<delete id="deleteData" parameterType="String">DELETE FROM `data_table` WHERE `id` = #{id}
</delete><delete id="batchDeleteData">DELETE FROM `data_table`WHERE `id` IN<foreach collection="list" item="item" open="(" close=")" separator=",">#{item}</foreach>
</delete>

对应生成的 SQL 语句

DELETE FROM `data_table` WHERE `id` = ?

DELETE FROM `data_table`
WHERE `id` IN (?,?,?)

清空数据

对应 SQL 的 TRUNCATE 语句

<update id="clearData">TRUNCATE TABLE `data_table`
</update>

对应生成的 SQL 语句

TRUNCATE TABLE `data_table`

💬相关

看完本文后可以进一步阅读博客文章《基于SpringBoot的数据迁移模板》

https://blog.csdn.net/weixin_42077074/article/details/128868655