Database Change Notification Listener Implementation

Oracle DB could notify client, when table data changes. Data could be changed by third party process or by different client session. Notification implementation is important, when we want to inform client about changes in the data, without manual re-query. I had a post about ADS (Active Data Service), where notifications were received from DB through change notification listener - Practical Example for ADF Active Data Service. Now I would like to focus on change notification listener, because it can be used in ADF not only with ADS, but also with WebSockets. ADF BC is using change notification to reload VO, when configured for auto refresh - Auto Refresh for ADF BC Cached LOV.

Change notification is iniatilized by DB and handled by JDBC listener. There are two important steps for such listener - start and stop. Sample application - WebSocketReusableApp.zip, implements servlet DBNotificationManagerImpl:

I'm starting and stopping DB change listener in servlet init and destroy methods:

DB listener initialization code is straightforward. Data changes are handled in onDatabaseChangeNotification method. Listener is registered againt DB table executed in initial query:

In my example I'm listening for changes in Employees table. Each time, when change happens - query is re-executed to calculate new average salary values for jobs:

On first load, when servlet is initialized - listener is registered. Initial data read happens and listener is reported as started:

I can change value in DB, commit it:

DB change listener is notified and query is being executed to fetch new averages. Value for SA_REP is changed:

I hope it makes it more clear how to use DB change listener. In my future posts I will describe how to push changes to JET through WebSocket.

Automatic ADF Logout on Browser Close with WebSocket

Every ADF project could have a requirement to handle browser close event effectively. Differently than desktop applications where we could handle such events, browser doesn't send any event to the server, when browser page is closed. This is especially important for transactional data, when user locks data row and lock must be released automatically, in case if user is closing browser without unlocking. Besides transactional data management, it is important for performance improvement - Web session will be closed instantly and WebLogic resources will be released. There was no reliable solution to handle this, now we can do it with WebLogic 12c and WebSockets. WebLogic 12c supports WebSockets natively, there is no need to configure anything or add libraries.

When WebSocket channel is closed, it delivers event to the server on browser close. We could use this event to release locked resources, we need to logout ADF session to force ROLLBACK. Sample application implements HTTP client invoked by WebSocket server endpoint. This HTTP client simulates browser activity and redirects to adfAuthentication with logout request, using original JSESSION ID from closed browser session. This works also during accidental client computer power off. Download sample application where I have described all important implementation steps - ADFSessionHandlingWebSocket.zip.

Sample application is protected with ADF Security, you can login with redsam/welcome1 user. It includes Oracle JET libraries, one of the dashboard tiles implements JET fragment. JET is not required for the sample to work, it is used only to implement dashboard UI.

You can observe from browser log when WebSocket connection is opened. Connection is established on initial page load, immediatelly after login. This opens WebSocket communication chanel, as soon as this chanel will be closed - WebSocket server endpoint will force logout for ADF session from closed browser:

As soon as WebSocket channel is established, ADF web session ID (JSESSIONID is retrieved from the cookie) is sent to WebSocket server endpoint. Sample logs ID of ADF web session:

I'm going to lock one of the records, I'm invoking ADF BC lock method (DB pool is disabled, this will keep DB connection assigned for AM):

Lock action for row data is visible in the log, SELECT FOR UPDATE is executed for ID 102:

Let's close a browser now without issuing ROLLBACK, invoke Quit action in the browser:

WebSocket channel will be closed and this will trigger ADF Authentication servlet request to logout from ADF web session. As logout happens, ADF resources are released, ADF BC is triggering ROLLBACK in DB:

Session is closed based on JSESSIONID. With HTTP Client we simulate user logout after browser was closed:

Now we should overview technical implementation. WebSocket channel is opened from JavaScript:

This happens on page load, with clientListener triggering connectSocket method:

Method connectSocket in JavaScript is using standard WebSocket API to open connection:

WebSocket server endpoint is defined with custom configurator. Through configurator we can reference HTTP session initiated in ADF. HTTP client in WebSocket endpoint will use it later, to simulate ADF logout:

ADF HTTP session is referenced through WebSocket configurator:

Helper session listener class is defined in web.xml, here I'm copying JSESSIONID from the cookie into session attribute (to be able to reference JSESSIONID in WebSocket endpoint):

