View source | View content page | Page history | Printable version   

Projects:Paged Datagrid/How to convert an existing Selector



This page describes the needed changes in order to convert an existing selector to leverage the new paging mechanism introduced in the Paged Datagrid project.

The conversion of an selector in optional. The changes done to the common code are designed in a way that the decision to use them can be done on a per selector basis.

Changes needed

To convert an existing selector changes in three different files are needed:

The example changes shown below are based on the conversion of the Product selector which is shipped in the core module and are shown in the common format as produced by the diff-tool. However thee diff-files have been edited to shorten them to be presentable more easily.

Changes: xsql-file

All xsql files of the selectors do contain at minimum two methods. One method (i.e. called countRows) to count all the rows which match the current filter criteria and a another one (i.e. called select) which selects the rows which match the current filter criteria and are inside some visible window using limit/offset in postgres and rownum on oracle.

The countRows method needs to be changed so it supports these queries including limit/offset or rownum. This is needed to be able to count the rows which are in i.e. the second page of the datagrid.

The technical change do add this is different on oracle/postgres and consists of a number of optional parameters which are then provided in the calling java code depending on the database system (oracle or postgres) used at runtime.

    <SqlMethod name="countRows" type="preparedStatement" return="String">
       <Sql> <![CDATA[
-        SELECT count(*) as value
-        FROM M_Product p, M_ProductPrice pr
+      SELECT count(*) AS value FROM ( SELECT '0' AS rn1, B.*  FROM ( 
+        SELECT 1 FROM M_Product p, M_ProductPrice pr
         WHERE p.M_Product_ID=pr.M_Product_ID
         AND p.IsSummary='N'
         AND p.IsActive='Y'
         AND pr.IsActive='Y'
         AND pr.M_PriceList_Version_ID = ?
         AND p.AD_Client_ID IN ('1')
-        AND p.AD_Org_ID IN ('1') 
+        AND p.AD_Org_ID IN ('1')
+        AND 1=1
+      ) B
+      ) A 
+      <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
         <Parameter name="key" ignoreValue="%" optional="true" after="WHERE "><![CDATA[ UPPER(p.Value) LIKE UPPER(?) AND ]]></Parameter>
         <Parameter name="name" ignoreValue="%" optional="true" after="WHERE "><![CDATA[ UPPER(p.Name) LIKE UPPER(?) AND ]]></Parameter>
         <Parameter name="priceListVersion"/>
         <Parameter name="adUserClient" type="replace" optional="true" after="p.AD_Client_ID IN (" text="'1'"/>
         <Parameter name="adUserOrg" type="replace" optional="true" after="p.AD_Org_ID IN (" text="'1'"/>
+      <Parameter name="pgLimit" type="argument" optional="true" after="AND 1=1"><![CDATA[LIMIT ]]></Parameter>
+      <Parameter name="oraLimit1" type="argument" optional="true" after=") B"><![CDATA[ WHERE ROWNUM <= ]]></Parameter>
+      <Parameter name="oraLimit2" type="argument" optional="true" after=") A "><![CDATA[WHERE RN1 BETWEEN ]]></Parameter>

Changes: java-file

The changes needed in the java servlet of a selector can be grouped into these areas:

The server-side backendPage number (stored in the users' session) needs to be removed, when the filters are cleared as it is only relevant to the current filters.

   private void removePageSessionVariables(VariablesSecureApp vars) {
+    vars.removeSessionValue("Product.currentPage");

The number of rows per backendPage is a server-side constant, but need to be available to the client side (javascript) code. To make it available it is transferred with the response to the STRUCTURE request.

  private void printGridStructure( ...
+    xmlDocument.setParameter("backendPageSize", String.valueOf(TableSQLData.maxRowsPerGridPage));

When the client-side code is asking for some rows with the standard DATA request the backendPage value needs to be read and the relative client-side offsets (relative to the start of a backendPage) need to be transformed into absolute server-side offsets. Additionally if the user clicked on a change-page link the backendPage needs to be incremented/decremented and a reexecution of the countQuery needs to be triggered to count the rows in the new changed backendPage. Finally the current backendPage needs to be sent to the client with the result returning the rows.

  private void printGridData( ...
+    int page = 0;
         // build sql orderBy clause
         String strOrderBy = SelectorUtility.buildOrderByClause(strOrderCols, strOrderDirs);
+        page = TableSQLData.calcAndGetBackendPage(vars, "Product.currentPage");
+        if (vars.getStringParameter("movePage", "").length() > 0) {
+          // on movePage action force executing countRows again
+          strNewFilter = "";
+        }
+        int oldOffset = offset;
+        offset = (page * TableSQLData.maxRowsPerGridPage) + offset;
+        log4j.debug("relativeOffset: " + oldOffset + " absoluteOffset: " + offset);
+        // New filter or first load
         if (strNewFilter.equals("1") || strNewFilter.equals("")) {
-          // New filter or first load
-          strNumRows = ProductData.countRows(this, strKey, strName, strPriceListVersion, Utility
-              .getContext(this, vars, "#User_Client", "Product"), Utility.getSelectorOrgs(this,
-              vars, strOrg));
+          // calculate params for sql limit/offset or rownum clause
+          String rownum = "0", oraLimit1 = null, oraLimit2 = null, pgLimit = null;
+          if (this.myPool.getRDBMS().equalsIgnoreCase("ORACLE")) {
+            oraLimit1 = String.valueOf(offset + TableSQLData.maxRowsPerGridPage);
+            oraLimit2 = (offset + 1) + " AND " + oraLimit1;
+            rownum = "ROWNUM";
+          } else {
+            pgLimit = TableSQLData.maxRowsPerGridPage + " OFFSET " + offset;
+          }
+          strNumRows = ProductData.countRows(this, rownum, strKey, strName, strPriceListVersion,
+              Utility.getContext(this, vars, "#User_Client", "Product"), Utility.getSelectorOrgs(
+                  this, vars, strOrg), pgLimit, oraLimit1, oraLimit2);
           vars.setSessionValue("Product.numrows", strNumRows);
     strRowsData.append("  </status>\n");
-    strRowsData.append("  <rows numRows=\"").append(strNumRows).append("\">\n");
+    strRowsData.append("  <rows numRows=\"").append(strNumRows).append(
+        "\" backendPage=\"" + page + "\">\n");

Changes: html-file

The changes needed in the html-file can be grouped into three areas:

To be able to get the translated labels for the new change page links an include to messages.js needs to be added (only if not yet present):

 <script language="JavaScript" src="../../../../../web/js/utils.js" type="text/javascript"></script>
+<script language="JavaScript" src="../../../../../web/js/messages.js" type="text/javascript"></script>

Then two new javascript functions are needed which manage the status/visibility update of the new UI artifact. The gridMovePage is just a small wrapper function as the id attribute of the grid object could have a different name per selector. The 'updateHeader' function will be called on each scroll and updates the new row position widget and the visibility of the previous page and next page links/buttons.

+function gridMovePage(direction) {
+  dijit.byId('grid').gridMovePage(direction);
+  return true;
+function updateHeader(liveGrid, offset) {
+  var backendPageSize = liveGrid.getBackendPageSize();
+  var currPageStart = (liveGrid.metaData.getBackendPage()*backendPageSize);
+  var pageFull = (liveGrid.metaData.getTotalRows() >= backendPageSize);
+  var firstPage = (liveGrid.metaData.getBackendPage() == 0);
+  var res = "<nobr class='DataGrid_Popup_text_bookmark'>";
+  if (!firstPage) {
+    res = res + "<a href='#' onclick='gridMovePage(\"PREVIOUSPAGE\"); setWindowElementFocus(\"grid_table_dummy_input\",\"id\");' class='DataGrid_Popup_text_pagerange' id='prevPage_link'>" + getMessage("GridPreviousPage") + " " + backendPageSize +"</a>&nbsp;|&nbsp;";
+  }
+  res = res + ((liveGrid.visibleRows>0)?(currPageStart+offset+1):0) + " - "+ (currPageStart+offset+liveGrid.visibleRows) + " / " + (currPageStart+liveGrid.metaData.getTotalRows());
+  if (pageFull) {
+    res = res + "&nbsp;|&nbsp;<a href='#' onclick='gridMovePage(\"NEXTPAGE\"); setWindowElementFocus(\"grid_table_dummy_input\",\"id\");' class='DataGrid_Popup_text_pagerange' id='nextPage_link'>" + getMessage("GridNextPage") + " " + backendPageSize +"</a>";
+  }
+  res = res + "</nobr>";
+  liveGrid.setGridPaging(!firstPage,pageFull);
+  dojo.byId('bookmark').innerHTML = res;

The last change is the addition of the new widget which shows the relative position of the currently visible rows and the total number of rows (in the current page). Note: This is the same widget which is already present in the Toolbar in of the grid view in all generated window. The html for this widget needs to be added just before table tag with the id="grid_sample" which is present for all selectors.

+                <table width="100%" cellspacing="0" cellpadding="0" border="0" id="grid_bookmark">
+                  <tr>
+                    <td class="DataGrid_Popup_text_container">
+                      <div id="bookmark">
+                        <nobr class='DataGrid_Popup_text_bookmark'>&nbsp;</nobr>
+                      </div>
+                    </td>
+                  </tr>
+                </table>
                 <table cellspacing="0" cellpadding="0" width="10px" class="DataGrid_Header_Table DataGrid_Body_Table" style="table-layout: auto;" id="grid_sample">

Additional changes for Multiple selectors

If a selector which can be used to select multiple records at once is to be converted one additional change is needed to its java code. The multiple selectors do have one additional methods in its servlet which is called to get the list of id's inside a specific record offset-range. This record offset-range needs to be converted from client-side relative offsets to server-side absolute offsets (by simply adding backendPageSize+backendPage) After this adjusting of the offset the rest of the processing can be used unchanged.

  private void printGridDataSelectedRows( ...
      // build sql orderBy clause
      String strOrderBy = SelectorUtility.buildOrderByClause(strOrderCols, strOrderDirs);
+     // get current page
+     String strPage = vars.getSessionValue("ProductMultiple|currentPage", "0");
+     int page = Integer.parseInt(strPage);
+     int oldMinOffset = minOffset;
+     int oldMaxOffset = maxOffset;
+     minOffset = (page * TableSQLData.maxRowsPerGridPage) + minOffset;
+     maxOffset = (page * TableSQLData.maxRowsPerGridPage) + maxOffset;
+     log4j.debug("relativeMinOffset: " + oldMinOffset + " absoluteMinOffset: " + minOffset);
+     log4j.debug("relativeMaxOffset: " + oldMaxOffset + " absoluteMaxOffset: " + maxOffset); 

How to check for correct the behavior after the conversion

If a converted selector is used with a number of rows which is below the backendPageSize (10000) then only two user visible changes can be noticed.

The new functionality can be tested in two ways:

Partial list of use cases to test

Assumption: Enough rows are present to use the new paging behavior

Retrieved from ""

This page has been accessed 4,480 times. This page was last modified on 8 June 2012, at 05:29. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.