思路:
1、一次拿所有数据,sql性能会很慢,JVM内存也不够用
2、可以按天每次处理500条解决sql性能,每次修改1笔及时释放栈帧内存防止内存溢出
处理逻辑:第一个while按天处理,第二个while取当天的500笔未处理数据
public void updateAccountJournal(){
String startDate = "2018-01-01";
String endDate = "2021-02-21";
try {
while(true){
log.info("startDate=========={}",startDate);
while (true){
int i = toUpdateAccountJournal(startDate);
log.info("i=========="+i);
if(i<1){
break;
}
}
startDate = DateUtil.format(DateUtil.addDay(DateUtil.parse(startDate),1),DateUtil.YEAR_MONTH_DAY_PATTERN);
if(endDate.equals(startDate)){
break;
}
}
} catch (Exception e) {
log.info(e.getMessage(),e);
}
private int toUpdateAccountJournal(String startDate) {
List<Map<String,Object>> list = receivableSettInfoService.accountMessage(startDate);
if(ListUtil.isEmpty(list)){
return 0;
}
for (Map<String,Object> map : list) {
receivableSettInfoService.updatRelatedSerialNo(map);
}
return list.size();
}
<resultMap id="accountMessageMap" type="java.util.HashMap">
<result column="id" property="id" />
<result column="order_id" property="orderId" />
<result column="order_type" property="orderType" />
</resultMap>
<select id="accountMessage" parameterType="java.lang.String" resultMap="accountMessageMap" >
select aj.id , at.order_id, at.order_type
from account_trans at,
account_journal aj
where at.id = aj.trans_id
and aj.related_serial_no is null
and at.order_type in (2901,2903,2101,2102,2002,2011,2012,2020,2021,203,2003,2013,2014,2601)
and aj.create_time > to_date( #{startDate,jdbcType=VARCHAR}, 'yyyy-MM-dd')
and aj.create_time <![CDATA[ < ]]> to_date(#{startDate,jdbcType=VARCHAR}, 'yyyy-MM-dd')+1
and rownum <![CDATA[ < ]]> 501
</select>
看生产日志跑起来还不错