MyBatis批量查詢、插入、更新、刪除的實(shí)現(xiàn)示例
更新時(shí)間：2023年05月15日 15:20:52 作者：zhangbeizhen18
由于需要處理短時(shí)間內(nèi)大量數(shù)據(jù)入庫(kù)的問(wèn)題，想到了Mybatis的批量操作，本文主要介紹了MyBatis批量查詢、插入、更新、刪除的實(shí)現(xiàn)示例，感興趣的可以了解一下
場(chǎng)景：使用MyBatis批量查詢(select)、批量插入(insert)、批量更新(update)、批量刪除(delete)操作MySQL數(shù)據(jù)庫(kù)。
版本：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í)，數(shù)據(jù)源請(qǐng)求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='杭州是一個(gè)發(fā)達(dá)城市'
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.測(cè)試代碼
5.1代碼
@Slf4j
@RestController
@RequestMapping("/hub/example/cityBatch")
public class CityBatchController {
@Autowired
private CityBatchMapper cityBatchMapper;
@GetMapping("/load01")
public Object load01() {
log.info("測(cè)試開(kāi)始...");
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("測(cè)試結(jié)束...");
return "執(zhí)行成功";
}
@GetMapping("/load02")
public Object load02() {
log.info("測(cè)試開(kāi)始...");
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","杭州是一個(gè)互聯(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("測(cè)試結(jié)束...");
return "執(zhí)行成功";
}
}5.2測(cè)試請(qǐng)求
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實(shí)體對(duì)象
@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建表語(yǔ)句
CREATE TABLE t_city ( CITY_ID BIGINT(16) NOT NULL COMMENT '唯一標(biāo)識(shí)', 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 '更新時(shí)間' ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='城市信息表';
6.3引入MyBatis依賴
使用mybatis-spring-boot-starter方式引入mybatis，對(duì)應(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批量查詢、插入、更新、刪除的實(shí)現(xiàn)示例的文章就介紹到這了,更多相關(guān)MyBatis批量查詢插入更新刪除內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
springboot+camunda實(shí)現(xiàn)工作流的流程分析
Camunda是基于Java語(yǔ)言，支持BPMN標(biāo)準(zhǔn)的工作流和流程自動(dòng)化框架，并且還支持CMMN規(guī)范，DMN規(guī)范，本文給大家介紹springboot+camunda實(shí)現(xiàn)工作流的流程分析，感興趣的朋友一起看看吧2021-12-12
Quarkus中filter過(guò)濾器跨域cors問(wèn)題解決方案
這篇文章主要為大家介紹了Quarkus中filter過(guò)濾器跨域cors問(wèn)題的解決方案，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2022-02-02
SpringBoot無(wú)法訪問(wèn)/static下靜態(tài)資源的解決
這篇文章主要介紹了SpringBoot無(wú)法訪問(wèn)/static下靜態(tài)資源的解決方案，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2021-09-09
SpringMVC中ModelAndView的使用及說(shuō)明
這篇文章主要介紹了SpringMVC中ModelAndView的使用及說(shuō)明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-11-11
JMX最常見(jiàn)的場(chǎng)景是監(jiān)控Java程序的基本信息和運(yùn)行情況,本文主要介紹了JMX監(jiān)控的具體使用,具有一定的參考價(jià)值,感興趣的可以了解一下2024-02-02
spring boot tomcat jdbc pool的屬性綁定
這篇文章主要介紹了spring boot tomcat jdbc pool的屬性綁定的相關(guān)資料，非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友參考下2018-01-01
Spark是專為大規(guī)模數(shù)據(jù)處理而設(shè)計(jì)的，基于內(nèi)存快速通用,可擴(kuò)展的集群計(jì)算引擎，實(shí)現(xiàn)了高效的DAG執(zhí)行引擎,可以通過(guò)基于內(nèi)存來(lái)高效處理數(shù)據(jù)流，運(yùn)算速度相比于MapReduce得到了顯著的提高。2021-06-06
這篇文章主要介紹了Java對(duì)象的內(nèi)存布局，我們知道在Java中基本數(shù)據(jù)類型的大小，例如int類型占4個(gè)字節(jié)、long類型占8個(gè)字節(jié)，那么Integer對(duì)象和Long對(duì)象會(huì)占用多少內(nèi)存呢？本文介紹一下Java對(duì)象在堆中的內(nèi)存結(jié)構(gòu)以及對(duì)象大小的計(jì)算2023-02-02
idea?compile項(xiàng)目正常啟動(dòng)項(xiàng)目的時(shí)候build失敗報(bào)“找不到符號(hào)”等問(wèn)題及解決方案
這篇文章主要介紹了idea?compile項(xiàng)目正常，啟動(dòng)項(xiàng)目的時(shí)候build失敗，報(bào)“找不到符號(hào)”等問(wèn)題,這種問(wèn)題屬于lombok編譯失敗導(dǎo)致，可能原因是依賴jar包沒(méi)有更新到最新版本，需要的朋友可以參考下2023-10-10
Intellij IDEA 2019 最新亂碼問(wèn)題及解決必殺技(必看篇)
大家在使用Intellij IDEA 的時(shí)候會(huì)經(jīng)常遇到各種亂碼問(wèn)題，今天小編給大家分享一些關(guān)于Intellij IDEA 2019 最新亂碼問(wèn)題及解決必殺技，感興趣的朋友跟隨小編一起看看吧2020-04-04