mysql java多线程并发插入数据_mysql函数大批量插入数据+java多线程并发案例

当前需要做个测试，mysql数据库需要5000万数据来完成性能测试，于是我们写了个函数来完成这个功能。

函数定义如下：

#批量插入数据库的函数

BEGIN

#定义需要插入数据库表中的字段；

DECLARE id BIGINT(20);

DECLARE phone_number varchar(11);

#定义变量控制插入数据库表的行数

DECLARE sid bigint(20) DEFAULT 1;

DECLARE count bigint(20);

declare total bigint(20) DEFAULT 50000000;

#开启循环判断

while sid < total do

SET count = sid + 100000; //每次插入数据库条数，防止条数太多提交失败；

#开始事务

start transaction;

WHILE (sid < count) DO //循环插入数据库数据

SET id = sid;

SET phone_number = CONCAT(1, ROUND(ROUND(RAND(),10)*10000000000));

INSERT INTO t_contact_phone(sid,phone_number)

VALUES(id,phone_number);

#累计行数

SET sid = sid+1;

END WHILE;

commit;  //提交事务

end while; //结束循环

END

执行上述函数即可创建5000万条数据在测试库用于测试。

当我们需要同时执行大批量操作，那如果依然顺序单线程执行，会比较耗时，为了提高效率，我们需要通过多线程异步方式来提高效率；

比方说大批量数据的同步方案，只能通过批量查询+多线程+异步的方式来完成。才能更短的时间内更高效的完成任务。

public classSynchronizationData {

private static finalLogger logger= LoggerFactory.getLogger(SynchronizationData.class);

@AutowiredprivateApplicationSumaryMapper applicationSumaryMapper;//需要同步的数据表(mybatis完成)

@AutowiredprivateContactPhoneMapper contactPhoneMapper;//需要插入的数据表一(mybatis完成)

@AutowiredprivateMiddleFieldMapper middleFieldMapper; //需要插入的数据表二(mybatis完成)

private staticExecutorService executor= Executors.newFixedThreadPool(2); //定义线程池用于多线程执行。

//需要同步总条数private longtotalNumber= 0;

//需要同步的最大进件id;private longmaxSid= 0;

//当前已经同步的sidprivate longcurrentSid= 0;

private longcurrentPage= 0;

private longmaxPage= 0;

public voidstartSynData() {

//获取当前系统中最大的sidmaxSid= applicationSumaryMapper.getMaxSid();

logger.info("需要同步的最大进件id(SID)为:", maxSid);

//根据sid获取总条数totalNumber= applicationSumaryMapper.getTotalNumber(maxSid);

logger.info("需要同步的进件总数为:", totalNumber);

maxPage= totalNumber/ 1000+ 1;

logger.info("最大页数为:", maxPage);

//判断能否开始执行，执行时间为晚上12点到早上8点booleansynStart = isSynStart(currentSid, maxSid);

if(synStart) {

//开始分页查询for(longi = 0; i <= maxPage;) {

ListcontactList = newArrayList();

ListmiddleList = newArrayList();

currentPage= i;

//查询需要同步的数据集

ListsynDataList = applicationSumaryMapper.getSynData(currentPage, currentSid, maxSid);

//遍历集合组装数据prepareDataForBatchInsert(contactList, middleList, synDataList);

i = batchAddData(i, contactList, middleList, synDataList);

}

} else{

//不符合同步条件，啥也不做}

}

@Transactional //通过事务控制同成功，同失败。private longbatchAddData(longi, ListcontactList, ListmiddleList, ListsynDataList) {

//异步批量插入contactListFuturecontactNumber = executor.submit(newcontactPhoneCallable(contactList));

//异步批量插入middleListFuturemiddleNumber = executor.submit(newmiddleFiledCallable(middleList));

try{

Integer number1 = contactNumber.get();

Integer number2 = middleNumber.get();

//如果都成功则继续if(number1 != -1&& number2 != -1) {

i++;

} else{

//失败则继续当前页执行}

} catch(Exception e) {

e.printStackTrace();

//异常并记录当前的sidlogger.info("异常最小的sid为:"+synDataList.get(0).getSid());

logger.info("异常最大的sid为:"+synDataList.get(synDataList.size()-1).getSid());

}

returni;

}

//遍历要同步的数据集合组装要批量插入的数据集合

private voidprepareDataForBatchInsert(ListcontactList, ListmiddleList, ListsynDataList) {

for(ApplicationSumary application : synDataList

) {

MiddleField middleField = newMiddleField(application.getSid(), application.getUnitAddressCode(), application.getCustunitAddress(), application.getRegistAddressCode(), application.getRegistAddress()

, application.getLiveAddressCode(), application.getLiveAddress(), application.getCustQq(), application.getCustWechatcode(), application.getAppStatus(), application.getAppStatusCode());

middleList.add(middleField);

//客户电话号if(application.getPhoneNubmer() != null) {

ContactPhone phoneForSelf = newContactPhone();

contactList.add(phoneForSelf);

};

//销售人员电话if(application.getSalesPhone() != null) {

ContactPhone phoneForSales = newContactPhone();

contactList.add(phoneForSales);

};

//家庭联系人电话if(application.getFamilyPhone() != null) {

ContactPhone phoneForFamily = newContactPhone();

contactList.add(phoneForFamily);

};

//紧急联系人电话if(application.getOtherContactphone() != null) {

ContactPhone phoneForOther = newContactPhone();

contactList.add(phoneForOther);

};

//工作证明人电话if(application.getFalyPhone() != null) {

ContactPhone phoneForFaly = newContactPhone();

contactList.add(phoneForFaly);

};

}

}

//判断是否可以开始同步，这里只允许晚上0点到早上八点允许执行

private booleanisSynStart(longcurrentSid, longmaxSid) {

Date date = newDate();

inthours = date.getHours();

if((hours >= 0|| hours <= 7) && currentSid < maxSid) {

return true;

} else{

return false;

}

}

//多线程执行任务一：给联系人表中批量插入数据

classcontactPhoneCallable implementsCallable{

privateListrunList;

publiccontactPhoneCallable(ListrunList) {

this.runList= runList;

}

@OverridepublicInteger call() {

try{

intaddNumber = contactPhoneMapper.batchAdd(runList);

returnaddNumber;

} catch(Exception e) {

return-1;

}

}

}

//多线程执行的任务二；给中间信息表中批量插入数据

classmiddleFiledCallable implementsCallable{

privateListrunList;

publicmiddleFiledCallable(ListrunList) {

this.runList= runList;

}

@OverridepublicInteger call() {

try{

intmiddleNumber = middleFieldMapper.batchAdd(runList);

returnmiddleNumber;

} catch(Exception e) {

return-1;

}

}

}

}

最后给出分页查询和批量插入的sql语句：

mybatis定义的方法：

ListgetSynData(@Param("currentPage") longcurrentPage, @Param("currentSid") longcurrentSid, @Param("maxSid") longmaxSid);

分页查询sql:

select

from t_application_sumary

where sid#{maxSid} and sid> #{currentSid} limit #{currentPage},#{currentPage+1000}

select>

mybatis定义的方法：

intbatchAdd(ListcontactList);

批量插入sql:

insert into t_contact_phone (sid,phone_type,phone_number,phone_name)

values

(#{item.sid,jdbcType=BIGINT}, #{item.phoneType,jdbcType=VARCHAR}, #{item.phoneNumber,jdbcType=char},

#{item.phoneName,jdbcType=VARCHAR})

foreach>

insert>