今天小编给大家分享的是MyBatis批量查询、插入、更新、删除如何实现，相信很多人都不太了解，为了让大家更加了解，所以给大家总结了以下内容，一起往下看吧。一定会有所收获的哦。
1.批量查询
提供两种方式。
方式一，返回值： List<CityPO>。
方式二，返回值： List<Map<String, Object>>。
1.1在CityBatchMapper中接口代码
@Repositorypublic 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中接口代码
@Repositorypublic 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中接口代码
@Repositorypublic 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中接口代码
@Repositorypublic 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@AllArgsConstructorpublic 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+8mybatis: mapper-locations: classpath*:mapper/*.xml configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl关于MyBatis批量查询、插入、更新、删除如何实现就分享到这里了，希望以上内容可以对大家有一定的参考价值，可以学以致用。如果喜欢本篇文章，不妨把它分享出去让更多的人看到。
