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批量查询、插入、更新、删除的实现示例的文章就介绍到这了,更多相关MyBatis批量查询插入更新删除内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！

文章详情

MyBatis批量查询、插入、更新、删除的实现示例

目录

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.基础支撑

6.1实体对象

6.2建表语句

6.3引入MyBatis依赖

6.4application.yml配置

软考中级精品资料免费领

相关文章

猜你喜欢

MyBatis批量查询、插入、更新、删除的实现示例

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

Mybatis Plus 实现批量插入的示例代码

sql查询语句教程之插入、更新和删除数据实例

【mybatis】mapper.xml中foreach的用法，含批量查询、插入、修改、删除方法的使用

spring data jpa开启批量插入、批量更新的示例分析

Oracle中批量查询、删除、更新使用BULK COLLECT提高效率的示例分析

基于Java 利用Mybatis实现oracle批量插入及分页查询

基于Java怎么用Mybatis实现oracle批量插入及分页查询

深入解析Golang中链表的插入、删除、更新和查询操作

如何在MyBatis中实现数据的批量新增和删除

Springboot 手动分页查询分批批量插入数据的实现流程

利用MySqlBulkLoader实现批量插入数据的示例详解

Python批量删除或移动指定图像的实现示例

MyBatis-Plus使用条件构造器实现不同条件的查询，更新删除操作

mysql实现查询数据并根据条件更新到另一张表的方法示例