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

@Repository public interface CityBatchMapper { // 1.1批量查询 List queryCity1_1(List paraList); // 2.1批量查询 }

   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  #{cityId}  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  #{cityId} 

@Repository public interface CityBatchMapper { // 1.2批量插入 int insertCity1_2(List cityList); // 2.2批量插入 int insertCity2_2(List> cityList); }

   insert into t_city_01 (CITY_ID, CITY_NAME, LAND_AREA, POPULATION, GROSS, CITY_DESCRIBE, DATA_YEAR, UPDATE_TIME) values  (#{cityPO.cityId}, #{cityPO.cityName}, #{cityPO.landArea}, #{cityPO.population}, #{cityPO.gross}, #{cityPO.cityDescribe}, #{cityPO.dataYear}, #{cityPO.updateTime})  insert into t_city_01 (CITY_ID, CITY_NAME, LAND_AREA, POPULATION, GROSS, CITY_DESCRIBE, DATA_YEAR, UPDATE_TIME) values  (#{cityPO.cityId}, #{cityPO.cityName}, #{cityPO.landArea}, #{cityPO.population}, #{cityPO.gross}, #{cityPO.cityDescribe}, #{cityPO.dataYear}, #{cityPO.updateTime}) 

@Repository public interface CityBatchMapper { // 1.3批量更新 int updateCity1_3(List cityList); // 2.3批量更新 int updateCity2_3(List> cityList); }

   update t_city_01 set CITY_DESCRIBE='杭州是一个发达城市' WHERE CITY_ID IN  #{cityPO.cityId}   update t_city_01 set CITY_DESCRIBE = #{cityPO.cityDescribe} where CITY_ID=#{cityPO.cityId} 

@Repository public interface CityBatchMapper { // 1.4批量删除 int deleteCity1_4(List cityList); // 2.4批量删除 int deleteCity2_4(List> cityList); }

   delete from t_city_01 where 1=1  AND CITY_ID IN  #{cityPO.cityId}  delete from t_city_01 where 1=1  AND CITY_ID IN  #{cityPO.cityId} 

@Slf4j @RestController @RequestMapping("/hub/example/cityBatch") public class CityBatchController { @Autowired private CityBatchMapper cityBatchMapper; @GetMapping("/load01") public Object load01() { log.info("测试开始..."); List paraList =  Arrays.asList(1L,2L,3L); // 1.批量查询结果集: List List list01 = cityBatchMapper.queryCity1_1(paraList); // 2.批量插入参数集: List cityBatchMapper.insertCity1_2(list01); // 3.批量更新参数集: List cityBatchMapper.updateCity1_3(list01); // 4.批量删除: List cityBatchMapper.deleteCity1_4(list01); log.info("测试结束..."); return "执行成功"; } @GetMapping("/load02") public Object load02() { log.info("测试开始..."); List paraList =  Arrays.asList(1L,2L); // 1.批量查询结果集: List> List> list02=cityBatchMapper.queryCity2_1(paraList); // 2.批量插入参数集: List> cityBatchMapper.insertCity2_2(list02); AtomicInteger add= new AtomicInteger(); list02.forEach((map -> { map.put("cityDescribe","杭州是一个互联网城市"+"_"+add.get()); add.getAndIncrement(); })); // 3.批量更新参数集: List> cityBatchMapper.updateCity2_3(list02); // 4.批量删除: List> cityBatchMapper.deleteCity2_4(list02); log.info("测试结束..."); return "执行成功"; } }

@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; }

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='城市信息表';

 org.mybatis.spring.bootmybatis-spring-boot-starter2.2.2

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