Very Practical CRUD with JET and ADF BC - POST and DELETE Methods

In my previous post I have described how to implement PATCH method with Oracle JET and call ADF BC REST service to update row attributes - Very Practical CRUD with JET and ADF BC - PATCH Method. Today I'm going to complete CRUD implementation with POST and DELETE methods, to create and delete rows through ADF BC REST service and JET UI.

Here you can watch video, where I demonstrate PATCH, POST and DELETE methods invoked from JET UI. Also you are going to see how pagination works with JET paging control and ADF BC range paging mode (Oracle JET Collection Paging Control and ADF BC REST Pagination Perfect Combination):

Download sample application - JETCRUDApp_v3.zip. This sample app contains JET UI and ADF BC REST implementation. I'm going to explain in more detail steps recorded in the above video.

Salary column was updated with right align in the new sample, in JET we can assign class name for table cell:

JET supports date formatters and converters. I was using yyyy-MM-dd pattern to format Hire Date attribute value:

POST method - row creation

New row is created with JET collection API method create. I have implemented success callback, if POST method in REST was successful and new row was inserted - collection is refreshed to display new row. There is no need to refresh, if collection is not virtualized (fetchSize and pagination is not used):

DELETE method - row removal

Row is removed from JET model with API method destroy. This calls DELETE method triggering REST service and removes row from JET collection, by refreshing current page (invoking GET method for collection page):

POST method test. Example of new row creation:

POST method is invoked by JET, which in turns calls ADF BC REST service and inserts new row through ADF BC into DB:

PATCH method test. Example of attribute value update. I have changed Salary attribute value:

Row with ID is updated by PATCH method through REST service call:

DELETE method test. Example of row removal:

Row with ID is removed by DELETE method through REST service call:

In my next posts I'm going to describe how implement validation and handle REST call errors.

ADF BC Groovy Improvements in ADF 12.2.1

Groovy scripting is improved in ADF 12.2.1. There are no inline Groovy expressions anymore. Expressions are saved in separate Groovy language file, external codesource. Each EO/VO will be assigned with separate file to keep Groovy expressions. This improves Groovy script maintenance (easier to check all Groovy expressions from EO/VO located in one file), also it improves runtime performance (as JDeveloper ADF code audit rule suggests).

Inline Groovy expressions created with previous JDeveloper versions are compatible in 12.2.1. All new expressions are created in separate *.bcs file. Sample application - ADFGroovyApp.zip, comes with validation rule implement in Groovy script expression:

We should check how validation rule definition looks in EO source. There is one inline Groovy expression I have created with previous JDeveloper version. It is marked as deprecated and suggested to be moved to external codebase:

Validation rule with Groovy expression created in JDeveloper 12.2.1 looks different. There is no inline Groovy code anymore, instead it points to the external codesource name:

Codesource name is registered inside operations definition:

I have defined two validation rules with Groovy for Jobs EO attributes MinSalary/MaxSalary. Code for both of them are included into single Jobs.bcs file:

On runtime it works correctly, entered values are validated against logic coded in Groovy:

Separate Groovy file approach can be turned off in ADF BC project settings, not recommended though:

Very Practical CRUD with JET and ADF BC - PATCH Method

I'm going to share few more tips, how to implement CRUD in JET and call ADF BC 12.2.1 REST service. You are going to learn how to implement table row selection and how to call REST PATCH method to update data through ADF BC REST service. Why very practical? You can take sample application code and use it in your project. Less theory and more practical hints - this is my approach.

This is how it looks JET table (with pagination control - Oracle JET Collection Paging Control and ADF BC REST Pagination Perfect Combination) and form. Download sample application - JETCRUDApp_v2.zip. User can select row and edit data in the form:

I'm going to change salary value for Alexander Hunold and invoke PATCH method with Update button click:

Through network monitor in NetBeans we can check REST method execution. PATCH method is executed for employee ID 103 with Content-Type set to Oracle ADF resource item:

Changed salary value is submitted through Request Data:

We should check ADF BC log. It shows executed SQL statement to update employee salary by employee ID. This works as expected:

Table row selection in JET is implemented with selection mode property:

ADF BC 12.2.1 REST service access in JET is easy. You need to define collection (set URL, fetchSize and model). In case of pagination, you should wrap table data source with paging data source type:

When collection is set with Fetch Size (collection is virtualized), there is a special way to access rows from the collection. Row retrieved by key from the collection, will be returned with Promise type. You should use then function to retrieve row value. This is how selected row data is retrieved. Next step is to update form fields to display selected row data, this is done through updatFields method:

Method updateFields sets each element from the form with values from selected row attributes:

Initially form elements are defined as observables with empty values:

In HTML each form field is mapped through data-bind property with  corresponding element defined in Java Script - this is how it's all mapped together:

