MyBatis学习笔记（七）批量插⼊ExecutorType.BATCH效率
对⽐
MyBatis 学习笔记（七）批量插⼊ExecutorType.BATCH效率对⽐
⼀、在mybatis中ExecutorType的使⽤
1.Mybatis内置的ExecutorType有3种，默认的是simple，该模式下它为每个语句的执⾏创建⼀个新的预处理语句，单条提交sql；⽽batch模式重复使⽤已经预处理的语句，
并且批量执⾏所有更新语句，显然batch性能将更优；
2.但batch模式也有⾃⼰的问题，⽐如在Insert操作时，在事务没有提交之前，是没有办法获取到⾃增的id，这在某型情形下是不符合业务要求的；
在测试中使⽤simple模式提交10000条数据，时间为18248 毫秒，batch模式为5023 ，性能提⾼70%；
@Test
public void mybatisBatch() {
SqlSession session = getSqlSessionFactory().openSession();
try {
DeptMapper deptMapper = (DeptMapper) Mapper(DeptMapper.class);
long start =System.currentTimeMillis();
for (int i = 0; i <10000 ; i++) {
SysDept dept=new SysDept(UUID.randomUUID().toString().substring(1,6), 1, new Date(), new Date(), 1);
deptMapper.saveSysDept(dept);
}
long end =System.currentTimeMillis();
System.out.println("耗时:"+(end-start));
//ExecutorType.BATCH 批量耗时耗时:2134
//单条操作耗时耗时:8584
} catch (Exception e) {
e.printStackTrace();
} finally {
sessionmit();
session.close();
}
}
@Test
public void saveDeptBatchOne() {
SqlSession session = getSqlSessionFactory().openSession();
try {
DeptMapper deptMapper = (DeptMapper) Mapper(DeptMapper.class);
long start =System.currentTimeMillis();
List<SysDept> deptList=new ArrayList<SysDept>();
for (int i = 0; i <100000 ; i++) {
SysDept dept=new SysDept(UUID.randomUUID().toString().substring(1,6), 1, new Date(), new Date(), 1);
deptList.add(dept);
if(i%500==0){
deptMapper.saveDeptBatch(deptList);
deptList.clear();
}
}
deptMapper.saveDeptBatch(deptList);
long end =System.currentTimeMillis();
System.out.println("耗时:"+(end-start));
//⾮BATCH批量耗时耗时:938
//⾮BATCH批量耗时耗时:938
} catch (Exception e) {
e.printStackTrace();
} finally {
sessionmit();
session.close();
}
}
@Test
public void saveDeptBatchTwo() {
//设置ExecutorType.BATCH原理：把SQL语句发个数据库，数据库预编译好，数据库等待需要运⾏的参数，接收到参数后⼀次运⾏，ExecutorType.BATCH只打印⼀次SQL语句，多次设置参数步骤，
SqlSession session = getSqlSessionFactory().openSession(ExecutorType.BATCH);
try {
DeptMapper deptMapper = (DeptMapper) Mapper(DeptMapper.class);
long start =System.currentTimeMillis();
List<SysDept> deptList=new ArrayList<SysDept>();
for (int i = 0; i <100000; i++) {
SysDept dept=new SysDept(UUID.randomUUID().toString().substring(1,6), 1, new Date(), new Date(), 1);
deptList.add(dept);
if(i%500==0){
deptMapper.saveDeptBatch(deptList);
deptList.clear();
}
}
deptMapper.saveDeptBatch(deptList);
long end =System.currentTimeMillis();
System.out.println("耗时:"+(end-start));
//BATCH批量耗时耗时:822
} catch (Exception e) {
e.printStackTrace();
} finally {
sessionmit();
session.close();
}
}
⼆、在mybatis+spring中ExecutorType的使⽤
批量更新sql语句1、在spring配置⽂件中添加批量执⾏的SqlSessionTemplate
<!--配置⼀个可以进⾏批量执⾏的sqlSession -->
<bean id="sqlSession" class="batis.spring.SqlSessionTemplate">
<constructor-arg name="sqlSessionFactory" ref="sqlSessionFactoryBean"></constructor-arg>
<constructor-arg name="executorType" value="BATCH"></constructor-arg>
</bean>
2、service中获取批量添加的SqlSession
@Service
public class DeptService {
@Autowired
private DeptMapper deptMapper;
@Autowired
private SqlSession sqlSession;
public List<Dept> addDept(){
//executorType=BATCH 添加操作
DeptMapper mapper = Mapper(DeptMapper.class);
return mapper.saveDept(Dept);
}
}
三、$和#的区别
#{}：可以获取map中的值或者pojo对象属性的值；
${}：可以获取map中的值或者pojo对象属性的值；
select * from tbl_employee where id=${id} and last_name=#{lastName} Preparing: select * from tbl_employee where id=2 and last_name=?
区别：
#{}：是以预编译的形式，将参数设置到sql语句中；PreparedStatement；防⽌sql注⼊${}：取出的值直接拼装在sql语句中；会有安全问题；
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论