mybatis批量保存

1、概述

mybatis 批量保存的时候，如果数据大于1000条，应该使用批量的形式进行处理数据；处理的不同的地方主要是在xml文件当中的配置信息；一种是使用java的for进行保存，另一种，是使用xml中的循环拼接sql来完成数据的插入；现在我们来对比两种方式的不同，以及执行效率；

2、 方法1：使用java的for循环来完成批量保存

1、打开批处理

session = sqlSessionFactory.openSession(ExecutorType.BATCH, true);

2、初始化数据

List<Student> preSaveStudent = initStudent();

3、执行批量插入

    for (int i = 0; i < preSaveStudent.size(); i++) {
            studentMapper.insert(preSaveStudent.get(i));
        }

4、事务提交

    session.commit();

代码实例：

@Test
    public void BatchSave() throws IOException{
        org.apache.ibatis.logging.LogFactory.useStdOutLogging();
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
                .build(reader);
        reader.close();
        SqlSession session = null;
        //1、打开批处理
        session = sqlSessionFactory.openSession(ExecutorType.BATCH, true);
        StudentMapper studentMapper = (StudentMapper) session.getMapper(StudentMapper.class);
        Map<String, Object> params = new HashMap<String,Object>();
        List<Student> students = studentMapper.queryStudentAndTeacher(params);
        
        //2、初始化数据
        List<Student> preSaveStudent = initStudent();
        Date first = new Date();  
        
        //3、执行批量插入
        for (int i = 0; i < preSaveStudent.size(); i++) {
            studentMapper.insert(preSaveStudent.get(i));
        }
        //4、事务提交
        session.commit();
        System.out.println("first quest costs:"+ (new Date().getTime()-first.getTime()) +" ms");  
    }
    /**
     * 打印学生信息
     * @author thero
     * @param students
     */
    public void printStudent(List<Student> students){
        for (Student student : students) {
            System.out.println(student.getId() + student.getTeacher().getName());
        }
    }
    
    /**
     * 初始化参数；用于测试保存内容；
     * @author thero
     * @return List<Student>
     *  student集合内容
     */
    public List<Student> initStudent(){
        List<Student> students = new ArrayList<Student>();
        for(int i = 0 ;i < 1000;i++){
            Student student = new Student();
            student.setGender("男"+i);
            student.setGrade("100"+i);
            student.setMajor("软件技术"+i);
            student.setName("小哈哈"+i);
            student.setSupervisorId(1);
            students.add(student);
        }
        return students;
    }

其中xml的配置内容为：

 <insert id="insert" parameterType="com.cpp.core.common.entity.Student" >
    insert into student (id, name, gender, 
      major, grade, supervisor_id
      )
    values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{gender,jdbcType=VARCHAR}, 
      #{major,jdbcType=VARCHAR}, #{grade,jdbcType=VARCHAR}, #{supervisorId,jdbcType=INTEGER}
      )
  </insert>

程序完成插入执行的时间为：
first quest costs:3267 ms

2.1 相关日志输出

Logging initialized using 'org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Openning JDBC Connection
Created connection 24166053.
Setting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@170bea5]
ooo Using Connection [com.mysql.jdbc.JDBC4Connection@170bea5]
==>  Preparing: SELECT s.id, s.`name`, s.gender, s.major, s.grade, s.supervisor_id , t.id as tea_id, t.`name` as tea_name, t.gender as tea_gender, t.research_area as tea_research_area, t.title as tea_title FROM student s LEFT JOIN teacher t ON s.supervisor_id = t.id 
==> Parameters: 
<==    Columns: id, name, gender, major, grade, supervisor_id, id, name, gender, research_area, title
<==        Row: 1, 李林, 男, 计算机科学与技术, 2011, 1, 1, 刘老师, null, null, null
<==        Row: 2, 陈明, 男, 软件技术, 2012, 1, 1, 刘老师, null, null, null
<==        Row: 4, 陈明2, 男, 软件技术, 2012, 2, 2, 郝老师, 男, 无语, 五
ooo Using Connection [com.mysql.jdbc.JDBC4Connection@170bea5]
==>  Preparing: insert into student (id, name, gender, major, grade, supervisor_id ) values (?, ?, ?, ?, ?, ? ) 
==> Parameters: null, 小哈哈0(String), 男0(String), 软件技术0(String), 1000(String), 1(Integer)
==> Parameters: null, 小哈哈0(String), 男0(String), 软件技术0(String), 1000(String), 1(Integer)
==> Parameters: null, 小哈哈1(String), 男1(String), 软件技术1(String), 1001(String), 1(Integer)
==> Parameters: null, 小哈哈2(String), 男2(String), 软件技术2(String), 1002(String), 1(Integer)
....
==> Parameters: null, 小哈哈999(String), 男999(String), 软件技术999(String), 100999(String), 1(Integer)
first quest costs:3267 ms

3、方法2 xml中循环学生的相关信息；

与方法1相比，在xml发生的变化较大的地方是xml,主要是在xml拼接了sql，下面是xml内容

  <insert id="batchInsert" parameterType="list">
      insert into student (id, name, gender, 
      major, grade
      )
    values 
     <foreach collection="list" item="item" index="index" separator=",">  
         (null,#{item.name},#{item.gender},#{item.major},#{item.grade})  
     </foreach>  
  </insert>

我们查看执行输入内容

Logging initialized using 'org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Openning JDBC Connection
Created connection 13676443.
Setting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@d0af9b]
ooo Using Connection [com.mysql.jdbc.JDBC4Connection@d0af9b]
==>  Preparing: SELECT s.id, s.`name`, s.gender, s.major, s.grade, s.supervisor_id , t.id as tea_id, t.`name` as tea_name, t.gender as tea_gender, t.research_area as tea_research_area, t.title as tea_title FROM student s LEFT JOIN teacher t ON s.supervisor_id = t.id 
==> Parameters: 
ooo Using Connection [com.mysql.jdbc.JDBC4Connection@d0af9b]
==>  Preparing: insert into student (id, name, gender, major, grade ) values (null,?,?,?,?) , ..., (null,?,?,?,?) 
==> Parameters: 小哈哈00-0(String), ..., 100999999-999(String)
耗费时间:257 ms

对比2中保存的方式，那么可以查看到保存的时间上的差别
方法1：first quest costs:3267 ms
方法2：耗费时间:257 ms

结论在使用批量保存的时候使用方法2进行保存的效率会有较大的提升