Mybatis-批量增加/更新/删除
文章目录
一. 前置准备
1.1 依赖
<!--2.2版本以上为junit5-->
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.5</version>
</parent>
<dependencies>
<!-- spring web -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.4.5</version>
</dependency>
<!-- 测试相关 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<version>2.4.5</version>
</dependency>
<!-- mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
<scope>provided</scope>
</dependency>
</dependencies>
1.2 数据库准备
表名:table
设置id主键自增
id | name |
---|---|
主键 | 姓名 |
1.3 Springboot及Mybatis框架搭建
- 包结构
-
yml
spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver #数据源配置 url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=GMT%2B8&characterEncoding=utf-seSSL=false username: root password: root mybatis: mapper-locations: classpath:mybatis/*.xml type-aliases-package: com.zhanghp.entity
1.4 logback
<?xml version="1.0" encoding="UTF-8" ?>
<configuration>
<!--定义日志文件的存储地址 勿在 LogBack 的配置中使用相对路径-->
<property name="LOG_HOME" value="${catalina.base}/logs/" />
<!-- 控制台输出 -->
<appender name="Stdout" class="ch.qos.logback.core.ConsoleAppender">
<!-- 日志输出格式 -->
<layout class="ch.qos.logback.classic.PatternLayout">
<!--格式化输出:%d表示日期,%thread表示线程名,%-5level:级别从左显示5个字符宽度%msg:日志消息,%n是换行符-->
<pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n
</pattern>
</layout>
</appender>
<!-- 按照每天生成日志文件 -->
<appender name="RollingFile" class="ch.qos.logback.core.rolling.RollingFileAppender">
<rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
<!--日志文件输出的文件名-->
<FileNamePattern>${LOG_HOME}/server.%d{yyyy-MM-dd}.log</FileNamePattern>
<MaxHistory>30</MaxHistory>
</rollingPolicy>
<layout class="ch.qos.logback.classic.PatternLayout">
<!--格式化输出:%d表示日期,%thread表示线程名,%-5level:级别从左显示5个字符宽度%msg:日志消息,%n是换行符-->
<pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n
</pattern>
</layout>
<!--日志文件最大的大小-->
<triggeringPolicy class="ch.qos.logback.core.rolling.SizeBasedTriggeringPolicy">
<MaxFileSize>10MB</MaxFileSize>
</triggeringPolicy>
</appender>
<!-- 日志输出级别 -->
<root level="info">
<appender-ref ref="Stdout" />
<!-- <appender-ref ref="RollingFile" />-->
</root>
<!--指定包名-->
<logger name="com.zhanghp.dao" level="DEBUG"></logger>
<!--日志异步到数据库 -->
<!--<appender name="DB" class="ch.qos.logback.classic.db.DBAppender">
日志异步到数据库
<connectionSource class="ch.qos.logback.core.db.DriverManagerConnectionSource">
连接池
<dataSource class="com.mchange.v2.c3p0.ComboPooledDataSource">
<driverClass>com.mysql.jdbc.Driver</driverClass>
<url>jdbc:mysql://127.0.0.1:3306/databaseName</url>
<user>root</user>
<password>root</password>
</dataSource>
</connectionSource>
</appender> -->
</configuration>
1.5 前置知识
- mybatis的动态sql中的foreach参数
- collection="" 遍历的集合或者是数组,参数是数组,collection中名字指定为array,参数是List集合,collection中名字指定为list
参数是Map集合,collection名字指定为map
- separator="" 多个元素取出的时候 用什么文字分隔
- open="" 以什么开头
- close="" 以什么结尾
- item="" 中间变量名
- index="" 集合中元素迭代时的索引
二. 批量插入
2.1 sql语句
insert into person (id, name) values (1,'张三'), (2,'李四')
2.2 代码实现
- mapper层
@Mapper
public interface PersonMapper {
int insertBatch(List<Person> list);
int insertBatch2(List<String> list);
int insertBatchForMap(@Param("map")Map<Integer, String> map);
}
- xml
<?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.zhanghp.dao.PersonMapper">
<insert id="insertBatch">
insert into person (id, name ) values
<foreach collection="list" separator="," item="it">
(#{it.id},#{it.name})
</foreach>
</insert>
<insert id="insertBatch2">
insert into person (name) values
<foreach item = "it" collection="list" separator="," >
<!--加括号-->
(#{it})
</foreach>
</insert>
<insert id="insertBatchForMap">
insert into person (id,name) values
<foreach index="key" collection="map" separator="," item = "val">
(#{key},#{val})
</foreach>
</insert>
</mapper>
- junit测试
@SpringBootTest(classes = BatchApplication.class)
public class TestDataBase {
@Autowired
private PersonMapper testMapper;
@Test
public void test2(){
List<Person> list = new ArrayList<>();
list.add(new Person(null,"1"));
list.add(new Person(null,"2"));
int i = testMapper.insertBatch(list);
if (i>0) {
System.out.println("yeah");
}
}
@Test
public void test3(){
List<String> list = new ArrayList<>();
list.add("test1");
list.add("test2");
int i = testMapper.insertBatch2(list);
if (i>0) {
System.out.println("yeah");
}
}
@Test
public void insertMap(){
Map<Integer, String> map = new HashMap<>();
map.put(null,"map123");
map.put(null,"map222");
int i = testMapper.insertBatchForMap(map);
if (i>0) {
System.out.println("yeah");
}
}
}
三. 批量更新
3.1 sql语句
insert into person (id,name) values (1,'2'),(2,'3') on duplicate key update name=values(name);
3.2 代码实现
- mapper
int updateForNew(List<Person> personList);
- xml
<update id="updateForNew">
insert into person (id,name) values
<foreach collection="list" separator="," item="it">
(#{it.id},#{it.name})
</foreach>
on duplicate key update name=values(name);
</update>
- junit
@Test
public void updateForNew(){
List<Person> list = new ArrayList<>();
list.add(new Person(1,"1"));
list.add(new Person(2,"2"));
int i = testMapper.updateForNew(list);
if (i>0) {
System.out.println("yeah");
}
}
四. 批量删除
4.1 sql语句
4.2 代码实现
- mapper
int deleteBatch(List<Integer> idList);
- xml
<delete id="deleteBatch">
delete from person where id in
<foreach collection="list" item="it" separator="," open="(" close=")">
#{it}
</foreach>
</delete>
- junit
@Test
public void deleteBatch(){
List<Integer> list = new ArrayList<>();
list.add(1);
list.add(2);
int i = testMapper.deleteBatch(list);
if (i>0) {
System.out.println("yeah");
}
}