This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes JDBC and the fly likes Fetching Rows From ResultSets (JDBC). Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Fetching Rows From ResultSets (JDBC)." Watch "Fetching Rows From ResultSets (JDBC)." New topic
Author

Fetching Rows From ResultSets (JDBC).

Jaynul Dewani
Greenhorn

Joined: Aug 02, 2003
Posts: 1
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30392
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

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?


Groovy
Wayne L Johnson
Ranch Hand

Joined: Sep 03, 2003
Posts: 399
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

Joined: Aug 18, 2003
Posts: 218
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

Joined: Sep 03, 2003
Posts: 399
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30392
    
150

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

Joined: Aug 18, 2003
Posts: 218
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
 
jQuery in Action, 2nd edition
 
subject: Fetching Rows From ResultSets (JDBC).