Sunday, December 20, 2015

Oracle JET Collection Paging Control and ADF BC REST Pagination Perfect Combination

Oracle JET provides out of the box support for collection paging control, this allows us to use table/list UI with show more option or pagination - Paging Control: High Watermark Table with unknown row count. The main advantage of pagination from performance point of view - no need to load entire collection at once. Data from the REST service can be loaded to the UI in small portions.

I did a test to check how it works to use JET pagination control on top of data from ADF BC REST service. It works great, it seems to be ADF BC REST and JET collection with pagination control are in perfect synch. ADF BC REST service is fetching data based on JET pagination settings.

Here you can download sample application (this archive contains two projects - ADF BC REST implemented in JDEV 12.2.1 and JET UI implemented in NetBeans) - Make sure to download JET distribution and copy JS/CSS into my sample app.

JET UI table with show more functionality rendering Employees table data received from ADF BC REST service:

I have configured JET to render 10 rows initially. ADF BC receives request to fetch 10 rows, we can see that from iterator range size:

ADF BC is configured with Range Paging support. SQL query is constructed with rownum to fetch only a subset of data for requested 10 rows:

Let's load more rows, click show more and 10 more rows will be loaded out of total 107:

We can observe correct behavior in ADF BC log. SQL query with rownum is executed and two ranges (1-10, 10-20) are filled with data, which corresponds to JET UI request:

Show more functionality for collection in JET is configured with loadMore mode option set for paging control. You should put paging control below table UI in JET, in HTML page:

JavaScript method responsible to call ADF BC REST service and construct collection data source with paging control sets fetch size. Through fetch size property, JET controls how many rows will be rendered in the page or how many more rows will be displayed with show more functionality. Fetch size is sent to ADF BC REST service, where VO range paging and data fetch are controlled accordingly:

If you want to render pagination instead of show more option, remove loadMore property from paging control. Table pagination out of the box rendered with JET on top of data fetched from ADF BC REST service:

I can load second page of data and check how rows are fetched in ADF BC:

ADF BC works the same way as with JET show more option. It executes SQL query with rownum based on range paging enabled and fetches first two pages of data. It would be preferable, if it would fetch only second page of data. Differently from show more option, user can't see first page anyway (we should log enhancement request with Oracle Support):

At the end of each request, ADF BC executes SQL to count rows. I guess this is needed for JET, to know total number of rows:

I was positively surprised, when I saw how fetch of the last page works. Instead of fetching all rows before last page (which is usually the case with going to the last page in ADF), ADF BC REST fetches only rows displayed in the last page. This makes JET UI paging control really fast and useful. Here I'm going to the last page in JET UI table:

ADF BC is fetching rows only for the last page, no rows fetched in between:


Bhabhi said...

Can you post the new link to: Paging Control: High Watermark Table with unknown row count ?
The link you have given doesn't work in current date.

Andrej Baranovskij said...

Link updated.