Paging support in the data service layer

Paging is used to return multiple "pages" of data when it is necessary to retrieve only a certain number of entries at a time. The data service layer supports paging of data, as well as setting result limits for paging.

Paging is generally accomplished by using two step queries. In the first stage of a two-step query, the search is performed, and the primary keys of the result set are found. This corresponds to the XPath search. In the second stage, the detailed information for each row in the result set are fetched. This corresponds to the access profile.

Paging can sometimes be used for a single-step query, if it returns one record per primary key of the base table.

Setting a result limit for paging for the entire service module

In the wc-component.xml service module configuration file, you can define the "maximumPagingResultLimit" attribute. For example:

<_config:dataservice dataMediatorType="JDBC" 
	metadataClass="com.ibm.commerce.catalog.example.server.metadata.CatalogExampleMetadata" 
	maximumPagingResultLimit="5000">
</_config:dataservice>

When paging is requested by the client, querying more rows than specified by the maximumPagingResultLimit will result in an exception.

How is paging supported by the query template

If you have particular query which you want to have a smaller or bigger limit, you can define a paging_result_limit in the SQL_STATEMENT section of your query template. For example, in the sample below, the paging_result_limit is set to 50:

BEGIN_XPATH_TO_SQL_STATEMENT
       <!-- fetch all CATENTRY records given a set of part numbers -->
       <!-- xpath = "/CatEntry[@catEntryId<123]" -->
       name=/CatEntry[@catEntryId<]+CatalogPaging
       paging_result_limit=50
       entity_table=CATENTRY
       dbtype=db2
       sql=  
             		SELECT  CATENTRY.$COLS:CATENTRY$
                      FROM CATENTRY WHERE CATENTRY.CATENTRY_ID < ?catEntryId? AND                            										
               CATENTRY.MARKFORDELETE = 0 ORDER BY CATENTRY.CATENTRY_ID
              
       paging_count
       sql =
              SELECT  
              	COUNT(*) as ct
              from 
                CATENTRY 
              where 
              	CATENTRY.CATENTRY_ID < ?catEntryId? AND                                                 
                            CATENTRY.MARKFORDELETE = 0
END_XPATH_TO_SQL_STATEMENT

paging_count SQL statement

If you are paging within a single step query, you need to provide a paging_count SQL statement. The paging count SQL statement should return the total number of unique records from the base table returned by your XPath to SQL template. If the number of the results exceeds the paging limit, an exception is thrown. An example of the paging_count SQL is shown in the following sample:

       paging_count
       sql =
              SELECT  
              	COUNT(*) as ct
              from 
                CATENTRY 
              where 
              	CATENTRY.CATENTRY_ID < ?catEntryId? AND                                                 
                            CATENTRY.MARKFORDELETE = 0

Note:

The paging_count SQL statement must be used for single-step queries.
The paging_count SQL statement is not required for two-step queries.
Parametric search queries should not specify the paging_count SQL statements.