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>

1	`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>

1	`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>

100

101

102

103

104

105

106

107

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