Consider the following URL
http://host/akc/show?
url=blogsURL
&ownerUserId=satya
&publicitemsloop_controlstring=1,30
Show blogs for satya starting at 1 and 30 of those.
Consider the data definition for this page
#define the url and template
blogsURL=aspire:\\reports\\blogs\\blogs.html
blogsURL.formHandlerName=BUFH
Request.BUFH.classname=com.ai.htmlgen.DBHashTableFormHandler1
#Get first name, last name for the user etc
request.BUFH.maindatarequest.classname=com.ai.db.DBRequestExecutor2
request.BUFH.maindatarequest.db=reportsDB
request.BUFH.maindatarequest.stmt=\
\
select first_name as user_first_name \
,last_name as user_last_name \
,email as user_email \
from users \
where user_id = {ownerUserId.quote}
#Get all the blogs
request.BUFH.publicitemsloop.class_request.className=com.ai.htmlgen.RandomTableHandler6
request.BUFH.publicitemsloop.query_request.className=com.ai.db.DBRequestExecutor2
request.BUFH.publicitemsloop.query_request.db=reportsDB
request.BUFH.publicitemsloop.query_request.stmt=\
SELECT * \
FROM filed_items AS fi, \
reports AS r, \
folders AS f \
WHERE 1=1 \
And fi.item_id=r.report_id \
And fi.folder_id=f.folder_id \
and f.public='Y' \
and f.owner_user_id={ownerUserId.quote} \
ORDER BY r.last_updated_on DESC
The work horse select
SELECT * \
FROM filed_items AS fi, \
reports AS r, \
folders AS f \
WHERE 1=1 \
And fi.item_id=r.report_id \
And fi.folder_id=f.folder_id \
and f.public='Y' \
and f.owner_user_id={ownerUserId.quote} \
ORDER BY r.last_updated_on DESC
But this will return all the rows in the database. That will be too many. We only want 1 to 30
You can do this in Oracle
SELECT * \
FROM filed_items AS fi, \
reports AS r, \
folders AS f \
WHERE 1=1 \
And fi.item_id=r.report_id \
And fi.folder_id=f.folder_id \
and f.public='Y' \
and f.owner_user_id={ownerUserId.quote} \
and rowid between 1 and 30
ORDER BY r.last_updated_on DESC
Well not every database is oracle
A decorator to the rescue
#decorator
request.BUFH.publicitemsloop.class_request.className=\
com.ai.htmlgen.RandomTableHandler6
#work horse
request.BUFH.publicitemsloop.query_request.className=\
com.ai.db.DBRequestExecutor2
request.BUFH.publicitemsloop.query_request.db=reportsDB
request.BUFH.publicitemsloop.query_request.stmt=\
SELECT * \
FROM filed_items AS fi, \
reports AS r, \
folders AS f \
WHERE 1=1 \
And fi.item_id=r.report_id \
And fi.folder_id=f.folder_id \
and f.public='Y' \
and f.owner_user_id={ownerUserId.quote} \
ORDER BY r.last_updated_on DESC
DBRequestExecutor2 executes a jdbc cursor. It does not retrieve any rows until it was asked to. RandomTableHandler6 is a special decorator that knows that the loop name is "publicitemsloop". It looks for a control string off of that called "_controlstring". It will advance the cursor to that window and only reads those rows. It is obviously quite efficient in the front portions of the dataset and you hope the user doesn't want to see the last set. The additional assumptions is that the data doesn't change beween subsequent requests such as
http://host/akc/show?
url=blogsURL
&ownerUserId=satya
&publicitemsloop_controlstring=30,60
A bit of javascript on the browser
Figuring out the next set of rows needed
function gotoNext()
{
controlString = "1,30";
csArray = controlString.split(",");
begin=csArray[0];
span=csArray[1];
iSpan = parseInt(span);
iBegin = parseInt(begin);
newBegin = iBegin + iSpan;
newControlString = "" + newBegin + "," + span;
url="/akc/servlet/DisplayServlet?url=blogsURL";
url += "&ownerUserId=aspire";
url += "&publicitemsloop_controlstring=" + newControlString;
document.location=url;
}
Writing the next, next buttons depending on the data availability
function writeNext()
{
available = "{{publicitemsloop_data_available}}";
if (available == "true")
{
document.write('<p><a href="javascript:gotoNext()">..More</a>');
document.write('<hr>');
}
}
References
1. General Introduction to other Server side Patterns in this series