MyBatis批量查询、插入、更新、删除的实现示例
作者：zhangbeizhen18 发布时间：2021-07-21 22:33:16
标签：MyBatis,批量,查询,插入,更新,删除
场景：使用MyBatis批量查询(select)、批量插入(insert)、批量更新(update)、批量删除(delete)操作MySQL数据库。
版本：JDK 1.8,Spring Boot 2.6.3,mybatis-3.5.9,MySQL-5.7.33。
1.批量查询
提供两种方式。
方式一，返回值： List<CityPO>。
方式二，返回值： List<Map<String, Object>>。
1.1在CityBatchMapper中接口代码
@Repository
public interface CityBatchMapper {
// 1.1批量查询
List<CityPO> queryCity1_1(List<Long> paraList);
// 2.1批量查询
}
1.2 在CityBatchMapper.xml中SQL代码
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hub.example.mapper.CityBatchMapper">
<!--1.1批量查询-->
<select id="queryCity1_1" parameterType="java.util.ArrayList" resultType="com.hub.example.domain.CityPO">
select CITY_ID AS "cityId",
CITY_NAME AS "cityName",
LAND_AREA AS "landArea",
POPULATION AS "population",
GROSS AS "gross",
CITY_DESCRIBE AS "cityDescribe",
DATA_YEAR AS "dataYear",
UPDATE_TIME AS "updateTime"
from t_city
WHERE CITY_ID IN
<foreach collection="list" item="cityId" open="(" separator="," close=")">
#{cityId}
</foreach>
</select>
<!--2.1批量查询-->
<select id="queryCity2_1" resultType="java.util.Map">
select CITY_ID AS "cityId",
CITY_NAME AS "cityName",
LAND_AREA AS "landArea",
POPULATION AS "population",
GROSS AS "gross",
CITY_DESCRIBE AS "cityDescribe",
DATA_YEAR AS "dataYear",
UPDATE_TIME AS "updateTime"
from t_city
WHERE CITY_ID IN
<foreach collection="list" item="cityId" open="(" separator="," close=")">
#{cityId}
</foreach>
</select>
</mapper>
2.批量插入
2.1在CityBatchMapper中接口代码
@Repository
public interface CityBatchMapper {
// 1.2批量插入
int insertCity1_2(List<CityPO> cityList);
// 2.2批量插入
int insertCity2_2(List<Map<String, Object>> cityList);
}
2.2 在CityBatchMapper.xml中SQL代码
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hub.example.mapper.CityBatchMapper">
<!--1.2批量插入-->
<insert id="insertCity1_2" parameterType="java.util.ArrayList">
insert into t_city_01 (CITY_ID,
CITY_NAME,
LAND_AREA,
POPULATION,
GROSS,
CITY_DESCRIBE,
DATA_YEAR,
UPDATE_TIME)
values
<foreach collection="list" item="cityPO" open="" separator="," close="">
(#{cityPO.cityId},
#{cityPO.cityName},
#{cityPO.landArea},
#{cityPO.population},
#{cityPO.gross},
#{cityPO.cityDescribe},
#{cityPO.dataYear},
#{cityPO.updateTime})
</foreach>
</insert>
<!--2.2批量插入-->
<insert id="insertCity2_2" parameterType="java.util.List">
insert into t_city_01 (CITY_ID,
CITY_NAME,
LAND_AREA,
POPULATION,
GROSS,
CITY_DESCRIBE,
DATA_YEAR,
UPDATE_TIME)
values
<foreach collection="list" item="cityPO" open="" separator="," close="">
(#{cityPO.cityId},
#{cityPO.cityName},
#{cityPO.landArea},
#{cityPO.population},
#{cityPO.gross},
#{cityPO.cityDescribe},
#{cityPO.dataYear},
#{cityPO.updateTime})
</foreach>
</insert>
</mapper>
3.批量更新
示例使用批量更新时，数据源请求URL需添加配置：allowMultiQueries=true。
3.1在CityBatchMapper中接口代码
@Repository
public interface CityBatchMapper {
// 1.3批量更新
int updateCity1_3(List<CityPO> cityList);
// 2.3批量更新
int updateCity2_3(List<Map<String, Object>> cityList);
}
3.2 在CityBatchMapper.xml中SQL代码
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hub.example.mapper.CityBatchMapper">
<!--1.3批量更新-->
<update id="updateCity1_3" parameterType="java.util.List">
update
t_city_01
set CITY_DESCRIBE='杭州是一个发达城市'
WHERE CITY_ID IN
<foreach collection="list" item="cityPO" open="(" separator="," close=")">
#{cityPO.cityId}
</foreach>
</update>
<!--2.3批量更新-->
<update id="updateCity2_3" parameterType="java.util.List">
<foreach collection="list" item="cityPO" open="" separator=";" close="">
update
t_city_01
set CITY_DESCRIBE = #{cityPO.cityDescribe}
where CITY_ID=#{cityPO.cityId}
</foreach>
</update>
</mapper>
4.批量删除
4.1在CityBatchMapper中接口代码
@Repository
public interface CityBatchMapper {
// 1.4批量删除
int deleteCity1_4(List<CityPO> cityList);
// 2.4批量删除
int deleteCity2_4(List<Map<String, Object>> cityList);
}
4.2 在CityBatchMapper.xml中SQL代码
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hub.example.mapper.CityBatchMapper">
<!--1.4批量删除-->
<delete id="deleteCity1_4" parameterType="java.util.List">
delete from t_city_01 where 1=1
<if test="list.size()>0">
AND CITY_ID IN
<foreach collection="list" item="cityPO" open="(" separator="," close=")">
#{cityPO.cityId}
</foreach>
</if>
</delete>
<!--2.4批量删除-->
<delete id="deleteCity2_4" parameterType="java.util.List">
delete from t_city_01 where 1=1
<if test="list.size()>0">
AND CITY_ID IN
<foreach collection="list" item="cityPO" open="(" separator="," close=")">
#{cityPO.cityId}
</foreach>
</if>
</delete>
</mapper>
5.测试代码
5.1代码
@Slf4j
@RestController
@RequestMapping("/hub/example/cityBatch")
public class CityBatchController {
@Autowired
private CityBatchMapper cityBatchMapper;
@GetMapping("/load01")
public Object load01() {
log.info("测试开始...");
List<Long> paraList = Arrays.asList(1L,2L,3L);
// 1.批量查询结果集: List<CityPO>
List<CityPO> list01 = cityBatchMapper.queryCity1_1(paraList);
// 2.批量插入参数集: List<CityPO>
cityBatchMapper.insertCity1_2(list01);
// 3.批量更新参数集: List<CityPO>
cityBatchMapper.updateCity1_3(list01);
// 4.批量删除: List<CityPO>
cityBatchMapper.deleteCity1_4(list01);
log.info("测试结束...");
return "执行成功";
}
@GetMapping("/load02")
public Object load02() {
log.info("测试开始...");
List<Long> paraList = Arrays.asList(1L,2L);
// 1.批量查询结果集: List<Map<String, Object>>
List<Map<String, Object>> list02=cityBatchMapper.queryCity2_1(paraList);
// 2.批量插入参数集: List<Map<String, Object>>
cityBatchMapper.insertCity2_2(list02);
AtomicInteger add= new AtomicInteger();
list02.forEach((map -> {
map.put("cityDescribe","杭州是一个互联网城市"+"_"+add.get());
add.getAndIncrement();
}));
// 3.批量更新参数集: List<Map<String, Object>>
cityBatchMapper.updateCity2_3(list02);
// 4.批量删除: List<Map<String, Object>>
cityBatchMapper.deleteCity2_4(list02);
log.info("测试结束...");
return "执行成功";
}
}
5.2测试请求
URL01: http://127.0.0.1:18080/hub-example/hub/example/cityBatch/load01
URL02: http://127.0.0.1:18080/hub-example/hub/example/cityBatch/load02
6.基础支撑
6.1实体对象
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class CityPO implements Serializable {
private Long cityId;
private String cityName;
private Double landArea;
private Long population;
private Double gross;
private String cityDescribe;
private String dataYear;
private Date updateTime;
}
6.2建表语句
CREATE TABLE t_city (
CITY_ID BIGINT(16) NOT NULL COMMENT '唯一标识',
CITY_NAME VARCHAR(64) COLLATE utf8_bin NOT NULL COMMENT '城市名',
LAND_AREA DOUBLE DEFAULT NULL COMMENT '城市面积',
POPULATION BIGINT(16) DEFAULT NULL COMMENT '城市人口',
GROSS DOUBLE DEFAULT NULL COMMENT '生产总值',
CITY_DESCRIBE VARCHAR(512) COLLATE utf8_bin DEFAULT NULL COMMENT '城市描述',
DATA_YEAR VARCHAR(16) COLLATE utf8_bin DEFAULT NULL COMMENT '数据年份',
UPDATE_TIME DATETIME DEFAULT NULL COMMENT '更新时间'
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='城市信息表';
6.3引入MyBatis依赖
使用mybatis-spring-boot-starter方式引入mybatis，对应mybatis-3.5.9和mybatis-spring-2.0.7核心依赖。
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.2</version>
</dependency>
6.4application.yml配置
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/hub_a_db?allowMultiQueries=true
username: hub_a
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
jackson:
time-zone: GMT+8
mybatis:
mapper-locations: classpath*:mapper/*.xml
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
来源：https://blog.csdn.net/zhangbeizhen18/article/details/130209543
0
投稿猜你喜欢
- PC上的浏览器会弹出证书错误的对话框，提示你是否要无视错误继续浏览。实际上在WebView里也可以这样做，以实现加载证书有问题的页面。Web
- 因为一直用spring整合了mybatis，所以很少用到mybatis的session缓存。 习惯是本地缓存自己用map写或者引入第三方的本
- 使用Javamail发送邮件，必需的jar包（请下载javamail的源文件，官方下载页：http://www.oracle.com/tec
- Java中Collections.sort()的使用在日常开发中，很多时候都需要对一些数据进行排序的操作。然而那些数据一般都是放在一个集合中
本文实例为大家分享了opencv利用视频的前n帧求平均图像的具体代码，供大家参考，具体内容如下自己写的哈，可以用该小程序对视频求解平均模型。- 1.RecycledPool的重用场景以及使用：多个RecyclerView出现，并且他们的item布局结构一致，这时候可以进行重用。在进行
- Command 常用属性CommText 要下达至数据源的命令CommanTimeout 出错等待时间Command 三种方法Execute
- 本人是从事互联网金融行业的，所以会接触到一些金融类的问题，常见的一种就是数字转汉字大小写的问题。所以抽空就写了一个小小的工具类，实现了数字转
- 本文实例讲述了Java操作Mongodb数据库实现数据的增删查改功能。分享给大家供大家参考，具体如下：首先，我们在windows下安装mon
- 本文实例为大家分享了SpringMVC框架实现图片上传与下载的具体代码，供大家参考，具体内容如下1、新建一个Maven webapp项目，引
- 本文实例为大家解析了Zxing生成二维码的经典案例，供大家参考，具体内容如下1、首先呢，先编译 compile ‘com.google.zx
- 在上篇文章给大家介绍了Spring boot + mybatis + Vue.js + ElementUI 实现数据的增删改查实例代码(一)
- 消息队列1.消息队列的原理1.1 msgqueue采用链表来实现消息队列， 该链表是由系统内核维护，1.2 系统中可能有很多的msgqueu
- 1、项目引用System.Management库文件2、创建HardwareHandler.cs类文件namespace HardInfoT
- 我们先来看本地如何生成图片验证码的，再来写输出到网页的验证码如何实现。先来看最简单的—实现的功能是，将一个字符串变成图片写入到文件中实现代码
- 前言MyBatis是一个支持普通SQL查询，存储过程和高级映射的优秀持久层框架。MyBatis消除了几乎所有的JDBC代码和参数的手工设置以
- 机器跑了一晚上，发现有崩溃现象，由于页面内有动态绘图功能，我怀疑是绘图原因，但是今天上午有人提醒我才想到，是不是间隔调用时DWR产生了内存泄
- JDK集合源码之HashMap解析1.树结构入门1.1 什么是树？树（tree）是一种抽象数据类型（ADT），用来模拟具有树状结构性质的数据
- 本文实例讲述了C#实现缩放字体的方法。分享给大家供大家参考。具体实现方法如下：using System;using System.Colle
- @SpringBootApplication的使用1. Spring Boot 的核心1.1. 入口类和 @SpringBootApplic
