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