- 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
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.
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:
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.
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:
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):
- 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
Demo application can be downloaded here
JDeveloper version 188.8.131.52.0