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:
-
- 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.
- Configure DB Change registration in DBChangeNotification.java class. Two main things worth to pay attention to:
-
- 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