基于MyBatis实现依次、批量、分页增删改查操作

梦未

已于 2023-04-14 17:49:29 修改

阅读量1.4k

点赞数

分类专栏：编程基础 # Java 文章标签： mybatis java 数据库 sql

于 2023-03-08 16:12:50 首次发布

本文链接：https://blog.csdn.net/weixin_42077074/article/details/129405833

版权

编程基础同时被 2 个专栏收录

35 篇文章 1 订阅

订阅专栏

Java

18 篇文章 1 订阅

订阅专栏

📚专栏

「Java数据集成」专栏

《Java发起HTTP请求并解析JSON返回数据》：下图简称为《请求和解析》
《基于MyBatis实现依次、批量、分页增删改查操作》：下图简称为《依批分增删改查》
《用Python根据JSON生成Java类代码和数据库建表SQL语句》：下图简称为《生成代码脚本》
《基于SpringBoot+MyBatis的数据增删改查模板》：下图简称为《增删改查模板》
《Java发起同异步HTTP请求和处理数据》：下图简称为《同异步请求和处理》
《基于SpringBoot+MyBatis的数据集成模板》：下图简称为《数据集成模板》
《JavaHTTP请求工具类HTTPUtils》：下图简称为《HTTP请求工具类》
《JavaJSON处理工具类JSONUtils》：下图简称为《JSON处理工具类》
《JavaXML处理工具类XMLUtils》：下图简称为《XML处理工具类》
《用Python生成随机JSON数据》：下图简称为《生成随机数据脚本》

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

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

注意，若用 <foreach> 以 ; 分隔多条 SQL 语句发给数据库（需要在配置里添加 allowMultiQueries=true），这种处理虽然也可以算是某种程度上的“批量”，但实际上会被 MyBatis 拆分成若干条独立的 SQL 语句发送给数据库，也就是说 SQL 语句并未在同一次提交中，同样会频繁连接数据库，因而本质上和依次处理没区别，不是真正意义上的“批量”

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

当有一定的数据量后（千量级），依次处理非常慢，最好使用批量处理
而数据量过于庞大时（十万量级）如果还采取 <foreach> 进行批量处理，整个过程十分耗时，它处理时间和数据量的关系就像是倒过来的抛物线，如下图所示
通常需要进行折中，使用分页处理，根据笔者自己经验而言，将每次的数据量定在 12000 左右效果较好，不过这个具体要视情况而定

关于 <foreach> 的用法和其利弊本文不再赘述，可以查看下面两篇博客文章

💬相关

博客文章《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-Plus，而分页操作在 MyBatis 的实现和批量操作是一样的，不一样的地方在 Java 代码中

而基于 Spring Boot + MyBatis 的完整实现可以在看完本文后再看下文

💬相关

博客文章《基于Spring Boot+MyBatis的数据增删改查模板》

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

数据库配置

配置文件 application.properties 常见配置

spring.datasource.driver-class-name：数据库驱动，如常见的 MySQL 数据库驱动 com.mysql.jdbc.Driver
spring.datasource.url：数据库的连接信息
- serverTimezone：时区，如亚洲/上海时区 Asia/Shanghai
- useUnicode 和 characterEncoding ：编码方式，如 true 和 utf8
- allowMultiQueries ：是否支持”;"号分隔的多条 SQL 语句的执行，如 true
- autoReconnect ：是否超时重连（当一个连接的空闲时间超过 8 小时后，MySQL就会断开该连接），如 true
- useSSL：是否使用 SSL，如 true
spring.datasource.username：数据库用户名
spring.datasource.password：数据库密码

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://<域名或IP地址>:<端口号>/<数据库名>?autoReconnect=true&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=Asia/Shanghai&useSSL=false&allowMultiQueries=true
spring.datasource.username=<数据库用户名>
spring.datasource.password=<数据库密码>

MyBatis 配置

配置文件 application.properties 常见配置

