背景:java web 开发
问题描述:我在数据源1取得了业务id放在了List<String>里面,然后以List<String> businessidlist作为条件从另外一个数据源查询所需要的数据:
<select id="findAcceptNoListForQuanDianExport" resultMap="BaseResultArcrecordMap" parameterType="org.gecko.archives.entity.ArcrecordEntity">
select w.acceptno ,w.businessid from TB_BUSINESS_FLOW w
<trim prefix="where" prefixOverrides="and">
w.businessid IN
<foreach collection="list" open="(" separator="," close=")" item="item">
#{item}
</foreach>
</trim>
order by w.createtime desc
</select>
但是某一天测试人员跟我说导出excel表格报错了(这个导出excel的操作就包括了上面的查询)。
分析:刚开始以为是其他原因,后来详细查看的debug信息,发现:where in 附近发生错误。上网一查才知道原来where in ()括号里面的值不能超过1000个。因为查询优化器觉得这样的查询是非常糟糕的,但是没办法,我只能通过where in的方法进行查询。(所需的数据在不同的数据源里面,业务需求如此)。
思路:既然不能超过1000,那我就最多放1000条在where in 条件里面! 如果数据超过1000条,就分成几段,每一段不能超过1000。
着手:注:businessIdlist就是where in 里面的条件
/*获取上面结果的企业的businessid,然后在另外一个数据源取得每个企业对应的受理号*/
List<String> businessIdlist = new ArrayList<>();
for(ArcrecordEntity a : arcrecordEntityList){
businessIdlist.add(a.getBusinessId());
}
/**
* 由于where in()里面的数量不能超过1000条,所以对受理号分段查询
*/
List<List<String>> Colle_businessIdlist = new ArrayList<>();
int count = 0;
if (businessIdlist.size() > 1000){
count = businessIdlist.size()/1000;
if(businessIdlist.size()%1000 > 0){
count++;
}
for(int i =0;i<count;i++){
//每一次循环需要把1000条放进Colle_businessIdlist
List<String> temp_businessIdlist = new ArrayList<>();
for(int j =i*1000;j<businessIdlist.size();j++){
if (j == 1000*(i+1)){
Colle_businessIdlist.add(temp_businessIdlist);
break;
}
temp_businessIdlist.add(businessIdlist.get(j));
if (j == businessIdlist.size()-1){
Colle_businessIdlist.add(temp_businessIdlist);
}
}
}
}
//在另外一个数据源取得每一个企业对应的受理号
List<ArcrecordEntity> AcceptNoList = new LinkedList<>();
for (List<String> busiIdList : Colle_businessIdlist){
AcceptNoList.addAll(recodeService.findAcceptNoListForQuanDianExport(busiIdList));
}
代码写得很清楚了,先要知道数据要分成几段(也就是我上面的count),分成几段成为了for的循环次数,每一次循环把不大于1000条数据的List<String> temp_businessIdlist = new ArrayList<>()放进
List<List<String>> Colle_businessIdlist = new ArrayList<>();里面。其中要注意最后一段的时候
if (j == businessIdlist.size()-1){ Colle_businessIdlist.add(temp_businessIdlist); },最后一段可能没有1000条,所以用size()来截止并且把
List<String> temp_businessIdlist = new ArrayList<>()放进
List<List<String>> Colle_businessIdlist = new ArrayList<>();里面。
最后的循环查询也是很关键的:List的addAll的灵活运用!
List<ArcrecordEntity> AcceptNoList = new LinkedList<>();
for (List<String> busiIdList : Colle_businessIdlist){ AcceptNoList.addAll(recodeService.findAcceptNoListForQuanDianExport(busiIdList));
}