Displaying a result of a query from the database in increments

Question

I want to display a list of all the users in my site but I only want to display 10 people per age. I don't know how exactly to do this. I know how to do it by just displaying all the users in one page but that's not very good is it?

If I do it with the code I have now, it will only get the first ten users over and over again. I want to be able to get all the users for a one time query, store it globally and then move through the list retrieving the next 10 and so on for display.

I am developing on appengine using Java and the Spring Framework some of the solutions I have been thinking about,

1. Store in the session and go through the list (very bad I guess)
2. hand it to the JSP, specifically to one of the scopes, page, request etc. But I think request will not work.
3. Look for a Spring controller that can handle this.

Answer 1

Generally speaking, you would use a form variable on your page (via GET or POST) called 'page', which would be a number. When you receive that in the servlet you would calculate a range based on the page number and configured rows per page.

Take a look at Paging through large datasets (yes it's Python but the same principles apply) and Queries and Indexes from the Google App Engine documentation.

Answer 2

Take a look at http://valuelist.sourceforge.net/

Answer 3

If you keep page size at 10 then you can retrieve your 10 users per age group for each page based on page number:

SELECT TOP 10 users FROM myusers 
 WHERE AGE = function(page_number) 
 ORDER BY some_ordering

I hope that JPA + appengine support such type of query.

Answer 4

Some database engines provide handy extensions to SQL for just this purpose. Like, in MySQL you can say something like "select ... whatever ... limit 50,10", where "50" is the row to start with and 10 is the number of rows to retrieve. Then on your display page you simply put next and previous buttons that pass the appropriate starting row number back to the server for the next run at the query.

If the SQL engine you're using has no such handy function, then you have to build an query-specific "where" clause based on the sort order.

To take a simple case, suppose in your example you are displaying the records in order by "user_name". You can use Statement.setMaxRows(10) to limit any queries to 10 rows. Then on your first call you execute, say, "select ... whatever ... from user order by user_name". Save the last user_name found. In your next button, you pass this user_name back to the server, and the query for the next call is "select ... whatever ... from user where user_name>'xxx' order by user_name", where 'xxx' is the last user_name from the previous call. Do the setMaxRows again so you are again limited to 10 rows of output. You can then let the user step through the entire output this way.

Letting the user go backwards is a bit of a pain. I've done it by keeping a table in a session variable with the starting key value for each page.