• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Fetching Rows From ResultSets (JDBC).

 
Jaynul Dewani
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Rightnow we are facing one problems fetching rows from resultset.
In our application, we have search page which comes up with
more than 2000 of records but on the screen we are displaying first 10
records and then we put the paging.. we are caching resultset so for each page we are executing sql to get the result and skip the rows based on the page user selects..
Now question, is it possible that we can say fetch first 20 rows or fetch rows from 21 to 30.. we are using DB2 6.0.
Thanks.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We had this problem for DB2 as well. There is a way to get certain rows, but it involves doing a nested query. It would be more efficient to just get the whole thing and skip the beginning data.
Alternatively, if possible, you can store the primary key for the 2000 records and query based on that the next time.
 
Pradeep bhatt
Ranch Hand
Posts: 8933
Firefox Browser Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Alternatively, if possible, you can store the primary key for the 2000 records and query based on that the next time.

What if new rows are added or rows pointed by cached primary keys are removed?
 
Wayne L Johnson
Ranch Hand
Posts: 399
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I've tried a hybrid approach for this situation. There are now about 2500 rows in a table, displayed 25 at a time with FWD/BACK buttons. On the server side I am caching 100 rows of data. So the first time they hit the database I get the rows and pass back 1-25. When the user does a FWD I simply return 26-50. Once they go forward to page 5 I query the database again and get rows 101-200 (by ignoring the first 100 rows). It take a little extra work to keep track of the "virtual" page to "real" page mapping, but it's very straightforward.
This is a good compromise for our application, and it cuts down on my database calls. There is frequent enough refresh from the database so nothing gets too stale.
I allow users to sort ascending/descending on any of the 8-10 columns returned, so every time that happens I also have to refresh the data. This requirement precludes me from storing the keys, since rarely is the data returned based on the primary key.
 
Sainudheen Mydeen
Ranch Hand
Posts: 218
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Wayne L Johnson
Originally posted by Wayne L Johnson:
On the server side I am caching 100 rows of data.

Do you use any logic in your query to get the first 100 rows then 100 and so on. I read many discussions in this forum and still I could not come to conclusion on this. As you said at first attempt, my query may return a resultset of 100 rows which matches the codition in the query. Lets say, out of first 200 rows in the actual table we are getting 100 rows which match the condition. So at second attempt we have to run our query from 201th row of the table. Do you use ROW NUMBER to achieve this? How you are doing this? Can you please explain?
-Sainudheen
 
Wayne L Johnson
Ranch Hand
Posts: 399
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's actually very simple. The first time I hit the database I get rows 1-100, which allows me to handle pages 1-4 w/out hitting the database again.
When the user goes to page 5, then I query the database again. I throw away the first 100 rows, and then save rows 101-200, which allows me to handle pages 5-8. I don't do anything with ROW NUMBER.
For our application it's a good trade-off, saving me from hitting the database as often. However when I do query the database, I use the brute-force method of reading past the rows I don't want and only saving those I do.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Pradeep,
What if new rows are added or rows pointed by cached primary keys are removed?

It depends on the business requirement. If you get rows 1-20 using true dynamic paging and row 5 disappears, what do you get the next time? Rows 21-40 or rows 20-39? Logic would say rows 21-40, but then you mis the original row 21 because it is now row 20.
Wayne's solutions sounds like a good compromise although it doesn't resolve this problem.
 
Sainudheen Mydeen
Ranch Hand
Posts: 218
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Wayne L Johnson:
I throw away the first 100 rows, and then save rows 101-200, which allows me to handle pages 5-8. I don't do anything with ROW NUMBER.

Thanks Wayne. I found a logic to achieve that.
-Sainudheen
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic