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.