CODEWITHSUNDEEP
javamysqlloops
arjunj
arjunj

Reputation: 1526

For loops in java over a range of records in a database

I have two id's which correspond to a range of records in a database. I want to process the records in batches of 1000. Lets say the two ids are 51234 and 59265. I would like to loop through this and make sure all records are processed.

I start with a for loop as below

for(int i = 51234; i < 59265; i= i+1000) {

select * from database where id between i and i+1000;
//do the stuff
}

Now this works fine till 59234th record, what about the last 31 records? I would like to process them as well in this run.

I probably can check whats the value of i in each iteration and check if adding 1000 to the query exceeds the max id and adjust the sql query. Is that the only way? Is for loop the right approach here?

Upvotes: 0

Views: 1664

Answers (3)

Brandon Kearby
Brandon Kearby

Reputation: 603

   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
    }

Sample Output:

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

Since between is inclusive, you have overlap on each batch. You can change it up and use inequalities to get around this:

 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);
 }

Sample output:

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

Upvotes: 5

Drazen Nikolic
Drazen Nikolic

Reputation: 526

You can use do-while loop:

int i = 51234;
do {
  // call DB: select * from database where id between i and i+1000
  // do stuff
  i = i + 1000;
} while (i < 59265);

Upvotes: 1

Jayant Narwani
Jayant Narwani

Reputation: 101

A simple solution for this would be to break for loop and execute the remaining ids separately. 

int i = 0;
for(i = 51234; i < 59265; i= i+1000) {
    doAllStuff(i, 1000);
    if(59265-i < 1000)
        break;
}
doAllStuff(i, 59265-i);

doAllStuff() would be something like below:

public void doAllStuff(int x, int y) {
    select * from database where id between x and y;
    //do the stuff
}

Upvotes: -2

Related Questions