Since there are over a hundred FAQs, you obviously will not want to return all of them at once. You can set up pagination in an XSQL custom action handler that calls a Java class. In this case, we have created paging.java, which accepts parameters from the XSQL page to set up the number of rows per page, rows-per-page, the URL for the page to invoke, url-pagename, the next page parameter name, p, and the parameters to pass in the URL, url-params. The following is a Java code fragment that performs the pagination:
public class Paging extends XSQLActionHandlerImpl {
private static final String PAGE_PARAM_NAME = "p";
private static final String ROWSPERPAGE = "rows-per-page";
private static final String TARGETPAGEARGS = "url-params";
private static final String PAGENAME = "url-pagename";
public void handleAction(Node root) throws SQLException {
XSQLPageRequest req = getPageRequest();
Element actElt = getActionElement();
// Get the count query from the action element content
String query = getActionElementContent();
// Get the number of rows per page, defaulting to 10
long pageSize = longVal(getAttributeAllowingParam(ROWSPERPAGE,actElt)
,10);
long totalRows = longVal(firstColumnOfFirstRow(root,query),0);
long curPage = longVal(variableValue(PAGE_PARAM_NAME,actElt),1);
// Get the name of the current page to use as the target
//String pageName = curPageName(req);
// Get any URL param names that need to be echoed into paging URL's
String pageArgs = getAttributeAllowingParam(TARGETPAGEARGS,actElt);
String pageName = getAttributeAllowingParam(PAGENAME,actElt);
// Calculate the total number of pages
long totalPages = totalRows / pageSize;
long fract = totalRows % pageSize;
if (fract > 0) totalPages++;
// Make sure current page is between 1 < cur < totalPages
if (curPage < 1) curPage = 1;if (curPage > totalPages)
curPage = totalPages;
// Create the <paging> fragment to add to the "data page"
Document d = actElt.getOwnerDocument();
Element e = d.createElement("paging");
root.appendChild(e);
addResultElement(e,"total-rows",Long.toString(totalRows));
addResultElement(e,"total-pages",Long.toString(totalPages));
addResultElement(e,"current-page",Long.toString(curPage));
if (curPage < totalPages)
addResultElement(e,"next-page",Long.toString(curPage+1));
if (curPage > 1)
addResultElement(e,"prev-page",Long.toString(curPage-1));
addResultElement(e,"target-page",pageName);
if (pageArgs != null && !pageArgs.equals("))
addResultElement(e,"target-args",expandedUrlParams(pageArgs,
actElt));
// Set to page-level parameters that the <xsql:query> can use
req.setPageParam("paging-skip",
Long.toString((curPage-1)*pageSize));
req.setPageParam("paging-max",Long.toString(pageSize));
}
// Get the name of the current page from the current page's URI
private String curPageName(XSQLPageRequest req) {
String thisPage = req.getSourceDocumentURI();;
int pos = thisPage.lastIndexOf('/');
if (pos >=0) thisPage = thisPage.substring(pos+1);
pos = thisPage.indexOf('?');
if (pos >=0) thisPage = thisPage.substring(0,pos-1);
return thisPage;
}
}
This class is invoked by the <xsql:action handler> element, as in the following example from list.xsql:
<xsql:action handler="oracle.xml.sample.xsql.portal.Paging"
rows-per-page="15" url-pagename="index.xsql?
pagename=list&cat={@cat}&lang={@lang}&">
<![CDATA[
SELECT count(1) FROM faq
]]>
</xsql:action>
Invoking this element will pass the three parameters to the paging class that will compute the total number of 15-row pages from the total number of FAQs. This class will also set the paging-skip and paging-max parameters that will be used in the following query to retrieve the correct set of questions:
<xsql:query skip-rows="{@paging-skip}" max-rows="{@paging-max}">
<![CDATA[
SELECT extractValue(value(x),'/FAQ/TITLE') as title,
extractValue(value(x),'/FAQ/@status') as status,
extractValue(value(x),'/FAQ/@id') as id
FROM faq x
order by id
]]>
</xsql:query>
Now all that you need to do is create a Next link that increments the p parameter to display successive FAQ pages. This is done by creating the following link to pass a new set of parameters to index.xsql and subsequently list.xsql:
http://localhost:8988/xdkus/app_faq/index.xsql? pagename=list&cat=all&lang=all&p=2
Each time p is incremented, a new set of 15 FAQs is displayed until the maximum is reached.