Two ways to batch insert mybatis

1. mybiats foreach tag

foreach is mainly used to build in conditions. It can iterate a set in a SQL statement. The attributes of the foreach element mainly include item, index, collection, open, separator, and close. item represents the alias of each element in the collection when iterating, index specifies a name, which is used to represent the position of each iteration during the iteration process, open represents what the statement starts with, and separator represents the time between each iteration. What symbol is used as the separator, and what close means to end with. The most critical and most error-prone thing when using foreach is the collection attribute. This attribute must be specified, but in different situations, the value of this attribute is different. Yes, there are mainly three situations:

If a single parameter is passed in and the parameter type is a List, the collection attribute value is list

If a single parameter is passed in and the parameter type is an array array , the attribute value of collection is array

If there are multiple parameters passed in, we need to encapsulate them into a Map

The specific usage is as follows:

<insert id="insertBatch" parameterType="List">
     INSERT INTO TStudent(name,age) <foreach collection="list" item="item" index="index" open="("close=")"separator="union all">
     SELECT #{item.name} as a, #{item.age} as b FROM DUAL </foreach></insert>

2. mybatis ExecutorType.BATCH

Mybatis built-in There are three types of ExecutorType. The default is simple. In this mode, it creates a new prepared statement for the execution of each statement and submits the sql individually; while the batch mode reuses the preprocessed statements and executes all update statements in batches. Obviously the batch performance will be better; but the batch mode also has its own problems. For example, during the Insert operation, there is no way to obtain the auto-incremented ID before the transaction is submitted. This does not meet the business requirements in certain situations

The specific usage is as follows:

*Method 1 of spring+mybatis

//获取sqlsession//从spring注入原有的sqlSessionTemplate@Autowiredprivate SqlSessionTemplate sqlSessionTemplate;// 新获取一个模式为BATCH，自动提交为false的session// 如果自动提交设置为true,将无法控制提交的条数，改为最后统一提交，可能导致内存溢出SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH,false);    //通过新的session获取mapper
    fooMapper = session.getMapper(FooMapper.class);    int size = 10000;    try{        for(int i = 0; i < size; i++) {
            Foo foo = new Foo();
            foo.setName(String.valueOf(System.currentTimeMillis()));
            fooMapper.insert(foo);            if(i % 1000 == 0 || i == size - 1) {             //手动每1000个一提交，提交后无法回滚 
            session.commit();            //清理缓存，防止溢出
            session.clearCache();
            }
        }
    } catch (Exception e) {        //没有提交的数据可以回滚
        session.rollback();
    } finally{
        session.close();
    }

spring+mybatis

Method 2:

Combined with the general mapper sql alias, preferably the package name + class name

public void insertBatch(Map<String,Object> paramMap, List<User> list) throws Exception {        // 新获取一个模式为BATCH，自动提交为false的session
        // 如果自动提交设置为true,将无法控制提交的条数，改为最后统一提交，可能导致内存溢出
        SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);        try {            if(null != list || list.size()>0){                int  lsize=list.size();                for (int i = 0, n=list.size(); i < n; i++) {
                    User user= list.get(i);
                    user.setIndate((String)paramMap.get("indate"));
                    user.setDatadate((String)paramMap.get("dataDate"));//数据归属时间
                //session.insert("com.xx.mapper.UserMapper.insert",user);
  //session.update("com.xx.mapper.UserMapper.updateByPrimaryKeySelective",_entity);
                                        session.insert(“包名+类名", user);
                    if ((i>0 && i % 1000 == 0) || i == lsize - 1) {
                        // 手动每1000个一提交，提交后无法回滚
                        session.commit();
                        // 清理缓存，防止溢出
                        session.clearCache();
                    }
                }
            }
        } catch (Exception e) {
            // 没有提交的数据可以回滚
            session.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }