• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Pagination by fetching batch of records in Java

 
Rithanya Laxmi
Ranch Hand
Posts: 185
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I want to build a pagination by fetching the records in small batches in java using JDBC with Oracle 11g DB , I am using the below query :-

Select * from (Select * from <tablename> where <condition> order by id) where rownum > ? and rownum < ?

Please let me know how can get the values in small batches in java? I have set the batchsize as 100. Hence it will fetch 1 - 100 records, how I can get the next set of records like 101-200, 201-300, 301-400, etc. How I can write the incremental logic in java to get the next set of batches? Please provide some examples on this which will be of great help.

Thanks.
 
Campbell Ritchie
Sheriff
Posts: 51461
87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I thought you can set a batch size whenever you execute a query returning values.
You might do well to iterate the result set 100 rows at a time, instead.
 
Rithanya Laxmi
Ranch Hand
Posts: 185
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Campbell. I am setting batch size as "100" , hence we will get the result set with 1-100 records initially, how I can write the incremental logic to get the next set of records like 101-200, 201-300, 301-400 in java ? Please Clarify.
 
Campbell Ritchie
Sheriff
Posts: 51461
87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Don't know. I haven't used JDBC for long enough to have forgotten most of it. Look in the Java® Tutorials. Maybe result sets, maybe row set? Don't know.
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1 way that coming to me first is
After getting 100 records you can set the value in jsp,say 100.
and when you click next, you can get the value(100) in servlet and fetch 101-200.
set value in jsp:200.
click next-> 201-300 and so on.
 
Rithanya Laxmi
Ranch Hand
Posts: 185
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tried something like the below which always fetches records like 0-100, 100-200, 200-300, here there is duplication where the record 100,200,300 is fetched again how we can avoid it?

PreparedStatement preparedStatement= connection.createStatement(sql);
preparedStatement.setLong(1, batchCounter*batchSize);
preparedStatement.setLong(2, (batchCounter+1)*batchSize)

batchCounter will be incremented in the loop and batchSize set to 100. Please clarify.
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And I guess rownum is not the way in oracle to fetch records batchwise.

Above query will never return any records regardless how many records exist in table.
For more information how rownum works you can go through oracle site.
http://docs.oracle.com/cd/B12037_01/server.101/b10759/pseudocolumns008.htm
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Begin batch counter from 1, not from 0.
 
Rithanya Laxmi
Ranch Hand
Posts: 185
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We cannot set the counter as 1 , where it will take 1*100 = which is 100 - 200 records initially instead of 1-100?
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

set counter in jsp.
Next time counter will be 201 and so on.
There are different logics for pagination. This is very basic one.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic