The Old Joel on Software Forum: Part 3 (of 5) - Design Question

Design Question - Database Queries

I have a thin client application that will bo doing database queries against Oracle.

The return results are over 500 for the query.

I want to dispay 10 results on a page in the browser at a time. And then the user can use the 'next' link to see the next 10 results. They can also hit the 'back' link on the page to view the previous 10 results.

I am using JSP and servlets - how is it best to do this?

Thanks,

JD

JD
Monday, December 8, 2003

Now, let me think for a minute, I read something about this somewhere...

JSP is Java Server Pages, right? It's like ASP, but it lets you run Java thingies in HTML files. Or was it Javascript? Is Java the same thing as Javascript, or it is something different? And "servlets" I'm pretty sure are little Java programs that run on the server, right?

So, I'm thinking you'd write a servlet to be run from a JSP page, or something like that... I'm a little fuzzy on the details...

Oh yeah, did I mention I'm having chest pains today? ... not sure if that's related... Where's that damn nurse with my halycon?

Grumpy Old-Timer
Monday, December 8, 2003

This all boils down to how big your base table might get.

You've got Oracle, so thankfully you can use ROWNUM.

The query you might like to try is a variant on this I would say:
select id from (select rownum as rowno,id from big_table where rowno<$end) where rowno>$start
(taken from a discussion at http://rhea.redhat.com/bboard-archive/acs_design/000aHd.html)

So you've got your query.

If you are using JSP and Servlets, then maybe you want to have a servlet that does the query and renders the result. If you were using Struts you'd have an Action that either did the query itself (or more likely used an API you'd written to return the results), put the results in a scope or ActionForm, then forward to a JSP to render.

Now your problem is this hits the database badly. If this is high volume, and the data itself isn't constantly changing, you'd do well to cache the results as this will take a tonne of pain away from your database. I've had good results with JCS, but your mileage may vary.

Konrad
Monday, December 8, 2003

Cache the results if you can, performing a subselection via rownum can really beat on your database if you have a lot of users or very active users.

Lou
Monday, December 8, 2003

Here is an example in ASP + MSSQL that you could use as an example, it uses a temp table to only return the records you want to display - http://www.4guysfromrolla.com/webtech/062899-1.shtml

Ben Richardson
Monday, December 8, 2003

You could create a view where you have the results ordered, and then select the subset from this view. This is pretty much the same as ROWNUM example, just not as vendor specific.

But as already said, if you can cache the whole set. Do that.

Juha
Tuesday, December 9, 2003

Jesus christ! Just pass the page number in the querystring and go to that page using the resultset's function. If that's not possible with the type of resultset you're using then just scroll thru the resultset by calling the next function (PAGE * number of records per page) times.

This is pretty standard - something any decent programmer can cough up in a minute - and you don't need oracle to do this.

Gamut
Friday, December 12, 2003