title: 批量查询DB的实现 tags:
- mybatis
- 批量
- 主键
- aop
- Batch categories: mybatis date: 2017-08-04 18:18:53
背景
由于业务量的上涨,门店转入业务数据的需求,催生了一批excel导入系统的需求。但是由于原先excel导入的实现基于行的模式(一行一行导入)
导致系统导入的效率很低,经常出现超时等等问题。
解决方案
1. 将多数的导入由一行转为批量导入,那么比如一次插入1000条数据。当然存在一些业务上的check(重复等等,不合理值等等),暂且不表。
比如
<insert id="addMemberCardInBatchModel" parameterType="com.air.tqb.model.TaMemberCard">
insert into
ta_member_card(pk_id,name,memo,card_type,prestore_amount,validate,permanent,use_type,amount,vip_money,favourable_money,total_number,creationtime,creator,id_own_org)
VALUES
<foreach collection="list" item="card" separator=",">
(CAST(#{card.pkId} as unsigned),#{card.name},#{card.memo},#{card.cardType},#{card.prestoreAmount},#{card.validate},#{card.permanent},#{card.useType},#{card.totalMoney},#{card.vipMoney},#{card.favourableMoney},#{card.totalNumber},now(),#{card.creator},CAST(#{card.idOwnOrg} as unsigned ))
</foreach>
</insert>
复制代码
问题
-
部分自动增加表在插入db需要用到业务主键,如何批量生成pk?(batchExecutor存在问题,可以参考https://my.oschina.net/zudajun/blog/674946 本文使用的是ReuseExecutor)
-
部分表的主键是uuid,那么如何批量生成uuid?
-
由于sql过长或者过于复杂导致出现一些异常,比如执行时间超时,栈溢出等等 如下
Packet for query is too large (2302364 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable
Thread stack overrun: 180624 bytes used of a 196608 byte stack, and 16000 bytes needed.
实现方案
- 系统中使用mybatis作为orm框架。那么mybatis通常我们使用selectKey或者jdbc3Key作为主键设置。通常可能如下
<selectKey keyProperty="pkId" resultType="String" order="BEFORE">
select Uuid_short() from dual
</selectKey>
<insert useGeneratedKeys="true" keyProperty="id" keyColumn="id">
复制代码
其中selectKey不可以生成多主键(排除)
剩下的在mybatis3.3.0不支持多主键(bug)后已经修复 (3.3.1) When insert a list or array,resolve the useGeneratedKeys error.
- 我们可能需要批量生成uuid了,那么方案可能如下
<select id="getUUidInBatch" resultType="java.lang.String" flushCache="true">
<foreach collection="list" separator=" union " >
select Uuid_short()
</foreach>
</select>
复制代码
当然更好的方案使用存储过程等根据次数要求生成 后面补上方案(只是示例)
3.对于批量的问题导致出现问题,我们考虑希望开发者在无感知的情况下完成数据的插入或者查询。那么考虑如下切面
package com.air.tqb.aop;
import com.air.tqb.annoate.Batch;
import com.google.common.collect.Lists;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.core.Ordered;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import java.util.Collection;
import java.util.List;
/**
* Created by qixiaobo on 2017/07/31.
*/
@Aspect
@Component
@Order(Ordered.HIGHEST_PRECEDENCE + 3)
public class BatchAspect {
private static final Log logger = LogFactory.getLog(BatchAspect.class);
@Pointcut("@annotation(com.air.tqb.annoate.Batch)&& @annotation(batch) ")
public void batchMethod(Batch batch) {
}
@Pointcut("execution(public int com.air.tqb.service..*.*(..)) && batchMethod(batch)")
public void intReturnMethod(Batch batch) {
}
@Pointcut("execution(public java.util.List com.air.tqb.service..*.*(..)) && batchMethod(batch)")
public void listReturnMethod(Batch batch) {
}
@Pointcut("execution(public void com.air.tqb.service..*.*(..)) && batchMethod(batch)")
public void voidReturnMethod(Batch batch) {
}
@Pointcut("args(java.util.List)&& args(list)")
public void listArgumentMethod(List list) {
}
@Around("listReturnMethod(batch)&&listArgumentMethod(list)")
public Object batchExecuteReturnList(ProceedingJoinPoint joinPoint, Batch batch, List list) throws Throwable {
int batchSize = batch.value();
List rltList = Lists.newArrayListWithExpectedSize(list.size());
List<List> partitions = Lists.partition(list, batchSize);
for (List partition : partitions) {
Object rlt = joinPoint.proceed(new Object[]{partition});
rltList.addAll((Collection) rlt);
}
return rltList;
}
@Around("intReturnMethod(batch)&&listArgumentMethod(list)")
public Object batchExecuteReturnInt(ProceedingJoinPoint joinPoint, Batch batch, List list) throws Throwable {
int batchSize = batch.value();
int rltTotal = 0;
List<List> partitions = Lists.partition(list, batchSize);
for (List partition : partitions) {
int rlt = (int) joinPoint.proceed(new Object[]{partition});
rltTotal += rlt;
}
return rltTotal;
}
@Around("voidReturnMethod(batch)&&listArgumentMethod(list)")
public Object batchExecuteReturnVoid(ProceedingJoinPoint joinPoint, Batch batch, List list) throws Throwable {
int batchSize = batch.value();
List<List> partitions = Lists.partition(list, batchSize);
for (List partition : partitions) {
joinPoint.proceed(new Object[]{partition});
}
return null;
}
}
复制代码
其中Batch注解如下
package com.air.tqb.annoate;
import com.air.tqb.common.AppConstant;
import java.lang.annotation.*;
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface Batch {
int value() default AppConstant.DEFAULT_DATA_BASE_BATCH_SIZE;
}
复制代码
只要在方法的签名上声明如下
|
如上可以完成批量插入。