基于Mybatis和BaseService的批量操作(MySql)

本文主要是介绍基于Mybatis和BaseService的批量操作(MySql)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1.通用Mapper

import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;/*** 基础mapper,实现增删改查,分页等基本功能** @param <T> 泛型参数* @author lw* @since 2019年2月14日 13:48:17*/
public interface BaseMapper<T> extends Mapper<T>, MySqlMapper<T> {
}

2.DO对象的Mapper继承通用Mapper

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;import java.util.List;/*** @author lw* @since 2019年2月14日 13:50:22*/
@Mapper
public interface AdminMapper extends BaseMapper<Admin> {@Select("select * from t_wj_seats_admin")List<Admin> list();void batchInsert(List<Admin> admins);
}

3.通用BaseService,这里只定义了批量插入

import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.util.List;/*** 通用业务逻辑层** @author lw* @version 1.0* @since 2019/2/21 上午 11:26*/
public class BaseService<T> {@Autowiredprivate BaseMapper<T> baseMapper;@Autowiredprivate SqlSessionFactory sqlSessionFactory;protected final Class<T> doClass;private Integer batchCommitCount = 10000;public BaseService() {doClass = getGenericParamClass();}public BaseService(Integer batchCommitCount) {this.batchCommitCount = batchCommitCount;doClass = getGenericParamClass();}@Transactional(rollbackFor = Exception.class)public boolean batchInsert(List<T> DOs) {if (CollectionUtils.isEmpty(DOs)) {return false;}SqlSession sqlSession = null;// 新获取一个模式为BATCH，关闭自动提交的sessiontry {sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);// 通过新的sqlSession获取MapperBaseMapper mapper = (BaseMapper) sqlSession.getMapper(getMapperClass());for (int i = 0, len = DOs.size(); i < len; i++) {mapper.insertSelective(DOs.get(i));if (i % batchCommitCount == 0 && i >= batchCommitCount) {// 提交,无法回滚sqlSession.commit();sqlSession.clearCache();}}sqlSession.commit();sqlSession.clearCache();} catch (Exception e) {// 回滚sqlSession.rollback();e.printStackTrace();return false;} finally {sqlSession.close();}return true;}/*** 获取对应的Mapper Class** @return class*/private Class getMapperClass() {Class<?> clazz = baseMapper.getClass().getSuperclass();try {Field h = clazz.getDeclaredField("h");h.setAccessible(true);Object hObject = h.get(baseMapper);Field mapperInterface = hObject.getClass().getDeclaredField("mapperInterface");mapperInterface.setAccessible(true);Object mapperObject = mapperInterface.get(hObject);Field name = mapperObject.getClass().getDeclaredField("name");name.setAccessible(true);Object nameValue = name.get(mapperObject);return Class.forName((String) nameValue);} catch (Exception e) {e.printStackTrace();}throw new RuntimeException("基础BaseService中无法获取mapper真实Class对象");}/*** 获取泛型参数Class对象** @return Class对象*/private Class<T> getGenericParamClass() {ParameterizedType genericSuperclass = (ParameterizedType) this.getClass().getGenericSuperclass();Type[] actualTypeArguments = genericSuperclass.getActualTypeArguments();return (Class<T>) actualTypeArguments[0];}
}

4.连接url加入rewriteBatchedStatements=true

jdbc:mysql://localhost:3306/db?useSSL=false&rewriteBatchedStatements=true

4.具体业务逻辑类继承通用Service,直接调用父类的批量方法就好


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import tk.mybatis.mapper.entity.Example;import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;/*** 管理员业务逻辑类** @author lw* @since 2019年2月14日 15:43:08*/
@Service
public class AdminService extends BaseService<Admin> {@Autowiredprivate AdminMapper mapper;@Transactional(rollbackFor = Exception.class)public void test() {List<Admin> admins = genList();long startTime = System.currentTimeMillis();batchInsert(admins);long endTime = System.currentTimeMillis();System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(new Date()) +"  baseService 批量插入" + admins.size() + "条 耗时(ms):" + (endTime - startTime));}private List<Admin> genList() {int len = 100;List<Admin> admins = new ArrayList<>(len);for (int i = 0; i < len; i++) {Admin admin = new Admin();admin.setUsername("测试" + i);admins.add(admin);}return admins;}
}

以上批量操作就完成啦

-------------------------------------------------------------------------------------------------------------------------------------------------

将BaseService的批量插入与使用xml的foreach进行批量插入进行性能对比

1.xml文件

<insert id="batchInsert" parameterType="java.util.List">insert t_admin (username)values<foreach collection="list" item="admin" separator=",">(#{admin.username})</foreach>
</insert>

2.测试类

 @Transactional(rollbackFor = Exception.class)public void test() {List<Admin> admins = genList();long startTime = System.currentTimeMillis();batchInsert(admins);long endTime = System.currentTimeMillis();System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(new Date()) +"  baseService 批量插入" + admins.size() + "条 耗时(ms):" + (endTime - startTime));}@Transactional(rollbackFor = Exception.class)public void test1() {List<Admin> admins = genList();long startTime = System.currentTimeMillis();mapper.batchInsert(admins);long endTime = System.currentTimeMillis();System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(new Date()) +"  xml foreach 批量插入" + admins.size() + "条 耗时(ms):" + (endTime - startTime));}private List<Admin> genList() {int len = 100;List<Admin> admins = new ArrayList<>(len);for (int i = 0; i < len; i++) {Admin admin = new Admin();admin.setUsername("测试" + i);admins.add(admin);}return admins;}

3.结果分析

性能对比表格
	5千条数据	一万五千条数据	十万条数据	一千万条数据
xml的foreach的批量插入耗时(ms)	153	388	2901	OutOfMemoryError
BaseService的批量插入耗时(ms)	391	1399	29035	时间太长,未计量,但确实可以插入