Update button handles click event and calls update method:

Row data is updated through REST service with PATCH method, initialized by save operation on current row. Row to be updated is assigned with attribute values from form fields. PATCH method is configured through save operation property, you must do it explicitly. By default, PUT method will be executed, which requires to submit all attributes for the row, otherwise ADF BC will throw error. PATCH method is much better, there is no need to submit values for all attributes from EO row:

In my next posts, I'm going to cover create/delete, validations and call failure cases.

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) - JETCRUDApp_v1.zip. 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:

Improved JET Rendering in ADF

Method and information outlined below describes my personal research and doesn't define best practice. Such approach is not recommended/supported by Oracle and should be used at your own risk. Oracle is working on JET Composite components for ADF, to be available in the future. Limitations and caveats for this approach are described by Duncan Mills:

1. No session sharing between ADF and JET 
2. ADF and JET can’t use the same cache 
3. No shared transaction 
4. Separate timeouts 
5. Geometry management 
6. Drag&drop not possible between ADF and JET 
7. Different maintenance and different libraries 
8. Different popup’s and glasspane

This post is supposed to provide improved solution for the approach described previously - ADF and Oracle JET - Integration Pattern with ADF Regions. Many thanks to John Brock, Product Manager for Oracle JET, for his proactive comments and suggestions.

I have changed it to JET rendering in ADF, instead of integration. Perhaps word 'integration' is too strong in this context. You should not mix two frameworks together, like trying to render data from ADF bindings in JET components. Oracle doesn't encourage mixing ADF and JET, there is no support for this.

But still there are valid business requirements to create JET islands inside ADF. For example, fast data entry or displaying data from WebSocket. We could use JET table on top of REST within ADF page and allow users to type and save data really fast. There will be no integration between ADF and JET features, simply JET UI rendering inside ADF page. Similar as ADF DVT components currently work, they are based on JET libs (at least I heard such talks on OOW).

Here you can download improved sample application (you must download JET separately and copy into public_html folder) - DashboardApp_v3.zip. Improvements:

1. No need to use ADF region, it works to isolate JET code into JSF fragment

2. We can use simple JSF include to include fragment with JET into ADF page

3.CSS stylesheet is loaded once in the main page. Using af:resource, instead of script:

4. This is how it looks, chart is rendered by JET component. Obviously we could render same chart with ADF DVT, but here is only simple example. In the future I will post more advanced use cases. ADF UI built with Masonry layout, including JET island tile:

Checking for Data Consistency with ADF BC REST 12.2.1

ADF BC REST supports HTTP ETag to check for data consistency. This is common approach to implement caching and optimistic locking in REST.  ADF BC REST relies on EO change indicator attribute, when change indicator attribute value is updated, new ETag is generated. All clients with previous ETag value should be prevented from updating data, without refreshing to the latest state. Read more - 22.9.1 Checking for Data Consistency When Updating ADF REST Resource Items. There are issues with data consistency check in ADF BC REST, hopefully it will be fixed in the future (read below).

To test data consistency check, enable discriminator attribute in EO. I have enabled Salary as discriminator. Each time when this attribute is changed, new ETag value will be generated and clients holding previous value will require to re-query:

GET with If-Non-Match use case (works)

Initially we don't know ETag attribute value, we can query with empty value in If-Non-Match header. Current ETag value for the row will be returned together with the data:

Copy ETag value into If-Non-Match header field and try to execute GET request. If row data is not changed (discriminator not changed), there should be no result - ETag remains the same. This can be useful when we want to check if there are any changes in the current row:

I would like to update discriminator value to check how GET would work with changed ETag. I have tried to update with PATCH from another REST client. Unfortunately current ADF BC REST implementation contains a bug and doesn't allow to update discriminator attribute (we are going to log a bug with Oracle Support):

I'm going to update discriminator value directly in DB:

Execute GET with If-Non-Match set to previous ETag - new row data will be returned (take a look, new ETag value is different from previous one):

PATCH with If-Match use case (doesn't work)

When you try to update data and row is already changed on the server (discriminator value changed), update should be prevented. This is how it should work and it almost works that way in ADF BC REST.

We can execute PATCH with If-Match value set to the same as it is set for current row on the server. Update is successful, as it should be:

Change discriminator value in the DB, this will force server to generate ETag value in the next request and it will not match with the one on the client (it should prevent next update):

Try to execute PATCH with previous ETag value, update doesn't happen (correct), but it also returns some strange message (not correct). It should be 412 (Precondition failed) HTTP code returned instead. This should be fixed (another bug to log with Oracle Support):

Download sample application - ADFBCRestApp_v4.zip. Read my previous post about ADF BC REST - Range Paging and ADF 12.2.1 ADF BC REST.