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

📚专栏

「Java数据集成」专栏

基础支撑
辅助(可选)
进阶
基础支撑
基础支撑
辅助(可选)
辅助(可选)
辅助(可选)
测试(可选)
《请求和解析》
《依批分增删改查》
《生成代码脚本》
《增删改查模板》
《同异步请求和处理》
《集成模板》
《HTTP请求工具类》
《JSON处理工具类》
《XML处理工具类》
《生成随机数据脚本》

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

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

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

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

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

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

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

image-20230216101119466

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

💬相关

博客文章《mybatis之foreach用法》

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

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

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

以下给出一个示例场景

  • 建立数据表 data_table,含有字段 idfield1field2
  • 建立 Java 类 Data,含有属性 idattr1attr2
  • 将 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
    • useUnicodecharacterEncoding :编码方式,如 trueutf8
    • 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>等)中 parameterTyperesultType 属性里写上传递给 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 操作(如 INSERTUPDAREDELERE),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 KEYUNIQUE 语句

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

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

其后,插入或更新数据对应 MySQL 的REPLACE INTOINSERT INTO... ON DUPLICATE KEY UPDATE ... 的语句

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

如果表同时存在多个主键或唯一键,那就首先根据主键进行重复项检查。如果没有主键,则会根据第一个唯一键进行检查,然后是第二个唯一键,以此类推。

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

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

💬相关

文章《慎用mysql replace语句》

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

由于 REPLACE INTOINSERT 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
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值