Mybatis批量插入数据

三种方式对比

1. foreach插入、

2. SqlSession批量插入、

3. sql插入 : @InsertProvider注解 效率最高

//测试类

@RunWith(SpringRunner.class)
@SpringBootTest
public class Test1 {

@Autowired
UsersMapper usersMapper;

@Autowired
SqlSessionFactory sqlSessionFactory;
public List<Users> list = new ArrayList<>();

@Before
public void  getList() {
    long start = System.currentTimeMillis();
    Users user;
    for (int i = 1; i <=50000 ; i++) {
        user = new Users();
        user.setId(i);
        user.setName("java");
        user.setAge(200);
        user.setManagerId(222);
        list.add(user);
    }
    System.out.println("拼装数据 耗时："+(System.currentTimeMillis()-start));
    System.out.println(list.size());
}


@Test
public void batchInsert() {
    SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);
    UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
    System.out.println("batchInsert 插入开始========");
    long start = System.currentTimeMillis();
    for (int i = 0; i < list.size(); i++) {
        mapper.insert(list.get(i));
        if (i%5000==4999) {
            sqlSession.flushStatements();
            //sqlSession.commit();
            //sqlSession.clearCache();
        }
    }
    //sqlSession.commit();
    //sqlSession.clearCache();
    sqlSession.flushStatements();
    System.out.println("SqlSession 批量插入耗时："+(System.currentTimeMillis()-start));
}

@Test
public void forEachInsert() {
    System.out.println("forEachInsert 插入开始========");
    long start = System.currentTimeMillis();
    for (int i = 0; i < list.size(); i++) {
        usersMapper.insert(list.get(i));
    }
    System.out.println("foreach 插入耗时："+(System.currentTimeMillis()-start));
}

@Test
public void sqlInsert() {
    System.out.println("sql 插入开始========");
    long start = System.currentTimeMillis();
    usersMapper.sqlInsert(list);
    System.out.println("sql 插入耗时："+(System.currentTimeMillis()-start));
}

// xml批量插入

@Test
public void xmlInsert() {
    System.out.println("xmlInsert 批量插入开始========");
    long start = System.currentTimeMillis();
    usersMapper.xmlBatchInsert(list);
    System.out.println("xmlInsert 批量插入耗时："+(System.currentTimeMillis()-start));
}
}

//sql插入相关类

@Repository
public interface UsersMapper extends BaseMapper<Users> {
	@InsertProvider(type = UsersProvider.class, method = "insertListSql")
	public void sqlInsert(List<Users> list);
	public void xmlBatchInsert(@Param("list") List<Users> list);
}

//sql插入相关类

@Repository
public interface UsersMapper extends BaseMapper<Users> {

	@InsertProvider(type = UsersProvider.class, method = "insertListSql")
	public void sqlInsert(List<Users> list);

	public void xmlBatchInsert(@Param("list") List<Users> list);
}

public class UsersProvider {

	public String insertListSql(List<Users> list) {
		StringBuffer sqlList = new StringBuffer();

		sqlList.append(" INSERT INTO users(id,name,age,manager_id)  VALUES ");
		for (int i = 0; i < list.size() ; i++) {
			Users user = list.get(i);
			sqlList.append(" (").append(user.getId()).append(",").append("'").append(user.getName()).append("',").append(user.getAge())
					.append(",").append(user.getManagerId()).append(")");
			if (i < list.size()-1) {
				sqlList.append(",");
			}
		}
		return sqlList.toString();
	}
}

// xml相关文件

<?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.example.demo.dao.UsersMapper">

<insert id="xmlBatchInsert">
		INSERT INTO users(id,name,age,manager_id)  VALUES
<foreach collection="list" item="item" index="index" separator=",">
		(#{item.id},
		#{item.name},
		#{item.age},
		#{item.managerId})
</foreach>
</insert>
</mapper>