best practice for dividing search results into pages

[Edmund Castermund]

Hi,
I have an app that goes into a db via jdbc to grab a list of objects and then a servlet presents them in the form of a table.

The app shows the search results in pages....the first 25 results, then click next, 26-50 and so on. Currently, every time the user clicks the link for the next page, a new search is generated returning the entire result set. I realize this is a terrible way to do it. My current plan is to send a page num to the function that calls the sql statement and adjust it to get the results I want...but I was wondering, since this is a ubiquitous scenario, what is the best practice for this?


Also, currently the app gets a list of objects and pulls all the data for those objects whether they are used or not. In terms of scalable db searching code, what is the best practice for this? any good books re: java -> db you can recommend?
bp

[Edmund Castermund]

ok,
answering my own question here. Came across this tutorial:
http://theopentutorials.com/examples/java-ee/jsp/pagination-in-servlet-and-jsp/

This will make it pretty easy to chunk up the search results:

select * from table limit 0, 25; select * from table limit 26, 25; select * from table limit 51, 25;

[Bear Bibeault]

There's info on this in the JspFaq.

And yes, making the database do the work is the right approach.

[Wendy Gibbons]

Edmund Castermund wrote:ok,
answering my own question here. Came across this tutorial:
http://theopentutorials.com/examples/java-ee/jsp/pagination-in-servlet-and-jsp/

This will make it pretty easy to chunk up the search results:

select * from table limit 0, 25; select * from table limit 26, 25; select * from table limit 51, 25;

limit is db dependant, This is mysql isn't it?

[Bear Bibeault]

Yes, it is DB-dspecific syntax. Using JPA or Hibernate eliminates the dependence on a particular SQL dialect.

[Bill Clar]

I was oblivious to the fact that Informix had such a feature:

SELECT SKIP 0, FIRST 10 reasons FROM idiots WHERE name = 'Bill Clar'

Time to do some refactoring.