使用MyBatis批量查询(select)、批量插入(insert)、批量更新(update)、批量删除(delete)

╰半橙微兮° 2024-04-23 03:16 104阅读 0赞

**记录**：412

**场景**：使用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">
      
      <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>
      
      <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">
      
      <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>
      
      <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代码**

**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代码**

**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

以上，感谢。

2023年4月17日