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()); }); } }