码库记事本

码库记事本

Mybatis批量提交实现步骤

小诸哥 0

简介:mybatis的批量操作减少数据库连接次数

一、mapper 使用foreach 遍历

  批量insert:

<insert id="addEmpsBatch">
INSERT INTO emp(ename,gender,email,did)
VALUES
<foreach collection="emps" item="emp" separator=",">
(#{emp.eName},#{emp.gender},#{emp.email},#{emp.dept.id})
</foreach>
</insert>

批量update:

<update id="updateNewStock" parameterType="java.util.List">
<foreach collection="list" item="bean" index="index" open="" close="" separator=";">
UPDATE green_beans
<set>
stock=#{bean.stock}
</set>
<where>
beanUid = #{bean.beanUid}
</where>
</foreach>
</update>

二、使用 mybatis ExecutorType.BATCH

使用步骤:

(1)在全局配置文件applcationContext.xml中加入

<!-- 配置一个可以批量执行的sqlSession -->
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
<constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory"></constructor-arg>
<constructor-arg name="executorType" value="BATCH"></constructor-arg>
</bean>

(2)在service实现中添加:

@Autowired
private SqlSession sqlSession;



//批量保存员工
@Override
public Integer batchEmp() {
// TODO Auto-generated method stub

//批量保存执行前时间
long start=System.currentTimeMillis();

EmployeeMapper mapper= sqlSession.getMapper(EmployeeMapper.class);
for (int i = 0; i < 10000; i++) {
mapper.addEmp(new Employee(UUID.randomUUID().toString().substring(0,5),"b","1"));

}
long end= System.currentTimeMillis();
long time2= end-start;
//批量保存执行后的时间
System.out.println("执行时长"+time2);
return (int) time2;

}

示例:

@Test //批量保存方法测试
public void testBatch() throws IOException{
SqlSessionFactory sqlSessionFactory=getSqlSessionFactory();
//可以执行批量操作的sqlSession
SqlSession openSession=sqlSessionFactory.openSession(ExecutorType.BATCH);

//批量保存执行前时间
long start=System.currentTimeMillis();
try{
EmployeeMapper mapper= openSession.getMapper(EmployeeMapper.class);
for (int i = 0; i < 1000; i++) {
mapper.addEmp(new Employee(UUID.randomUUID().toString().substring(0,5),"b","1"));
}

openSession.commit();
long end= System.currentTimeMillis();
//批量保存执行后的时间
System.out.println("执行时长"+(end-start));
//批量 预编译sql一次==》设置参数==》10000次==》执行1677
//非批量 (预编译=设置参数=执行 )==》100001121

}finally{
openSession.close();
}
}

mapper:

public interface EmployeeMapper {  
//批量保存员工
public Long addEmp(Employee employee);
}

mapper.xml :

<mapper namespace="com.agesun.mybatis.dao.EmployeeMapper"
<!--批量保存员工 -->
<insert id="addEmp">
insert into employee(lastName,email,gender)
values(#{lastName},#{email},#{gender})
</insert>
</mapper>

三、总结:

方式一、需要修改数据库属性添加allowMutiQueries=true,例如:jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true

方式二、需要开启事务提交,在applcationContext.xml中添加BATCH

标签: 实现