list mybatis批量保存_mybatis批量保存

mybatis批量保存

mybatis

批量保存

1、概述

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

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

1、打开批处理

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

2、初始化数据

List 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 params = new HashMap();

List students = studentMapper.queryStudentAndTeacher(params);

//2、初始化数据

List 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 students){

for (Student student : students) {

System.out.println(student.getId() + student.getTeacher().getName());

}

}

/**

* 初始化参数；用于测试保存内容；

*@author thero

*@return List

* student集合内容

*/

public List initStudent(){

List students = new ArrayList();

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 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}

)

程序完成插入执行的时间为：

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 [[email protected]]

ooo Using Connection [[email protected]]

==> 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 into student (id, name, gender,

major, grade

)

values

(null,#{item.name},#{item.gender},#{item.major},#{item.grade})

我们查看执行输入内容

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 [[email protected]]

ooo Using Connection [[email protected]]

==> 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 [[email protected]]

==> 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进行保存的效率会有较大的提升