Mybatis批量插入数据

三种方式对比

1. foreach插入、

2. SqlSession批量插入、

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

//测试类

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

@Autowired
UsersMapper usersMapper;

@Autowired
SqlSessionFactory sqlSessionFactory;
public List 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%50004999) {
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 {
@InsertProvider(type = UsersProvider.class, method = "insertListSql")
public void sqlInsert(List list);
public void xmlBatchInsert(@Param("list") List list);
}

//sql插入相关类

@Repository
public interface UsersMapper extends BaseMapper {

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

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

public class UsersProvider {

public String insertListSql(List 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相关文件

INSERT INTO users(id,name,age,manager_id) VALUES

(#{item.id},
#{item.name},
#{item.age},
#{item.managerId})