File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes using jdbc to display search result Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "using jdbc to display search result" Watch "using jdbc to display search result" New topic
Author

using jdbc to display search result

sandy sean
Ranch Hand

Joined: Dec 30, 2010
Posts: 69
Hi!

i m implementing pagination type thing using jdbc to display search results.

Suppose i have 1000 records in database. I want to access first 500 records and display them. then on clicking "next" another 500 records would be fetched from database.

can anyone provide any hint for this???

thanks...
Philip Grove
Ranch Hand

Joined: Aug 18, 2009
Posts: 68

SQL LIMIT guarantees at most a given number of results and you can control the offset. Just be aware that it executes the entire statement and then limits the number of returned results. So if you have 1,000 results and want them 500 at a time, it executes the entire statement twice. With statements that takes a long time to execute you are probably better off getting all the results at once and do the buffer work yourself.
sandy sean
Ranch Hand

Joined: Dec 30, 2010
Posts: 69
What is the purpose of resultset.setFetchSize()??

can this be used to implement this?
Philip Grove
Ranch Hand

Joined: Aug 18, 2009
Posts: 68

Now we're getting into the obscure corners of JDBC, because I suspect that it basically uses a LIMIT keyword on the statement. But judging from the documentation I think it can be used.

In a time-space trade off involving JDBC I always recommend time winning and the extra space be used. You also run the risk of people clicking "next" and waiting a long time while the code is getting the rest of the results.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18643
    
    8

sandy sean wrote:What is the purpose of resultset.setFetchSize()??


The purpose is what the API documentation says; that's what the documentation is for.

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for this ResultSet object.


That's kind of obscure, especially to people who are reading it with a desire about what they want it to mean, so let's read what it actually says.

It's talking about a ResultSet object. So this might be the result of a query which returns a couple of hundred thousand rows, let's say.

And it's talking about fetching rows from the database "when more rows are needed". What could that mean? When the code says "get me the next row", and that row hasn't been fetched from the database, then more rows are needed. That's what it means.

So the default is for the ResultSet to fetch all of those couple of hundred thousand rows. This could blow out your memory. So you could tell the ResultSet to only get one thousand at a time, as it needs them, so you don't run that risk.

Now, does it say somewhere that your program will be notified in some way when the next thousand are fetched? Nope. To your code the ResultSet acts identically whether the fetch size is set or not, except that records will move from the database to your program at different times.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: using jdbc to display search result