MyBatis批量查詢、插入、更新、刪除的實現(xiàn)示例

更新時間：2023年05月15日 15:20:52 作者：zhangbeizhen18

由于需要處理短時間內(nèi)大量數(shù)據(jù)入庫的問題，想到了Mybatis的批量操作，本文主要介紹了MyBatis批量查詢、插入、更新、刪除的實現(xiàn)示例，感興趣的可以了解一下

場景：使用MyBatis批量查詢(select)、批量插入(insert)、批量更新(update)、批量刪除(delete)操作MySQL數(shù)據(jù)庫。

版本：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.批量更新

示例使用批量更新時，數(shù)據(jù)源請求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='杭州是一個發(fā)達城市'
    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.批量查詢結(jié)果集: List<CityPO>
    List<CityPO> list01 = cityBatchMapper.queryCity1_1(paraList);
    // 2.批量插入?yún)?shù)集: List<CityPO>
    cityBatchMapper.insertCity1_2(list01);
    // 3.批量更新參數(shù)集: List<CityPO>
    cityBatchMapper.updateCity1_3(list01);
    // 4.批量刪除: List<CityPO>
cityBatchMapper.deleteCity1_4(list01);
log.info("測試結(jié)束...");
    return "執(zhí)行成功";
  }
  @GetMapping("/load02")
  public Object load02() {
    log.info("測試開始...");
    List<Long> paraList =  Arrays.asList(1L,2L);
    // 1.批量查詢結(jié)果集: List<Map<String, Object>>
    List<Map<String, Object>> list02=cityBatchMapper.queryCity2_1(paraList);
    // 2.批量插入?yún)?shù)集: List<Map<String, Object>>
    cityBatchMapper.insertCity2_2(list02);
    AtomicInteger add= new AtomicInteger();
    list02.forEach((map -> {
        map.put("cityDescribe","杭州是一個互聯(lián)網(wǎng)城市"+"_"+add.get());
        add.getAndIncrement();
    }));
    // 3.批量更新參數(shù)集: List<Map<String, Object>>
    cityBatchMapper.updateCity2_3(list02);
    // 4.批量刪除: List<Map<String, Object>>
    cityBatchMapper.deleteCity2_4(list02);
    log.info("測試結(jié)束...");
    return "執(zhí)行成功";
  }
}

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.基礎(chǔ)支撐

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 '生產(chǎn)總值',
  CITY_DESCRIBE VARCHAR(512) COLLATE utf8_bin DEFAULT NULL COMMENT '城市描述',
  DATA_YEAR VARCHAR(16) COLLATE utf8_bin DEFAULT NULL COMMENT '數(shù)據(jù)年份',
  UPDATE_TIME DATETIME DEFAULT NULL COMMENT '更新時間'
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='城市信息表';

6.3引入MyBatis依賴

使用mybatis-spring-boot-starter方式引入mybatis，對應(yīng)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

到此這篇關(guān)于MyBatis批量查詢、插入、更新、刪除的實現(xiàn)示例的文章就介紹到這了,更多相關(guān)MyBatis批量查詢插入更新刪除內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

MyBatis批量查詢、插入、更新、刪除的實現(xiàn)示例

目錄

1.批量查詢

1.1在CityBatchMapper中接口代碼

1.2 在CityBatchMapper.xml中SQL代碼

2.批量插入

2.1在CityBatchMapper中接口代碼

2.2 在CityBatchMapper.xml中SQL代碼

3.批量更新

3.1在CityBatchMapper中接口代碼

3.2 在CityBatchMapper.xml中SQL代碼

4.批量刪除

4.1在CityBatchMapper中接口代碼

4.2 在CityBatchMapper.xml中SQL代碼

5.測試代碼

5.1代碼

5.2測試請求

6.基礎(chǔ)支撐

6.1實體對象

6.2建表語句

6.3引入MyBatis依賴

6.4application.yml配置

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

MyBatis批量查詢、插入、更新、刪除的實現(xiàn)示例

目錄

1.批量查詢

1.1在CityBatchMapper中接口代碼

1.2 在CityBatchMapper.xml中SQL代碼

2.批量插入

2.1在CityBatchMapper中接口代碼

2.2 在CityBatchMapper.xml中SQL代碼

3.批量更新

3.1在CityBatchMapper中接口代碼

3.2 在CityBatchMapper.xml中SQL代碼

4.批量刪除

4.1在CityBatchMapper中接口代碼

4.2 在CityBatchMapper.xml中SQL代碼

5.測試代碼

5.1代碼

5.2測試請求

6.基礎(chǔ)支撐

6.1實體對象

6.2建表語句

6.3引入MyBatis依賴

6.4application.yml配置

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

MyBatis批量查詢、插入、更新、刪除的實現(xiàn)示例