select rows in batch of 5 from resultset

[nikki sinha]

i have used a select command in my java program and stored its value in the result set. now while looping in the resultset i want to use a select command which will select the first 5 lines of the resultset and insert into other table. for the second time, it should select the next 5 lines and insert into the table. and for the third time, so on..

Statement s = connection.createStatement();
s.executeQuery("Select * from table1");
ResultSet res = s.getResultSet();
while(res.next()){
 // here i want to select the first 5 lines of the result set and insert in the second table
}

[Rakesh K. Cherukuri]

It would have been better if you can mention what solution(s) you have tried.

Coming back to the question, having an inner loop with a count of 5 should do the trick. Actually, in such cases the ResultSet support is limited i believe. Using other collections should make it easier.

[nikki sinha]

Rakesh K. Cherukuri wrote:It would have been better if you can mention what solution(s) you have tried.

Coming back to the question, having an inner loop with a count of 5 should do the trick. Actually, in such cases the ResultSet support is limited i believe. Using other collections should make it easier.

Thank you for a quick reply,
i have tried it doing this way:

Statement s = connection.createStatement();   // connection object of first database 
s.executeQuery("Select * from table1"); 
ResultSet res = s.getResultSet();
Statement s = conn.createStatement ();    // connection object of second database
while(res.next()){ 
  while(res.next()) && (res.getRow()%5) !=0 ){
      String sql = insert statement
      s.addBatch(sql);
     }
   s.executeBatch();
}

this code selects the rows in alternate manner.... i want to get frist 5 in one batch then secon 5 in second batch.

[Rakesh K. Cherukuri]

Problem with the posted code is calling res.next() repetitively. Thing is each call to res.next() moves cursor (ResultSet internal implementation) one step ahead. A general rule is to associated each call to next() with a read of the row.

Try it this way

        boolean b = res.next();
        while(b){   
        	  while((b) && (res.getRow()%5) !=0 ){  
        	      // read the data from res and maintain it for the inserts
        		  b= res.next();
        	  }  
        	  s.executeBatch();  
        } 

Caution : code is not a tested one. Expect corner case issues

[Martin Vajsar]

Are you doing this to speed things up?

If the two tables are on the same database, and you don't do any complicated calculations on them, you could use a single SQL command:
insert into target(column1, column2, column3) select column_x, column_y, column_z from source where ...
That's the fastest way to do it. The data won't even travel over the network from the database to wherever your Java code runs.