目录
查询不存在批量,一般是增删改的概念
import com.lyx.mybatis.dao.StudentMapper;
import com.lyx.mybatis.entity.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.Reader;
public class StudentTest {
public static void main(String[] args) throws IOException {
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
//获取执行批量插入前的时间
long start = System.currentTimeMillis();
//for循环实现批量插入1000行数据
for (int i = 0; i < 1000; i++) {
//当前i值为偶数,则当前student对象的grade值为g1
String grade = (i % 2 == 0) ? "g1" : "g2";
int classId = (i % 2 == 0) ? 1 : 2;
String stuName = "Gavin" + i;
Student student = new Student(grade, stuName, classId);
mapper.insertStudent(student);
}
//获取执行批量插入后的时间
long end = System.currentTimeMillis();
//输出执行插入1000行需要多少毫秒
System.out.println("运行时间:" + (end - start));
sqlSession.commit();
sqlSession.close();
}
}
/*
* 运行结果:
* 运行时间:8551
* */
如何让批量插入运行效率提高?
数据库处理层
1. 设置ExecutorType为BATCH类型
批量操作的核心代码就是添加ExecutorType.BATCH
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
分析效率提高的原因:
第一步:开启日志
第二步:log4j.properties
log4j.rootLogger=DEBUG,stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
stdout:标准输出 appender:输出目的地 pattern:模式 layout:布局 conversion:转变,转换
第三步:测试
由上图可以看到,BATCH类型,在执行的时候只需要放值,在执行时,对于sql语句:
只预编译一次,这个sql语句的架构只写一次,下次调用,只需要放参数值到sql语句中
不加Executor.BATCH,发现每次插入都要预编译
不推荐的插入方式:
2. <foreach>标签实现批量插入(不推荐)
2.1 Oracle数据库实现:
以begin
insert into student(grade,stuName,classId) values(?,?,?);
insert into student(grade,stuName,classId) values(?,?,?);
end;为例
也可把begin和end;写到foreach中:
<foreach collection="list" open="begin" close="end;" separator="," item="u">
insert into t_user values(null,#{u.username},#{u.password},#{u.gender},#{u.registTime})
</foreach>
测试:
运行报错:
解决以后再次运行:
2.2 mysql数据库批量插入
1. XxxMapper.xml文件
<insert id="insertStudents">
insert into student(grade,stuName,classId) values
<foreach collection="list" separator="," item="student" close=";">
(#{student.grade},#{student.Name},#{student.classId})
</foreach>
</insert>
2. 测试
import com.lyx.mybatis.dao.StudentMapper;
import com.lyx.mybatis.entity.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
public class StudentTest {
public static void main(String[] args) throws IOException {
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
// SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
SqlSession sqlSession = sqlSessionFactory.openSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
//获取执行批量插入前的时间
long start = System.currentTimeMillis();
List<Student> students = new ArrayList<>();
students.add(new Student("g2", "Huary", 1));
students.add(new Student("g2", "Mreli", 1));
students.add(new Student("g2", "Jerffy", 2));
mapper.insertStudents(students);
//获取执行批量插入后的时间
long end = System.currentTimeMillis();
//输出执行批量插入需要多少毫秒
System.out.println("运行时间:" + (end - start));
sqlSession.commit();
sqlSession.close();
}
}
2.3 为什么不推荐
- 没有用到mybatis对批量插入的支持
- 不适合数据库迁移
- 数据量的情况下,则会将拼接的SQL语句拉的很长,而部分数据库 对SQL语句的长度有限制