我有两个ID,对应于数据库中的一系列记录。我想以1000的批量处理记录。假设两个ID是51234和59265。
我从下面的for循环开始
for(int i = 51234; i < 59265; i= i+1000) {
select * from database where id between i and i+1000;
//do the stuff
}
现在,直到59234th Record,这效果很好,最后31个记录呢?我也想在此运行中处理它们。
我可能可以检查每次迭代中i的值是多少,并检查在查询中添加1000是否超过最大ID并调整SQL查询。那是唯一的方法吗?对于循环是正确的方法吗?
int batchSize=1000;
for(int i = 51234; i <= 59265; i+=batchSize) {
select * from database where id between i and Math.min(i+batchSize, 59265);
//do the stuff
}
样本输出:
between 51234 and 52234 actualBatch=1000
between 52234 and 53234 actualBatch=1000
between 53234 and 54234 actualBatch=1000
between 54234 and 55234 actualBatch=1000
between 55234 and 56234 actualBatch=1000
between 56234 and 57234 actualBatch=1000
between 57234 and 58234 actualBatch=1000
between 58234 and 59234 actualBatch=1000
between 59234 and 59265 actualBatch=31
因为是包容性的,因此您在每批上都有重叠。您可以将其更改并使用不平等来解决此问题:
int batchSize = 1000;
int start=51234;
int end=59265;
for(int i = start; i < end + 1; i+=batchSize) {
select * from database where id >= i and id < Math.min(i+batchSize, end);
}
样本输出:
id >= 51234 and id < 52234 actualBatch=1000
id >= 52234 and id < 53234 actualBatch=1000
id >= 53234 and id < 54234 actualBatch=1000
id >= 54234 and id < 55234 actualBatch=1000
id >= 55234 and id < 56234 actualBatch=1000
id >= 56234 and id < 57234 actualBatch=1000
id >= 57234 and id < 58234 actualBatch=1000
id >= 58234 and id < 59234 actualBatch=1000
id >= 59234 and id < 59266 actualBatch=32
您可以使用do-while
循环:
int i = 51234;
do {
// call DB: select * from database where id between i and i+1000
// do stuff
i = i + 1000;
} while (i < 59265);
一个简单的解决方案是打破循环并分别执行其余ID。
int i = 0;
for(i = 51234; i < 59265; i= i+1000) {
doAllStuff(i, 1000);
if(59265-i < 1000)
break;
}
doAllStuff(i, 59265-i);
doallstuff((将像下面类似:
public void doAllStuff(int x, int y) {
select * from database where id between x and y;
//do the stuff
}