项目开发使用的springboot+mybatis+mySql,前期设计问题有个路段与路口的关系表没建,只有一个表里,路口id对应多个路段id,但是多个路段id用“,”隔开存在一个字段里,导致后期查询很繁琐,老板让新建个表批处理下,路口数据三万多条,对应的路段数据应该有十几万条
service层代码:
// 先查出路口与路段信息
List<Map<String, String>> oldMappingList = mapper.getRidSegIdMapping();
// 建立线程池,开启线程批处理(本猿的电脑四核八线程,就启8个线程)
ExecutorService executor = Executors.newFixedThreadPool(8);
for(int i=1;i<9;i++){ executor.submit(new MappingInsertCallable(oldMappingList.subList((i-1)*5000,i*5000),i)); }
多线程类(上文for循环中使用的MappingInsertCallable)
public class MappingInsertCallable implements Callable<Integer> { private List<Map<String, String>> runList; private Integer treadNum; private static ApplicationContext applicationContext; private CityHomeDataMapper mapper = applicationContext.getBean(CityHomeDataMapper.class); public MappingInsertCallable(List<Map<String, String>> runList, int treadNum) { this.runList = runList; this.treadNum = treadNum; } @Override public Integer call() { try { System.out.println("start threadId=="+treadNum+"---"+System.currentTimeMillis()); runList.forEach(item -> { String[] ridArr = item.get("ridseq").split(","); System.out.println(ridArr); mapper.insertRidSegIdMapping(item.get("segId"),ridArr); }); System.out.println("end threadId=="+treadNum+"---"+System.currentTimeMillis()); return 1; } catch (Exception e) { return -1; } } }
写这个多线程类的时候遇到了一个小坑,因为用了springBoot,使用mapper就直接写了注入:
@Autowired private CityHomeDataMapper mapper;
这样是没效果的,请教了同事改为:
private CityHomeDataMapper mapper = applicationContext.getBean(CityHomeDataMapper.class);
完美运行,本来二十分钟没跑完的,多线程五分钟解决~
下面加上mapper下的代码:
int insertRidSegIdMapping(@Param("segId")String segId, @Param("ridArr") String[] ridArr);
对应的mybaitis sql文件:
<insert id="insertRidSegIdMapping"> insert into table_name values <foreach item="item" index="index" collection="ridArr" open="" separator="," close=""> (#{segId},#{item}) </foreach> </insert>