mybatis批量添加和批量修改,批量删除
mybatis批量添加
<insert id="addBatch" parameterType="java.util.List">
INSERT INTO ip_message(ip,ip_content,ip_time)
VALUE
<foreach collection="list" item="item" separator=",">
(#{item.ip},#{item.ipContent},#{item.ipTime})
</foreach>
</insert>
int addBatch(List<QueryIpBean> queryIpBeanList);
mybatis批量修改
&allowMultiQueries=true
<update id="updateBatch" parameterType="java.util.List">
<foreach collection="list" item="item" separator=";">
UPDATE ip_message SET ip_content=#{item.ipContent} WHERE ip=#{item.ip}
</foreach>
</update>
int updateBatch(List<QueryIpBean> queryIpBeanList);
这里不要小瞧&allowMultiQueries=true
&allowMultiQueries=true 是 MySQL 数据库连接字符串的一部分，用于指示 MySQL 是否允许多个查询同时执行。如果不加这个参数，默认情况下 MySQL 是不允许一个 SQL 语句中包含多个查询的。 当你使用 MyBatis 批量更新或插入等操作时，可能会在一个 SQL 语句中包含多个操作，这时如果数据库连接字符串中没有 &allowMultiQueries=true，就会报错
mybatis批量删除
在使用in的过程中，使用int类型数据，会走索引，但是效率低
in使用varchar类型查询，会全表扫描
因此删除的时候，最好使用走索引的方式批量删除数据
<delete id="delBatch" parameterType="java.util.List">
DELETE FROM ip_message WHERE id IN
<foreach collection="list" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</delete>
int delBatch(List<Integer> idLists);
以下是完整代码
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.1.RELEASE</version>
<relativePath/>
</parent>
<groupId>com.mysql</groupId>
<artifactId>mysql-service</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
<!--lombok依赖-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
</project>
server.port=3307 logging.level.com.java=debug logging.level.web=debug spring.devtools.add-properties=false spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.datasource.url=jdbc:mysql://127.0.0.1:3306/yourheart-dev?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true spring.datasource.username=root spring.datasource.password=root mybatis.mapper-locations=classpath:mapping/*.xml
package com.java;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
* @author yourheart
* @Description
* @create 2022-05-14 23:53
*/
@SpringBootApplication
@MapperScan(basePackages = "com.java.mapper")
public class MysqlApplication {
public static void main(String[] args) {
SpringApplication.run(MysqlApplication.class, args);
}
}
package com.java.bean;
import lombok.Builder;
import lombok.Data;
/**
* @author yourheart
* @Description
* @create 2021-11-23 22:03
*/
@Data
@Builder
public class QueryIpBean {
/**
* 主键
*/
private String id;
/**
* ip地址
*/
private String ip;
/**
* ip地址的详细信息
*/
private String ipContent;
/**
* ip信息存储时间
*/
private String ipTime;
}
package com.java.utils;
import lombok.extern.slf4j.Slf4j;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* 作者 yourheart
* 时间: 2020/5/27 14:35
* 描述
*/
@Slf4j
public class TimeUtils {
//获取当前时间
public static String getTime() {
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String format = sdf.format(date);
return format;
}
/**
* 获取哪一天
* @return
*/
public static String getDay() {
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String format = sdf.format(date);
return format;
}
/**
* 获取哪一年
* @return
*/
public static String getYear() {
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
String format = sdf.format(date);
return format;
}
}
package com.java.mapper;
import com.java.bean.QueryIpBean;
import java.util.List;
public interface QueryIpMapper {
/**
* 查询ip表的所有数据
* @return
*/
List<QueryIpBean> getIpMessageList();
/**
* 批量插入数据
* @param queryIpBeanList
* @return
*/
int addBatch(List<QueryIpBean> queryIpBeanList);
int addBatchs(List<QueryIpBean> queryIpBeanList);
/**
* 单条数据插入
* @param queryIpBean
* @return
*/
int addIpMessage(QueryIpBean queryIpBean);
/**
* 批量修改数据
* @param queryIpBeanList
* @return
*/
int updateBatch(List<QueryIpBean> queryIpBeanList);
/**
* 批量删除数据
* @param idLists
* @return
*/
int delBatch(List<Integer> idLists);
}
<?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.java.mapper.QueryIpMapper">
<select id="getIpMessageList" resultType="com.java.bean.QueryIpBean">
SELECT * FROM ip_message ORDER BY ip_time DESC
</select>
<insert id="addBatch" parameterType="java.util.List">
INSERT INTO ip_message(ip,ip_content,ip_time)
VALUE
<foreach collection="list" item="item" separator=",">
(#{item.ip},#{item.ipContent},#{item.ipTime})
</foreach>
</insert>
<insert id="addBatchs" parameterType="java.util.List">
INSERT INTO ip_message(ip,ip_content,ip_time)
VALUES
<foreach collection="list" item="item" separator=",">
(#{item.ip},#{item.ipContent},#{item.ipTime})
</foreach>
</insert>
<insert id="addIpMessage" parameterType="com.java.bean.QueryIpBean">
INSERT INTO ip_message(ip,ip_content,ip_time)
VALUES(#{ip},#{ipContent},#{ipTime})
</insert>
<update id="updateBatch" parameterType="java.util.List">
<foreach collection="list" item="item" separator=";">
UPDATE ip_message SET ip_content=#{item.ipContent} WHERE ip=#{item.ip}
</foreach>
</update>
<delete id="delBatch" parameterType="java.util.List">
DELETE FROM ip_message WHERE id IN
<foreach collection="list" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</delete>
</mapper>
package com.java;
import com.java.bean.QueryIpBean;
import com.java.mapper.QueryIpMapper;
import com.java.utils.TimeUtils;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.ArrayList;
import java.util.List;
/**
* @author yourheart
* @Description
* @create 2022-05-15 0:21
*/
@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class MysqlApplicationTests{
@Autowired
private QueryIpMapper queryIpMapper;
@Test
public void delBatch(){
List<Integer> idLists=new ArrayList<>();
idLists.add(26);
idLists.add(25);
idLists.add(24);
queryIpMapper.delBatch(idLists);
}
public void upBatch(){
List<QueryIpBean> ipMessageList=new ArrayList<>();
ipMessageList.add(QueryIpBean.builder().ip("192.168.1.1").ipContent("mybatis批量修改测试003").ipTime(TimeUtils.getTime()).build());
ipMessageList.add(QueryIpBean.builder().ip("127.0.0.1").ipContent("mybatis批量修改测试004").ipTime(TimeUtils.getTime()).build());
queryIpMapper.updateBatch(ipMessageList);
}
public void batchTestAdd(){
List<QueryIpBean> ipMessageList=new ArrayList<>();
//100万条数据
for (int i=0;i<1000000;i++){
ipMessageList.add(QueryIpBean.builder().ip("192.168.1.1").ipContent("出去浪002").ipTime(TimeUtils.getTime()).build());
}
long startTime = System.currentTimeMillis();
queryIpMapper.addBatch(ipMessageList);
long endTime=System.currentTimeMillis();
log.info("【100万条数据】【value批处理耗时】:{}",(endTime-startTime)+"ms");
long startTime1 = System.currentTimeMillis();
queryIpMapper.addBatchs(ipMessageList);
long endTime1=System.currentTimeMillis();
log.info("【100万条数据】【values批处理耗时】:{}",(endTime1-startTime1)+"ms");
}
public void addIpMessage(){
List<QueryIpBean> ipMessageList = null;
ipMessageList.forEach(a->{
a.setIpTime(TimeUtils.getTime());
});
long startTime = System.currentTimeMillis();
ipMessageList.forEach(a->{
queryIpMapper.addIpMessage(a);
});
long endTime=System.currentTimeMillis();
log.info("【values批处理耗时】:{}",(endTime-startTime)+"ms");
}
public void addBatch(){
List<QueryIpBean> ipMessageList = null;
ipMessageList.forEach(a->{
a.setIpTime(TimeUtils.getTime());
});
long startTime = System.currentTimeMillis();
queryIpMapper.addBatch(ipMessageList);
long endTime=System.currentTimeMillis();
log.info("【values批处理耗时】:{}",(endTime-startTime)+"ms");
}
public void testLIst(){
List<QueryIpBean> ipMessageList = queryIpMapper.getIpMessageList();
ipMessageList.forEach(a->{
log.info(a.toString());
});
}
}
浙公网安备 33010602011771号