MyBatis批量查询、插入、更新、删除如何实现

发布时间：2023-06-20 11:10:02 作者：栢白
来源：亿速云阅读：102

今天小编给大家分享的是MyBatis批量查询、插入、更新、删除如何实现，相信很多人都不太了解，为了让大家更加了解，所以给大家总结了以下内容，一起往下看吧。一定会有所收获的哦。

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

关于MyBatis批量查询、插入、更新、删除如何实现就分享到这里了，希望以上内容可以对大家有一定的参考价值，可以学以致用。如果喜欢本篇文章，不妨把它分享出去让更多的人看到。