精华内容
下载资源
问答
  • mybatis 批量插入
    2021-11-22 13:49:39

    今天在写mybatis批量加入时,发现插入时间太长了,才几千条数据就要接近一分钟才能完成,所以也去网上找了下解决方案

    一、一条一条插入

      <insert id="insert" parameterType="com.cntd.entity.Attack">
        insert into attack (id,score, container)
        values 
        ( #{id,jdbcType=INTEGER},
          #{score,jdbcType=TINYINT}, 
          #{container,jdbcType=LONGVARCHAR})
      </insert>
            //这种插入效率太慢了 每一次都要执行一次sql插入语句
            List<Attack> list = .....
            for(Attack attack: list){
                 attackMapper.insert(attack);
            }

    二、去网上查找了对应的解决方案

    大概就是xml使用 foreach 来达到一条插入语句插入n条内容。

    大概有两种情况

    一种解读之后发现也是一样一条一条插入那和我刚刚开始的情况是一样的,使用以下动态sql的情况

    <insert id="insertBatch" parameterType="List" useGeneratedKeys="true" keyProperty="id">
        <foreach collection="list" item="item" index="index" separator=";">
            insert into cntd_attack 
            <trim prefix="(" suffix=")" suffixOverrides=",">    
              <if test="score != null">
                score,
              </if>
              <if test="container != null">
                container,
              </if> values
            <trim prefix="values (" suffix=")" suffixOverrides=",">
               <if test="score != null">
                    #{score,jdbcType=TINYINT},
              </if>
              <if test="container != null">
                #{container,jdbcType=LONGVARCHAR},
              </if>
        </foreach>
      </insert>

    三、最终解决方案

     只使用foreach,这样就能达到我们一次性插入多条数据的情况了。

    但是一次性插入不要太多,mysql默认一次性最大插入1MB的数据,如果超过这个数据量将会报错, 最后我选择了一次性插入1000条数据,速度果然杠杠上升。

    <insert id="insertBatch" parameterType="List" useGeneratedKeys="true" keyProperty="id">
        insert into cntd_attack 
               (score, container) 
               values
        <foreach collection="list" item="item" index="index" separator=",">
            (
                #{item.score,jdbcType=TINYINT},
                #{item.container,jdbcType=LONGVARCHAR}
            )
        </foreach>
      </insert>

    更多相关内容
  • 本文给大家分享MyBatis批量插入(insert)数据操作知识,非常不错,具有参考借鉴价值,感兴趣的朋友一起学习吧
  • 主要介绍了Mybatis批量插入数据返回主键的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
  • 本篇文章主要介绍了详解MyBatis批量插入数据Mapper文件的写法,具有一定的参考价值,感兴趣的小伙伴们可以参考一下。
  • 本文通过实例代码给大家分享了MyBatis批量插入数据到Oracle数据库中的两种方式,非常不错,具有参考借鉴价值,需要的朋友参考下吧
  • 只提供代码,自己去下载相关jar包谢谢只提供代码,自己去下载相关jar包谢谢只提供代码,自己去下载相关jar包谢谢只提供代码,自己去下载相关jar包谢谢只提供代码,自己去下载相关jar包谢谢
  • 主要给大家介绍了关于Mybatis批量插入更新xml方式和注解方式的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Mybatis具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
  • Mybatis批量插入

    2021-11-25 12:27:48
    三种批量插入的方式 ... * Mybatis批量插入测试实体类 * * @Author: chenyang * @Date: 2021/11/25 12:33 */ public class Person { /** * 用户名 */ private String name; /** * 用户密码 */ .

    三种批量插入的方式

    • 循环插入
    • foreach标签
    • 批处理

    数据库和实体类

     

    package com.yang.model;
    
    /**
     * Mybatis批量插入测试实体类
     *
     * @Author: chenyang
     * @Date: 2021/11/25 12:33
     */
    public class Person {
    
        /**
         * 用户名
         */
        private String name;
        /**
         * 用户密码
         */
        private String password;
    
        // get set tostring
    }
    

    循环插入

    for循环调用dao层的单条插入方法

    insert into table ([列名],[列名])  values ([列值],[列值]));
    或:
    insert into table values ([列值],[列值]));
         <insert id="insertPerson" parameterType="person">
            INSERT INTO m_person(name, password, createtime)
            VALUES (#{name}, #{password}, now())
        </insert>
        @Test
        public void test1(){
            // 初始化数据
            ArrayList<Person> personList = new ArrayList<>();
            for (int i = 0; i < 1000; i++) {
                personList.add(new Person("N "+ i, "P "+i));
            }
    
            long start = System.currentTimeMillis();
    
            // 循环插入数据
            personList.forEach(personDao::insertPerson);
            
            //计算插入1000条数据所用时间
            System.out.println(System.currentTimeMillis() - start); // 144401
        }

    foreach

    原生批量插入方法是依靠 MyBatis 中的 foreach 标签,将数据拼接成一条原生的 insert 语句一次性执行的, 

    好处:可以避免程序和数据库建立多次连接,增加服务器负荷。

    insert into table  ([列名],[列名]) 
     VALUES
    ([列值],[列值]),
    ([列值],[列值]),
    ([列值],[列值]);
        <insert id="insertPersons" parameterType="person">
            INSERT INTO m_person(name, password, createtime) VALUES
            <foreach collection="list" item="item" separator=",">
                (#{item.name}, #{item.password}, now())
            </foreach>
        </insert>

    参数解释:

    foreach 的主要作用在构建 in 条件中,它可以在 sql 语句中进行迭代一个集合。foreach 元素的属性主要有 collection,item,separator,index,open,close。

    1. collection:指定要遍历的集合。表示传入过来的参数的数据类型。该属性是必须指定的,要做 foreach 的对象。在使用 foreach 的时候最关键的也是最容易出错的就是 collection 属性。在不同情况 下,该属性的值是不一样的,主要有一下 3 种情况: a. 如果传入的是单参数且参数类型是一个 List 的时候,collection 属性值为 list。 b. 如果传入的是单参数且参数类型是一个数组的时候,collection 的属性值为 array。 c. 如果传入的参数是多个的时候,就需要把它们封装成 Map,当然单参数也可以封装成 Map。Map 对象没有默认的键。

    2. item:表示集合中每一个元素进行迭代时的别名。将当前遍历出的元素赋值给指定的变量,然后用#{变量名},就能取出变量的值,也就是当前遍历出的元素。

    3. separator:表示在每次进行迭代之间以什么符号作为分隔符。select * from tab where id in(1,2,3)相当于1,2,3之间的","

    4. index:索引。index 指定一个名字,用于表示在迭代过程中,每次迭代到的位置。遍历 list 的时候 index 就是索引,遍历 map 的时候 index 表示的就是 map 的 key,item 就是 map 的值。

        @Test
        public void test2(){
            // 初始化数据
            ArrayList<Person> personList = new ArrayList<>();
            for (int i = 0; i < 1000; i++) {
                personList.add(new Person("N "+ i, "P "+i));
            }
    
            long start = System.currentTimeMillis();
    
            // 循环插入数据
            personDao.insertPersons(personList);
    
            //计算插入1000条数据所用时间
            System.out.println(System.currentTimeMillis() - start); // 861
        }

    可能出现的问题:

    • 无法获取批量插入的id
    • 拼接的SQL语句过大从而导致程序执行错误,因为默认情况下 MySQL 可以执行的最大 SQL(大小)为 4M。

    这就是原生批量插入方法的缺点,也是为什么 MP 需要分批执行的原因,就是为了防止程序在执行时,因为触发了数据库的最大执行 SQL 而导致程序执行报错。

    当然我们也可以通过设置 MySQL 的最大执行 SQL 来解决报错的问题,设置命令如下:

    -- 设置最大执行 SQL 为 10M
    set global max_allowed_packet=10*1024*1024;

    对于批量插入中间有一个失败会怎么样

    批量语句,只要有一个失败,就会全部失败。数据库会回滚全部数据。

    关于测试过程可以看这篇博客:mysql批量插入语句执行失败的话,是部分失败还是全部失败

    首先我们知道了mybatis <foreache>批量插入,是在程序内拼接sql 语句(拼接成多条同时插入的sql语句),拼接后发给数据库。

    就相当于咱们自己在mysql的命令行中,执行一条多插入的语句。默认情况下 mysql 单条语句是一个事务,这在一个事务范围内,当中间的sql语句有问题,或者有一个插入失败,就会触发事务回滚。同时你也能看到错误提示。(命令行执行单条sql的情况)

    所以有一个插入不成功肯定全部回滚。

    所以一般情况下我们推荐即使使用批量插入,也要分批次。

    每次批次设置多少?需要根据你的插入一条数据的参数量来做度量。因为受限条件是sql语句的长度。

    而且分批插入更加合理,对于插入失败,回滚范围会缩小很多。

    前面两者对比

    循环插入:需要每次都获取session,获取连接,然后将sql 语句发给mysql 去执行(JDBC一般情况下是通过TCP/IP 进行连接和数据库进行通信的)。可以看这里 mysql四种通信协议

    批量插入: 批量插入通过foreach 标签,将多条数据拼接在sql 语句后,一次执行只获取一次session,提交一条sql语句。减少了程序和数据库交互的准备时间

     

    批处理

    Mybatis内置的ExecutorType有3种,SIMPLE、REUSE、BATCH; 默认的是simple,该模式下它为每个语句的执行创建一个新的预处理语句,单条提交sql;而batch模式重复使用已经预处理的语句,并且批量执行所有更新语句,显然batch性能将更优;但batch模式也有自己的问题,比如在Insert操作时,在事务没有提交之前,是没有办法获取到自增的id,这在某型情形下是不符合业务要求的;

    我在测试一开始,发现改成 Mybatis Batch提交的方法都不起作用,实际上在插入的时候仍然是一条条记录的插,而且速度远不如原来 foreach 拼接SQL的方法,这是非常不科学的。

    后来才发现要批量执行的话,连接URL字符串中需要新增一个参数:rewriteBatchedStatements=true

    • rewriteBatchedStatements参数介绍

    MySqlJDBC连接的url中要加rewriteBatchedStatements参数,并保证5.1.13以上版本的驱动,才能实现高性能的批量插入。MySql JDBC驱动在默认情况下会无视executeBatch()语句,把我们期望批量执行的一组sql语句拆散,一条一条地发给MySql数据库,批量插入实际上是单条插入,直接造成较低的性能。只有把rewriteBatchedStatements参数置为true, 驱动才会帮你批量执行SQL。这个选项对INSERT/UPDATE/DELETE都有效。

        @Autowired
        private SqlSessionFactory sqlSessionFactory;
    
        @Test
        public void test3(){
            // 初始化数据
            ArrayList<Person> personList = new ArrayList<>();
            for (int i = 0; i < 1000; i++) {
                personList.add(new Person("N "+ i, "P "+i));
            }
    
            long start = System.currentTimeMillis();
    
            SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
            PersonDao mapper = sqlSession.getMapper(PersonDao.class);
    
            for (int i = 0; i < 1000; i++) {
                mapper.insertPerson(personList.get(i));
                if (i % 500 == 0){
                    // 每500条数据一起提交
                    sqlSession.commit();
                    // 清空缓存
                    sqlSession.clearCache();
                }
            }
            sqlSession.commit();
            sqlSession.clearCache();
            //计算插入1000条数据所用时间
            System.out.println(System.currentTimeMillis() - start); // 632
        }

    三者效率对比:批处理 > foreach >循环插入

    SqlSessionFactory

    SqlSessionFactory 有六个方法创建 SqlSession 实例。通常来说,当你选择其中一个方法时,你需要考虑以下几点:

    • 事务处理:你希望在 session 作用域中使用事务作用域,还是使用自动提交(auto-commit)?(对很多数据库和/或 JDBC 驱动来说,等同于关闭事务支持)

    • 数据库连接:你希望 MyBatis 帮你从已配置的数据源获取连接,还是使用自己提供的连接?

    • 语句执行:你希望 MyBatis 复用 PreparedStatement 和/或批量更新语句(包括插入语句和删除语句)吗?

    基于以上需求,有下列已重载的多个 openSession() 方法供使用。

     SqlSession openSession()
     SqlSession openSession(boolean autoCommit)
     SqlSession openSession(Connection connection)
     SqlSession openSession(TransactionIsolationLevel level)
     SqlSession openSession(ExecutorType execType, TransactionIsolationLevel level)
     SqlSession openSession(ExecutorType execType)
     SqlSession openSession(ExecutorType execType, boolean autoCommit)
     SqlSession openSession(ExecutorType execType, Connection connection)
     Configuration getConfiguration();

    展开全文
  • Java实现Mybatis将数据批量插入到Oracle中
  • Mybatis 批量插入

    千次阅读 2022-01-19 16:45:34
    本文我们主要讨论一下Mybatis批量插入操作。在这之前,我们还是得先了解insert, update 和 delete标签。 <insert id="insertAuthor" parameterType="domain.blog.Author" flushCache="true" statementType=...

    insert, update 和 delete

    前文我们说到了select标签,以及一些复杂查询的处理。本文我们主要讨论一下Mybatis的批量插入操作。在这之前,我们还是得先了解insert, update 和 delete标签。

    <insert
      id="insertAuthor"
      parameterType="domain.blog.Author"
      flushCache="true"
      statementType="PREPARED"
      keyProperty=""
      keyColumn=""
      useGeneratedKeys=""
      timeout="20">
    
    <update
      id="updateAuthor"
      parameterType="domain.blog.Author"
      flushCache="true"
      statementType="PREPARED"
      timeout="20">
    
    <delete
      id="deleteAuthor"
      parameterType="domain.blog.Author"
      flushCache="true"
      statementType="PREPARED"
      timeout="20">
    
    属性描述
    id在命名空间中唯一的标识符,可以被用来引用这条语句。
    parameterType将会传入这条语句的参数的类全限定名或别名。这个属性是可选的,因为 MyBatis 可以通过类型处理器(TypeHandler)推断出具体传入语句的参数,默认值为未设置(unset)。
    flushCache将其设置为 true 后,只要语句被调用,都会导致本地缓存和二级缓存被清空,默认值:(对 insert、update 和 delete 语句)true。
    timeout这个设置是在抛出异常之前,驱动程序等待数据库返回请求结果的秒数。默认值为未设置(unset)(依赖数据库驱动)。
    statementType可选 STATEMENT,PREPARED 或 CALLABLE。这会让 MyBatis 分别使用 Statement,PreparedStatement 或 CallableStatement,默认值:PREPARED。
    useGeneratedKeys(仅适用于 insert 和 update)这会令 MyBatis 使用 JDBC 的 getGeneratedKeys 方法来取出由数据库内部生成的主键(比如:像 MySQL 和 SQL Server 这样的关系型数据库管理系统的自动递增字段),默认值:false。
    keyProperty(仅适用于 insert 和 update)指定能够唯一识别对象的属性,MyBatis 会使用 getGeneratedKeys 的返回值或 insert 语句的 selectKey 子元素设置它的值,默认值:未设置(unset)。如果生成列不止一个,可以用逗号分隔多个属性名称。
    keyColumn(仅适用于 insert 和 update)设置生成键值在表中的列名,在某些数据库(像 PostgreSQL)中,当主键列不是表中的第一列的时候,是必须设置的。如果生成列不止一个,可以用逗号分隔多个属性名称。
    databaseId如果配置了数据库厂商标识(databaseIdProvider),MyBatis 会加载所有不带 databaseId 或匹配当前 databaseId 的语句;如果带和不带的语句都有,则不带的会被忽略。

    普通的修改和删除都非常简单,这里我们不再赘述。我们主要来讨论讨论插入操作。

    新建一张测试表t_my_emp

    在这里插入图片描述

    单条数据的插入

    <?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">
    <!-- namespace我们对应到了我们的EmpMapper接口 -->
    <mapper namespace="com.yyoo.mybatis.mapper.MyEmpMapper">
    
        <insert id="insert" parameterType="MyEmp">
            insert into t_my_emp(name,age,sex)
            values(#{name},#{age},#{sex})
        </insert>
    
    </mapper>
    
    public class Demo6 {
    
        public static void main(String[] args) throws IOException {
    
            String resouce = "mybatis-config.xml";
            InputStream in = Resources.getResourceAsStream(resouce);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
            
            SqlSession session = sqlSessionFactory.openSession();
            try{
                MyEmpMapper empMapper = session.getMapper(MyEmpMapper.class);
                long start = System.currentTimeMillis();
                int num = 100;
                for (int i = 0;i < num;i++) {
                    Random random = new Random();
                    MyEmp myEmp = new MyEmp();
                    myEmp.setName(AutoNameUtil.autoSurAndName());
                    myEmp.setAge(random.nextInt(50) + 15);// 15岁及以上
                    myEmp.setSex(random.nextInt(2));
                    empMapper.insert(myEmp);
                }
    
                session.commit();
                long end = System.currentTimeMillis();
                System.out.println("直接insert执行时间:"+(end - start));
    
            }catch (Exception e){
                e.printStackTrace();
                session.rollback();
            }finally {
                session.close();
            }
    
        }
    
    
    
    }
    

    因为数据库设置了id自增(mysql)所以id属性可以不用传。但如果我们的id是使用的序列来实现的,每次插入前需要查询一次序列的值,再或者我们的id每次插入时是现有的最大id+1怎么办?

    使用selectKey标签

        <insert id="insert" parameterType="MyEmp">
            <!-- keyProperty主键的字段名称,order=before表示在insert之前执行 -->
            <selectKey keyProperty="id" resultType="int" order="BEFORE" statementType="PREPARED">
                select ifnull(max(id),0) + 1 from t_my_emp
            </selectKey>
            insert into t_my_emp(id,name,age,sex)
            values(#{id},#{name},#{age},#{sex})
        </insert>
    
        public static void main(String[] args) throws IOException {
    
            String resouce = "mybatis-config.xml";
            InputStream in = Resources.getResourceAsStream(resouce);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
            SqlSession session = sqlSessionFactory.openSession();
            try{
                MyEmpMapper empMapper = session.getMapper(MyEmpMapper.class);
    
                Random random = new Random();
                MyEmp myEmp = new MyEmp();
                myEmp.setName(AutoNameUtil.autoSurAndName());
                myEmp.setAge(random.nextInt(50)+15);// 15岁及以上
                myEmp.setSex(random.nextInt(2));
    			// 不用设置id
                empMapper.insert(myEmp);
                session.commit();// 提交事务,否则插入不成功
    
            }catch (Exception e){
                e.printStackTrace();
                session.rollback();
            }finally {
                session.close();
            }
    
        }
    

    这种写法在批量插入的时候就会在通过java代码来循环执行empMapper.insert。

    AutoNameUtil类是随机生成姓名的工具类,请查看Java生成随机常用汉字或姓名一文

    使用foreach标签批量插入

        <insert id="insertForeach">
            insert into t_my_emp(name,age,sex)
            values
            <foreach item="myEmp" collection="myEmpList" separator=",">
            (#{myEmp.name},#{myEmp.age},#{myEmp.sex})
            </foreach>
        </insert>
    
    public interface MyEmpMapper {
    
        int insert(MyEmp myEmp);
    
        int insertForeach(@Param("myEmpList") List<MyEmp> myEmpList);
    }
    
    
    public class Demo8 {
        public static void main(String[] args) throws IOException {
    
            String resouce = "mybatis-config.xml";
            InputStream in = Resources.getResourceAsStream(resouce);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
            
            SqlSession session = sqlSessionFactory.openSession();
            try{
                MyEmpMapper empMapper = session.getMapper(MyEmpMapper.class);
    
                int num = 100;
                List<MyEmp> list = new ArrayList<>();
                for(int i = 0; i < num; i++) {
                    Random random = new Random();
                    MyEmp myEmp = new MyEmp();
                    myEmp.setName(AutoNameUtil.autoSurAndName());
                    myEmp.setAge(random.nextInt(50) + 15);// 15岁及以上
                    myEmp.setSex(random.nextInt(2));
                    list.add(myEmp);
                }
    
                long startTime = System.currentTimeMillis();
                empMapper.insertForeach(list);
                session.commit();
                long endTime = System.currentTimeMillis();
                System.out.println("批量执行时间:"+(endTime - startTime));
    
            }catch (Exception e){
                e.printStackTrace();
                session.rollback();
            }finally {
                session.close();
            }
    
        }
    }
    
    

    轻松插入100000条数据。而且速度还很快。有兴趣可以去试试循环执行Mapper.insert方法的插入,看看时间的差距。

    以上插入时id值我们使用的mysql的自增id,此时不能使用selectKey标签了。如果不想使用mysql的自增id,这样大批量插入的情况下请使用分布式id。

    使用ExecutorType.BATCH

    public class Demo9 {
        public static void main(String[] args) throws IOException {
    
            String resouce = "mybatis-config.xml";
            InputStream in = Resources.getResourceAsStream(resouce);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
            
            SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
            try{
                MyEmpMapper empMapper = session.getMapper(MyEmpMapper.class);
                long start = System.currentTimeMillis();
                int num = 1000000;
                for (int i = 0;i < num;i++) {
                    Random random = new Random();
                    MyEmp myEmp = new MyEmp();
                    myEmp.setName(AutoNameUtil.autoSurAndName());
                    myEmp.setAge(random.nextInt(50) + 15);// 15岁及以上
                    myEmp.setSex(random.nextInt(2));
                    empMapper.insert(myEmp);
                }
    
    			// 立即执行更新,执行后清除BATCH模式缓存的sql语句
                session.flushStatements();
                session.commit();
                long end = System.currentTimeMillis();
                System.out.println("直接insert执行时间:"+(end - start));
    
            }catch (Exception e){
                e.printStackTrace();
                session.rollback();
            }finally {
                session.close();
            }
    
        }
    }
    

    springBoot下设置BATCH模式

    @Bean("sqlSessionTemplate")
        public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory factory) {
            // 使用上面配置的Factory ,并且设置Template为BATCH方式
            SqlSessionTemplate template = new SqlSessionTemplate(factory, ExecutorType.BATCH);
            return template;
        }
    

    执行时间对比

    执行方式100条1000条1w条10w条100w条
    循环insert475ms1054ms4342ms29350ms279170ms
    foreach插入413ms499ms976ms2913ms报错(sql语句太大了)
    BATCH插入385ms583ms2255ms14492ms149447ms

    为了结果的准确性,每次执行前我都将已经插入的数据删除重新插入。

    从结果可以看出,foreach 和 BATCH方式在大量数据插入时效率较高。但是foreach在生成的sql语句太长(也就是字符串打大小超过限制之后会报异常),而BATCH的方式不会出现异常。注意:我们的表结构非常简单,实际情况下,表字段可能比较多,那么foreach方式可能在10w条的时候就会报错了。

    结论:在数据量级不是特别大的时候推荐使用foreach方式,在数据量级非常大的时候可以使用BATCH方式。批量插入请勿使用循环insert的方式进行。

    其实看对比结果foreach方式优势明显,只是数据量过大的时候会报错,那么如果在批量数据的事务要求并不严格的情况下,可以将超大量的数据分批次,按foreach的方式插入,是个不错的解决办法。以上面的时间来看,100w条数据的时间为2913*10=29130ms,远远比149447ms小的多。

    上一篇:Mybatis resultMap复杂查询结果映射
    下一篇:Spring Boot整合Mybatis

    展开全文
  • 主要介绍了MyBatis批量插入数据过程解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
  • mybatis 批量插入 嵌套select
  • 主要介绍了Mybatis数据批量插入如何实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
  • MyBatis是一个支持普通SQL查询,存储过程和高级映射的优秀持久层框架。这篇文章主要介绍了mybatis批量插入的两种方式(高效插入)的相关资料,非常不错,具有参考借鉴价值,感兴趣的朋友一起看看吧
  • mybatis批量插入、批量更新常规写法,及升级写法 null value in column “xxx” violates not-null constraint mybatis批量操作报错问题处理。 批量插入 常规写法: <insert id="insertUser" parameterType=...

    前言

    mybatis批量插入、批量更新常规写法,及升级写法
    null value in column “xxx” violates not-null constraint mybatis批量操作报错问题处理。

    批量插入

    常规写法:

    <insert id="insertUser" parameterType="com.test.UserEntity">
    	insert into t_com_user(user_name, age, gender)
    	values
    	<foreach collection ="list" item="item" index="index" open="(" close= ")" separator= "),(">
    		#{item.userName, jdbcType=VARCHAR},
    		#{item.age, jdbcType=INTEGER},
    		#{item.gender, jdbcType=INTEGER}
    	</foreach>
    </insert>
    
    

    假如我们在批量插入数据的时候,还想做一下关联查询,该怎么办?
    如下写法,免去了先查询数据,然后遍历list赋值,再批量插入的操作

    <insert id="insertUser" parameterType="com.test.UserEntity">
    	insert into t_com_user(user_name, age, gender, dept_name)
    	select 
    		t1.user_name, t1.age, t1.gender, t2.dept_name
    	from (
    		VALUES
    		<foreach collection ="list" item="item" index="index" open="(" close= ")" separator= "),(">
    			#{item.userName, jdbcType=VARCHAR},
    			#{item.age, jdbcType=INTEGER},
    			#{item.gender, jdbcType=INTEGER},
    			#{item.deptId, jdbcType=INTEGER}
    		</foreach>
    	) as t1 (user_name, age, gender, dept_id)
    	left_join t_com_dept t2 on t1.dept_id = t2.id
    </insert>
    
    

    批量更新

    常规写法
    这种写法实际执行过程是单条执行,即使使用的是id查找数据,但是效率较差。

    <update id="updateUser" parameterType="com.test.UserEntity">
    	<foreach collection ="list" item="item" index="index" separator= ";">
    		update t_com_user set
    			user_name = #{item.userName, jdbcType=VARCHAR},
    			age = #{item.age, jdbcType=INTEGER},
    			gender = #{item.gender, jdbcType=INTEGER}
    		where id = #{item.id, jdbcType=INTEGER}
    	</foreach>
    </update>
    
    

    升级写法:参考批量插入操作,使用连表更新的语法

    <update id="updateUser" parameterType="com.test.UserEntity">
    	update t_com_user t1 set
    		user_name = t2.user_name,
    		age = t2.age,
    		gender = t2.gender
    	from (
    		values
    		<foreach collection ="list" item="item" index="index" open="(" close= ")" separator= "),(">
    			#{item.id, jdbcType=INTEGER},
    			#{item.userName, jdbcType=VARCHAR},
    			#{item.age, jdbcType=INTEGER},
    			#{item.gender, jdbcType=INTEGER}
    		</foreach>
    	) as t2 (id, user_name, age, gender)
    	where t1.id = t2.id
    </update>
    
    

    ERROR: null value in column “user_name” violates not-null constraint mybatis 批量操作报错问题处理

    mybatis的批量操作过程,传入的list对象集合中,对象的某个属性难免会为null,如果数据库表该列恰好有not-null限制,则会报错;处理办法,本文以pgsql的批量更新为例,亲测可行:
    方法一:
    使用COALESCE(#{item.userName, jdbcType=VARCHAR}, ‘默认名称’)
    方式二:user_name = COALESCE(t2.user_name, ‘默认名称’)
    二选一即可

    <update id="updateUser" parameterType="com.test.UserEntity">
    	update t_com_user t1 set
    		user_name = COALESCE(t2.user_name, '默认名称'),
    		age = t2.age,
    		gender = t2.gender
    	from (
    		values
    		<foreach collection ="list" item="item" index="index" open="(" close= ")" separator= "),(">
    			#{item.id, jdbcType=INTEGER},
    			COALESCE(#{item.userName, jdbcType=VARCHAR}, '默认名称'),
    			#{item.age, jdbcType=INTEGER},
    			#{item.gender, jdbcType=INTEGER}
    		</foreach>
    	) as t2 (id, user_name, age, gender)
    	where t1.id = t2.id
    </update>
    
    

    mysql 使用IFNULL函数 pgsql 使用COALESCE函数
    方法二:
    在foreach中使用if标签判断

    <if test= "item.userName != null">
    	#{item.userName, jdbcType=VARCHAR}
    </if>
    <if test= "item.userName == null">
    	'默认名称'
    </if>
    
    

    Oracle mysql下,不支持 from (value (),()) as t 的写法;可以参考

    select * from (
    	select 1, '张三' from dual union
    	select 2, '李四' from dual 
    ) t
    
    

    null 原因

    当mybatis做批量插入时,插入的字段可能没值,此时不做处理的话,mybatis会报异常,执行失败

    根据mybatis的官网介绍,此时需要添加对应的jdbcType类型映射,以处理null值 ,具体的映射关系参考:jdbc映射关系表

    此时,在所有可能为空的字段取值中添加jdbcType=XXX(一般全部添加即可)

    案例

    1.使用union all 来串连每个values,其中jdbcType的设置可以使null值也输入进去

    <insert id="saveList" parameterType="java.util.List">
    	INSERT INTO DDZHPT.CMS_SCHEDUAL_DETIAL
    	(
    		DEPT_ID,
    		SCHEDUAL_DATE,
    		CMS_SCHEDUAL_TYPE_ID,
    		CMS_SCHEDUAL_TEAM_ID,
    		CMS_SCHEDUAL_TYPE_PERIOD_ID,
    		CMS_SCHEDUAL_TIME_ID,
    		SYS_POST_ID,
    		POINT_ID,
    		PERSON_ID
       )
       <foreach collection="list" item="item" index="index" separator="union all">
       			SELECT
       			#{item.deptId,jdbcType=DECIMAL},
       			#{item.schedualDate,jdbcType=TIMESTAMP},
       			#{item.cmsSchedualTypeId,jdbcType=VARCHAR},
       			#{item.cmsSchedualTeamId,jdbcType=VARCHAR},
       			#{item.cmsSchedualTypePeriodId,jdbcType=VARCHAR},
       			#{item.cmsSchedualTimeId,jdbcType=VARCHAR},
       			#{item.sysPostId,jdbcType=VARCHAR},
       			#{item.pointId,jdbcType=VARCHAR},
       			#{item.personId,jdbcType=VARCHAR}
       			FROM DUAL
       </foreach>
    </insert>
    
    

    2.纯粹使用foreach

    <insert id="saveList" parameterType="java.util.List">
    	<foreach collection="list" item="item" index="index" separator="union all">
    			INSERT INTO DDZHPT.CMS_SCHEDUAL_DETIAL
    			(
    				DEPT_ID,
    				SCHEDUAL_DATE,
    				CMS_SCHEDUAL_TYPE_ID,
    				CMS_SCHEDUAL_TEAM_ID,
    				CMS_SCHEDUAL_TYPE_PERIOD_ID,
    				CMS_SCHEDUAL_TIME_ID,
    				SYS_POST_ID,
    				POINT_ID,
    				PERSON_ID
    		   )VALUES(
    		   			#{item.deptId,jdbcType=DECIMAL},
    		   			#{item.schedualDate,jdbcType=TIMESTAMP},
    		   			#{item.cmsSchedualTypeId,jdbcType=VARCHAR},
    		   			#{item.cmsSchedualTeamId,jdbcType=VARCHAR},
    		   			#{item.cmsSchedualTypePeriodId,jdbcType=VARCHAR},
    		   			#{item.cmsSchedualTimeId,jdbcType=VARCHAR},
    		   			#{item.sysPostId,jdbcType=VARCHAR},
    		   			#{item.pointId,jdbcType=VARCHAR},
    		   			#{item.personId,jdbcType=VARCHAR}
    		   			)
    	</foreach>
    </insert>
    
    

    推荐使用第一种方式,数据库语句只有一条,减少数据库执行语句的负担

    3.union与union all区别
    在这里插入图片描述

     <!--插入所有列清单-->
        <sql id="insertAllCol">
            <trim prefix="(" suffix=")" suffixOverrides=",">
                FPH,
                EFFECTIVE_TAX_AMOUNT,
                PURCHASER_TAXNO,
                INVOICE_STATE,
                DEDUCTIBLE_MODE,
                AMOUNT,
                OVERDUE_CHECK_MARK,
                ABNORMAL_TYPE,
                NSRSBH,
                ANTI_FAKE_CODE,
                UPDATE_TIME,
                DEDUCTIBLE_PERIOD,
                AGENCY_DRAWBACK,
                RESALE_CERTIFICATE_NUMBER,
                INVOICE_NO,
                CREATE_TIME,
                INV_ISSUE_DATE,
                TAX,
                AUDIT_STATE,
                DEDUCTIBLE_TYPE,
                DEDUCTIBLE_DATE,
                MANAGEMENT_STATUS,
                SALES_TAXNAME,
                DEDUCTIBLE_STATE,
                FLOW_ID,
                INVOICE_CATAGORY,
                SALES_TAXNO,
                INVOICE_CODE,
                ORIGINAL_PERIOD,
                INFO_SOURCES,
            </trim>
        </sql>
     
        <sql id="insertAllValueWithItem" databaseId="oracle">
            <trim prefix=" SELECT " suffix=" FROM dual " suffixOverrides=",">
                #{item.fph,jdbcType=VARCHAR},
                #{item.effectiveTaxAmount,jdbcType=NUMERIC},
                #{item.purchaserTaxno,jdbcType=VARCHAR},
                #{item.invoiceState,jdbcType=DATE},
                #{item.deductibleMode,jdbcType=VARCHAR},
                #{item.amount,jdbcType=NUMERIC},
                #{item.overdueCheckMark,jdbcType=VARCHAR},
                #{item.abnormalType,jdbcType=VARCHAR},
                #{item.nsrsbh,jdbcType=VARCHAR},
                #{item.antiFakeCode,jdbcType=VARCHAR},
                #{item.updateTime,jdbcType=DATE},
                #{item.deductiblePeriod,jdbcType=VARCHAR},
                #{item.agencyDrawback,jdbcType=VARCHAR},
                #{item.resaleCertificateNumber,jdbcType=VARCHAR},
                #{item.invoiceNo,jdbcType=VARCHAR},
                #{item.createTime,jdbcType=DATE},
                #{item.invIssueDate,jdbcType=VARCHAR},
                #{item.tax,jdbcType=NUMERIC},
                #{item.auditState,jdbcType=NUMERIC},
                #{item.deductibleType,jdbcType=VARCHAR},
                #{item.deductibleDate,jdbcType=VARCHAR},
                #{item.managementStatus,jdbcType=VARCHAR},
                #{item.salesTaxname,jdbcType=VARCHAR},
                #{item.deductibleState,jdbcType=VARCHAR},
                #{item.flowId,jdbcType=NUMERIC},
                #{item.invoiceCatagory,jdbcType=VARCHAR},
                #{item.salesTaxno,jdbcType=VARCHAR},
                #{item.invoiceCode,jdbcType=VARCHAR},
                #{item.originalPeriod,jdbcType=VARCHAR},
                #{item.infoSources,jdbcType=VARCHAR},
            </trim>
        </sql>
    

    注:union all和union的区别

    union all连接查询,结果不去重
    union做连接查询结果去重

    展开全文
  • mybatis批量插入更新数据
  • MyBatis 批量插入数据的 3 种方法

    万次阅读 2021-11-12 13:17:24
    批量插入功能是我们日常工作中比较常见的业务功能之一,今天来一个 MyBatis 批量插入的汇总篇,同时对 3 种实现方法做一个性能测试,以及相应的原理分析。 先来简单说一下 3 种批量插入功能分别是: 循环单次插入;...
  • mapper.xml: INSERT INTO xxx ( value_id, prop_value, prop_id ) VALUES ( #{item.valueId}, #{item.propValue}, #{item.propId} ) 错误案例:
  • 使用Mybatis完成Oracle数据库批量插入
  • mybatis 批量插入 和批量更新一、批量插入二、批量更新 一、批量插入 <insert id="insert" parameterType="list"> insert into aaa( id, name, create_time, update_time, create_user, update_user) ...
  • 批量插入数据,数据需要有自增 id。每次插入有一个唯一的 sessionId 来标记这些记录,插入完成之后返回这个 sessionId。 方案 循环插入单条记录,伪代码: int sessionId = dao.querySessionId(); for (Record ...
  • 批量插入这个问题,我们用 JDBC 操作,其实就是两种思路吧: 用一个 for 循环,把数据一条一条的插入(这种需要开启批处理)。 生成一条插入 sql,类似这种 insert into user(username,address) values(‘aa’,‘bb...
  • MyBatis批量插入数据

    2021-02-03 21:19:38
    在程序中封装了一个List集合对象,然后需要把该集合中的实体插入到数据库中,由于项目使用了Spring+MyBatis的配置,所以打算使用MyBatis批量插入,由于之前没用过批量插入,在网上找了一些资料后最终实现了,把详细...
  • MyBatis 批量插入数据到Oracle
  • 文章目录一、前言二、...Mybatis批量插入的正确姿势到底是什么?在网上浏览了非常多的帖子,很多都是复制粘贴来的,内容基本都是在误导别人,几乎没有测试验证,如果照做的话,性能反而相对于单条几乎没有任何提升,
  • Mybatis如何执行批量操作 使用foreach标签 foreach的主要用在构建in条件中,它可以在SQL语句中进行迭代一个集合。foreach标签的属性主要有item,index,collection,open,separator,close。 item 表示集合中...
  • mybatis批量插入更新

    2021-12-02 12:47:41
    1、replace into… 此种方式,主键会改变,如果主键使用的是自增策略,每插入更新一次,主键都会自增 2、ON DUPLICATE KEY UPDATE ,此种方式,主键不会变

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 23,263
精华内容 9,305
关键字:

mybatis批量插入

友情链接: Mharde.zip