Override inputListOfValues component model and Continuous Query Notification sample in ADF

Requirement:

Standard ADF inputListOfValues component should behave like this:
    • When pressed "Search", action should stop and wait untill any change happen in DB table being searched
    • When change happen in DB table, search should resume among updated data. If no change happen it should resume after 10 seconds timeout
    • In addition - search input field validation should happen on "Search" press. If there is no at least one field filled with at least 3 symbols - error should be shown.
Well, this requirement may look sensless, but this is just a PoC of functionality, of what and how is possible to implement. Maybe a peace or a whole of this sample can be adapted to your case.

Solution:
  • Create a separate class which extends ListOfValuesModel, put custom logic there for validation and processing query
  • Use Continuous Query Notification (CQN) or in earlier Java releases called Database Change Notification (DCN) for delivering event from DB to application
  • Use Java latches (CountDownLatc) for suspending and resuming search after either an event from DB or timeout occurs.
This PoC will use Oracle DB and HR schema for sample data. Will put LOV on departmentId attribute of EMPLOYEES table. Will suspend searching in DEPARTMENTS table untill any change there happens.
  • Lets start from creating custom class for inputListOfValues model:


  • Configure LOV for departmentId in EmployeesView. Set UI hint for list type "Input Text With List of Values". Create main.jspx file and drag'n'drop EmpoyeesView data control into it, render as regular ADF table. Check whether inputListOfValues component is rendered for departmentId attribute:

  • Create a managed bean mainBean.java for main.jspx and put there getter of inputListOfValues model. Reassign model in main.jspx.
  • Get back to custom model class ListOfValuesModelEx. Put necessary validation and change subscribtion logic into method performQuery. Special attention to CountDownLatch utility here - it is used to suspend query execution and wait either till it is notified from the CQN listener or timeout occurs. The key points are emphasized in red:
For more detailed sample description of Continuous Query Notification (CQN/DCN) usage, please have a glimpse to my previous post: WebSocket together with DB Change Notification in ADF for immediate data refresh in browser (complete sample).

Thats it,  please download sample application to find more details and test it!

JDeveloper version 12.1.0.0

WebSocket together with DB Change Notification in ADF for immediate data refresh in browser (complete sample).

Requirement
  • We have DB table contents displayed in ADF GUI
  • After changes in that DB table (by anyone else in the background) we want to immediately display in our currenlty open browser session
Solution

To achieve our goal we need somehow to know when changes in DB happen. Once we know when, need to notify browser session in order it could be refreshed. This time we'll exploit two Java offerings:
  • JDBC Database Change Notifications (DCN) - to subscribe and receive notifications when changes happen in DB
  • WebSocket - protocol other that HTTP which works in request/response manner. Basically its full-duplex communication channel over a single TCP connection. That means we can have bidirectional continuous communication between server application and browser.
Lets do simple PoC with EMPLOYEES table from HR schema. Just display its contents and immediately refresh if changes happen in this table.
Make sure you have access to any Oracle DB (recommended >= 11.1 - more DCN features available) HR schema.
  • Create basic ADF Fusion Web Application. Create bussiness component for EMPLOYEES table and generate simple .jspx file with its contents displayed on the screen (ADF table component is fine):

  • That was the very basic so far. Now will create WebSocket project which will serve as listener for DB changes and the one which send notifications to browser. In the same recently created application, create new project of type "WebSocket":

    • Create java class which will implement WebSocket service. Annotate it as a server endpoint:

    • Click on code assistant icon and select "Configure Project for Web Socket":

Recent action has been generated web content folder with necessary belongings. 
    • Implement main service methods. Lets choose annotation driven implementation and add following methods with according annotations:
Note that method processMessageFromClient will not be used by fact in this sample, because it is not necessary to fullfill our requirement. But we just add a stub of it to be able to utilize it later if necessary. We have full WebSocket service structure defined already. It can be deployed although will not process anything benefiting our needs.
  • Now lets move to ViewController project and do necessary steps to be able to  establish and close connection between browser session and WebSocket service. Also implement refreshing of data in the browser.
    • As there are no dedicated ADF components for connection establishing and maintaining it, will leverage basic javascript to implement that. Add WebSocket.js file to ViewController project:

Notice WebSocket service URL marked with green rectangle. You need to check if its the same in your enviroment. Target URL is displayed right after running service in integrated server:

    • Create two buttons in the GUI which do connection and disconnection accordingly. Either of them will be client listener based and call javascript functions. Add javascript resources to page:


    • Add another button id="b1" (visible in screenshot above) to refresh table content when event from WebSocket service is received. You can make it either visible or invisible. It doesn't matter, because its action is queued using javascript means (function refreshJs() ) anyway. Add server listener which invokes bean method to refresh EmployeesView by re-executing its iterator:


At this moment were done with basic WebSocket connectivity implementation. We can run either ViewController and WebSocket project and try to enable and disable WebSocket connection. Check how it works:

  • Lets move back to WebSocket project implementation. There we still need logic which takes care of data change notifications from database.
    • Ensure that DB user HR has change notification privilege: 
grant change notification to hr;
    • Add DB notification subscription controller and listener classes. Add WebLogic remote client library to project:


    • Configure data source for HR connection in WebLogic and use it in DBChangeNotification.java for accessing DB:


    • Configure DB Change registration in DBChangeNotification.java class. Two main things worth to pay attention to: 
      • There are set two registration properties: DCN_NOTIFY_ROWIDS (tells that we are interested in changed rowids and want to get them in the payload) and DCN_QUERY_CHANGE_NOTIFICATION (tells that our registration is of higher granularity and is select statement based rather than DB object based).
      • In this sample statement "select salary from employees where employee_id=100" is used,  by which we ask DB to notify only when salary of employee whose employee_id="100" changes. DCN_QUERY_CHANGE_NOTIFICATION property is needed if we want filtering against some criteria and focus on certain columns take effect. If this property would be missing - even with such quite fine granularity query DB would notify about any change in EMPLOYEES table. 

    • Add Service.SendMessageToClient call in DB change listener (DBChangeNotificationListener.java):


  • Add javascript logic inside onMessage function to queue refresh action to ADF application from browser:

  • We're done, now can test the behavior:
    • Run application and WebSocket service
    • Enable WebSocket connection by clicking button in the GUI
    • Change salary of employee_id="100" using SQLDeveloper or other SQL execution means. 
    • Change should be immediately visible in the browser. 
In the Integrated server and browser logs can see notification cycle logged:

 

Note: for cleaning DB registrations can use app described in my other post How to select all JDBC Database Change Notification Registrations and clean them?

Demo application can be downloaded here



JDeveloper version 12.1.3.0.0

How to select all JDBC Database Change Notification Registrations and clean them?

Question:
How do I select all database change notifications registered via JDBC driver by my DB user?

Answer:
select * from user_change_notification_regs


Question:
How do I clean all of my DB change notifications?

Answer:
Database change notifications registered via JDBC driver can be deregistered only the same way - using JDBC driver. Therefore you need to execute this code:

This code can be downloaded here.

Comment:
More info about JDBC database change notifications can find in Database JDBC Developer's Guide