mybatis.typeAliasesPackage：配置别名包扫描路径，用于简化映射文件中的类型引用。如值设为了 com.example.pojo 后就可以将 MyBatis 的 XML 映射文件中的标签属性 resultType="com.example.pojo.Data" 简化为 resultType="Data"
mybatis.mapperLocations：XML 映射文件的位置
mybatis.configuration.mapUnderscoreToCamelCase：是否启用命名法转换。若开启，MyBatis 会自动将数据库字段的蛇式命名法（下划线命名法）转换成 Java 类的驼峰命名法
mybatis.configuration.cacheEnabled：是否开启缓存。若开启，MyBatis 会将查询结果缓存起来，以便在后续的查询中可以直接从缓存中读取数据

# MyBatis
mybatis.typeAliasesPackage=com.example.pojo
mybatis.mapperLocations=classpath:mapper/*.xml
mybatis.configuration.mapUnderscoreToCamelCase=true
mybatis.configuration.cacheEnabled=false

考虑参数及类型映射

简单来说，就是要告诉 MyBatis 如何将 Java 对象和数据表记录相互映射

一般而言，需要在 MyBatis 标签（如 <select>、<insert>等）中 parameterType 和 resultType 属性里写上传递给 SQL 语句的参数类型和执行结果返回的类型，在占位符 #{} 或 ${} 中传递参数，倘若涉及较为复杂的自定义 POJO 类型，还需在 <parameterMap> 和 <resultMap> 给出映射规则

MyBatis 标签
- parameterType 属性
  - 如基本类型 parameterType="int"
  - 如数组类型 parameterType="int[]"
  - 如集合类型 parameterType="java.util.Map<java.lang.String, java.lang.Object>"
  - 如 POJO 类型 parameterType="com.example.pojo.Data"
- resultType 属性
  - 写法类似于上述的 parameterType 属性
  - 对于返回影响行数的 SQL 操作（如 INSERT、UPDARE、DELERE），MyBatis 会默认将返回值映射为 int 类型，可以不写 resultType="int"
- 自动类型推断
  - 如果使用 MyBatis 3.5.0 及以上版本，具有自动类型推断功能，简单的情况可以不指定 parameterType 属性 resultType 属性，但还是建议指定
#{} 和 ${} 占位符
- #{} 可传递参数，它可以防止 SQL 注入，自动进行必要的转义和类型转换
- ${} 用于替换字符串常量，它不会对参数进行转义和类型转换，容易受到 SQL 注入攻击
<parameterMap> 和 <resultMap> 标签
- <parameterMap> 由若干 <parameter> 组成，一个 <parameter> 表明一个字段映射到一个属性
- 但多数时候，用占位符 #{} 就已经能表明属性名、自动转义和类型转换了，不太需要 <parameterMap>
- <resultMap>由若干 <result> 组成，一个 <result> 表明一个属性映射到一个字段
  - property 属性说明 Java 类中的属性名
  - column 属性说明数据库中的字段名
  - javaType 属性说明 Java 类型，一般是可以不指定的，因为#{}可以自动转义和类型转换，但假如传值有 null 时，就必须手动指定 javaType
  - jdbcType 属性说明在数据库中的数据类型（JDBC 类型），不指定的话 MyBatis 会自动根据 Java 类型来推断数据库类型，但这不一定是可靠的，建议指定
- 若不写，则默认 POJO 类属性名和相同的字段名相互映射
- 若不写且配置了 mybatis.configuration.mapUnderscoreToCamelCase=true，则按驼峰命名法的 POJO 类属性名和蛇式命名法（下划线命名法）的字段名相互映射

对于本文的例子而言可以有下面的映射规则

<parameterMap id="dataParameterMap" type="Data">
    <parameter property="id" column="id" javaType="java.lang.String" jdbcType="VARCHAR" />
    <parameter property="attr1" column="field1" javaType="java.lang.String" jdbcType="VARCHAR" />
    <parameter property="attr2" column="field2" javaType="java.lang.String" jdbcType="VARCHAR" />
</parameterMap>

<resultMap id="dataResultMap" type="Data">
    <result column="id" property="id" jdbcType="VARCHAR" javaType="java.lang.String" />
    <result column="field1" property="field1" jdbcType="VARCHAR" javaType="java.lang.String" />
    <result column="field2" property="field2" jdbcType="VARCHAR" javaType="java.lang.String" />
</resultMap>

查询数据

对应 SQL 的 SELECT 语句

<select id="selectData" parameterType="String" resultType="Data" resultMap="dataResultMap">
    SELECT * FROM `data_table`
    WHERE `id` = #{id}
</select>

<select id="batchSelectData" resultType="Data" resultMap="dataResultMap">
    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` =
	CASE
		WHEN `id` = ? THEN ?
		WHEN `id` = ? THEN ?
		WHEN `id` = ? THEN ?
	END,
	`field2` =
	CASE
		WHEN `id` = ? THEN ?
		WHEN `id` = ? THEN ?
		WHEN `id` = ? THEN ?
	END 
WHERE
	`id` IN (?,?,?)

插入或更新数据

仅 MySQL 支持，且需要设置主键或唯一键，对应 SQL 的 PRIMARY KEY 或 UNIQUE 语句

主键可以简单理解为不允许出现 NULL 值的唯一键

在单列定义主键：ALTER TABLE ... ADD PRIMARY KEY (...)
在多列定义主键：ALTER TABLE ... ADD PRIMARY KEY (..., ...)
在单列定义唯一键：ALTER TABLE ... ADD CONSTRAINT ... UNIQUE (...)
在多列定义唯一键：ALTER TABLE ... ADD CONSTRAINT ... UNIQUE (..., ...)

其后，插入或更新数据对应 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 实现是几乎一样的，此处就不再赘述了

下面将 id 作为主键作为示例

<insert id="insertOrUpdateData" parameterType="Data">
    INSERT INTO `data_table` (`id`,`field1`,`field2`)
    VALUES (#{id},#{attr1},#{attr2})
    ON DUPLICATE KEY UPDATE
    `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
    `field1` = VALUES(`field1`),
    `field2` = VALUES(`field2`)
</insert>

对应生成的 SQL 语句

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

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

删除数据

对应 SQL 的 DELETE 语句

<delete id="deleteData" parameterType="String">
    DELETE FROM `data_table` WHERE `id` = #{id}
</delete>

<delete id="batchDeleteData" parameterType="java.util.List">
    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`

简易实现

MyBatis-Plus 相对于 MyBatis 来说提供了一些更高层次的封装和功能，原先对于单个表的大量基础的增删改查操作直接调用 MyBatis-Plus 里的接口就行（包括本文基于 MyBatis 实现的依次、批量、分页增删改查），连 SQL 语句都可以不用写，通用于不同的关系型数据库，对于个人小型简易的项目而言非常方便，极大提升了开发效率。

但从另一个角度，MyBatis-Plus 的耦合度更高了，持久化层（Mapper 层及对应的 XML 映射文件）会入侵到业务逻辑层（Service 层），比如数据表和实体类耦合在一起等。此外，MyBatis-Plus 提供的是一些基础的增删改查接口，涉及到复杂的业务操作还是要像原先 MyBatis 一样老老实实写 SQL 语句，从长期来看选用哪个还是取决于个人需求。

💬相关

文档——MyBatis-Plus 的 CRUD 接口

https://baomidou.com/pages/49cc81/#service-crud-%E6%8E%A5%E5%8F%A3

梦未

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
基于MyBatis实现依次、批量、分页增删改查操作

我们知道处理数据有三种思路：依次、批量、分页，对应方法如下for注意，若用以;分隔多条 SQL 语句发给数据库（需要在配置里添加），这种处理虽然也可以算是某种程度上的“批量”，但其 SQL 语句并未在同一次提交中，因而本质上和依次处理没区别选取哪种方法取决于你的数据量（记录数 × 字段数，也就是行数 × 列数）大小当有一定的数据量后（千量级），依次处理非常慢，最好使用批量处理而数据量过于庞大时（十万量级）如果还采取
复制链接

扫一扫

专栏目录