OnClose method in WebSocket endpoint is invoked, when connection channel between client and server is closed (browser is closed). Here I'm invoking custom method handleLogout method:

I'm constructing HTTP client request with the same JSESSIONID value as it was copied from ADF session. HTTP Get is executed against ADF Authentication servlet with logout request using JSESSIONID value. This is forcing ADF session to logout from simulated HTTP session by HTTP client:

Edit Use Case for ADF 12.2.1 Dashboard with Masonry Layout

I was researching ways to implement edit functionality for dashboard created with ADF 12.2.1 masonry layout. Check my previous post, where dashboard was implemented - ADF 12.2.1 Responsive Dashboard with Masonry Layout. I have decided to use panel drawer component, which would bring editable fields in the context of data displayed in the dashboard.

Panel drawer icon is displayed in the top right corner of the dashboard:

User can click on the icon and this shows editable form for the data rendered in the dashboard:

Dashboard is implemented with masontry layout, it makes it possible to re-arrange tiles. This proves to be useful when editing data. I can move tile with the chart and change salary value. Chart is synched and new value becomes visible. Two actions are done at once - data update and change review in the chart:

One more use case - validation. While editing salary, we can check tile with minimum and maximum salary values. This could help to understand, what salary value can be accepted. For example, if too low salary is set, validation error is returned. User can cross check this in the chart with minimum and maximum values:

Panel drawer is defined in the same group, where masonry layout block is located:

Edit form is rendered through ADF region, this means it can be reusable:

Download sample application - DashboardApp_v4.zip.

Handling ADF BC 12.2.1 REST Validation in Oracle JET

CRUD use case would not be complete without validation logic implementation. Oracle JET allows to implement standard and custom validators on the client. Probably most of the simple to average complexity logic will be implemented in Oracle JET. Complex use cases can be handled in ADF BC validation rules, data will be validated through REST calls. When validation fails in ADF BC, error message is propagated through REST back to Oracle JET client, where we can parse it and attach to the UI field.

You can watch demo video, it shows how it works. I'm trying to update a row with invalid data, rules are executed in ADF BC and validation messages are displayed next to the UI fields in JET:

There are three rules defined in ADF BC. It might be tricky to map UI field with error message. This is easy in case of PATCH method, ADF BC REST returns failed attribute name together with the message. This doesn't work in the same way with POST method, when new row is created. For this reason, I have introduced custom indicator into each validation message *AttributeName*. I'm parsing this value in JET, to understand which UI field should be marked as incorrect.

Rule 1. Unique Key validation rule for Email attribute.

Rule 2. Hire Date validation rule, it should not be in the future.

Rule 3. Salary range validation rule.

Mandatory check is performed in JET, if mandatory UI field is not set - JET will prevent further call to REST service. I have achieved this with JET invalidComponentTracker property defined for JET UI input fields:

Tracker variable is defined and accessed in JavaScript, here we are going to evaluate if there are any issues on UI components, before calling REST service. There are three variables defined to report valdiation messages from ADF BC REST for Hire Date, Email and Salary fields:

In update function, before calling REST - I check if there are no issues with UI components by obtaining tracker:

Method showMessages is invoked for tracker, if there are issues - focus is set on the first one. If this doesn't happen, it means there are no issues and further REST call is allowed:

This is how it looks on UI. I'm trying to update empty row, there are mandatory fields and built-in functionality prevents me from submitting data:

Let's move to the main topic of this post - ADF BC REST call validation error handling. JET allows to define callbacks for REST call action - success/error. If ADF BC REST call fails with validation issue, error callback is invoked. Here we can get response text, parse it and assign to the UI fields. Multiple validation rules can fail at once in ADF BC, REST returns multiple messages in one response. This is good, we can display all errors at once:

Messages are attached to UI field through special property (and variable defined above) - messageCustom. This must be set on JET UI field:

In JavaScript callback, we are parsing response, constructing error message and assigning it to the messageCustom of specific UI field. This is how message is displayed on UI, next to the field:

Here is example of all three validation rules failing and messages are displayed on JET UI:

All three are reported in REST call response by ADF BC validation. We just need to parse returned message in Oracle JET and display it:

Download sample application - JETCRUDApp_v4.zip.