using jdbc to display search result

[sandy sean]

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]

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]

What is the purpose of resultset.setFetchSize()??

can this be used to implement this?

[Philip Grove]

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]

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.