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

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

2. OSCON 2004 Summary page for Server side patterns session

3. Abstract Page Data Pattern: Code Examples