Notifying ADF applications of database changes – fast and lean using Database Query Result Change Notification – Part One

In two articles, I will demonstrate how we can ensure that changes in the database – new, changed or deleted data – can rapidly be reflected in ADF based user interfaces running in browsers. This story involves a two-step push-mechanism: push from database to middle tier and push from middle tier to browser. For the former – discussed in part 1 – we will use the Oracle Database (and JDBC Driver) feature Query Result Change Notification and for the latter we leverage the Active Data Service in ADF Faces. At the end of this first part in the series, you will have relevant changes in the database reported to the ADF application, almost instantaneously. The push to the user interface does not yet happen. However, any request from browser to server will serve to also refresh the relevant UI components, thanks to the declarative ‘piggy back’ mechanism that the ADF Binding framework provides us with.

The approach demonstrated here will work with Oracle Database 10g or 11g – provided you are using the 11g version of the JDBC drivers. Any version of ADF 11g will do on the application side.

The application architecture under scrutiny in part one can be sketched as follows – with the database underneath – with tables EMP and DB_NOTIFICATIONS – and the ADF Model layer on top:

The essential mechanism in this first part is the Database Query Result Change Notification feature. In a nutshell, this feature allows us to register a Java Object as a listener with the Oracle JDBC Driver and indirectly with the Oracle Database, to listen to notifications of changes in a certain data set. A SQL query that defines the relevant data set (both columns and rows) is associated with the registration of the listener. Whenever DML operations in the database impact the data set, a notification is sent by the database to a special socket on which the JDBC Driver has a special thread listening. On this thread, the registered listener objects are notified whenever the notification is received from the database. This is a very fast mechanism. ADF uses this same mechanism for Read Only View Objects in Shared Application Modules that have their Auto Refresh property set to true. Several blog articles write about this, for example: http://niallcblogs.blogspot.nl/2009/07/auto-refreshing-adf11g-gantt-project.html, http://radio-weblogs.com/0118231/2008/06/26.html and http://niallcblogs.blogspot.nl/2009/06/auto-refreshing-adf-chart-objects-in.html. In this article, the restriction of Read Only View Objects and Shared Application Modules does not apply; we use the Database Query Result Change Notification independently from ADF BC.

The steps we go through:

1. In the Database

Using the file model.ddl in the downloadable sample project:

– assuming table EMP already exists (from the SCOTT schema, you know):

create table DB_NOTIFICATIONS, sequence db_notifications_seq and the AFTER STATEMENT trigger on EMP that creates a new row in DB_NOTIFICATIONS, whenever a DML statement has been executed against table EMP.

Ensure that the user under which the tables and trigger are created has the CHANGE NOTIFICATION database privilege:

grant change notification to scott

Note: registrations for database notifications can be seen through data dictionary views USER_CHANGE_NOTIFICATION_REGS or DBA_CHANGE_NOTIFICATION_REGS. Registrations can be removed in PL/SQL using DBMS_CHANGE_NOTIFICATION.DEREGISTER (  regid IN NUMBER). In Java, something similar can be done using oracle.jdbc.OracleConnection.unregisterDatabaseChangeNotification(). When registrations are created from a JVM, these will automatically disappear when the JVM shuts down.

2. In the Model project

Create a Fusion Web Application. Create a Database Connection to the SCOTT schema. Create Business Components based on the EMP table in a new Application Module. Create the implementation class for the Application Module that extends from ApplicationModuleImpl. Override the prepareSession() method in this class. We will abuse this method to register the database change notification listener with the database. Note: there are far better places to do this than this prepareSession() method, but it was easy for now.

The code for registering the listener for database notifications is in class DatabaseNotificationHandler that implements the DatabaseChangeListener interface defined in the Oracle JDBC driver. This interface defines method onDatabaseChangeNotification:

The listeners that are notified in this method implement interface DataUpdateEventListener that is defined in the Model project. These listeners are registered with the DatabaseNotificationHandler (in static collection):

The method registerForDatabaseNotifications is invoked from the prepareSession() method:

This method uses the OracleConnection object to register a notification listener:

Note the SQL query that is registered: only new rows in table DB_NOTIFICATIONS with the event_type set to EMP_DML are to be listened for.

3. ViewController project

Create a new JSF page. Add a PanelCollection to the page. Drag the Employees collection from the Data Control Palette and drop it as an ADF Table inside the PanelCollection. At this point, you can run the page. The table with the employees is shown.

When the EMP data is updated from another source than this page, and the transaction is committed, the database will notify the ADF application through is registered listener. Unfortunately, at this point, that listener does not do anything useful with the notifications, because no listeners have been registered at this point.

In order to get notifications of EMP changes into the View-tier, we configure a managed bean databaseNotificationProcessor (application scope)

that registers with the DatabaseNotificationHandler in its constructor:

Additionally, a session scope managed bean – empRefresher – is configured

This bean registers with bean databaseNotificationProcessor as listener to the data change events that databaseNotificationProcessor receives from the Model tier and distributes to View-level listeners (implementing DataUpdateListenerI).

When the database sends its notification, the Model tier receives and re-routes to the application scope databaseNotificationProcessor  that sends events to the session scope EmpRefresher. And when the latter bean receives the event, it sets its needsRefreshing boolean to true. Note: when sessions can be serialized (perhaps to be reestablished on another node in the middleware cluster), this is clearly a too simplistic approach; in those cases, we would need a proper queuing mechanism (e.g. JMS) or a trick where beans after being deserialized register as listener.

So now the question arises: when this boolean is true and knowledge about the change at database level has arrived in each specific session – what can we do to make the UI refresh?

As mentioned before, the push from server to browser will be discussed in the next part in this two part series. In this installments, we will settle for: the view has the [knowledge of the] change and when a request is handled from a browser for a specific session, these changes are piggy backed to the client.

This piggy back is realized through two aspects. First, the change event policy property for the iterator binding is set to ppr. This instructs ADF to perform partial page refresh on all components data bound to this iterator if and when it contains updates.

The second element we need is the refresh of the data itself – the requery of the underlying ViewObject. This is implemented using an invokeAction:

This invokeAction executes the Execute action that will execute the query for the EmpView1 ViewObject:

The RefreshCondition for the invokeAction is defined as #{empRefresher.needsRefreshing}. Here we see the empRefreher bean again, that was registered as a listener on the application scope bean databaseNotificationProcessor and is notified of any database level change in EMP. It sets the boolean that is used in this RefreshCondition. When the RefreshCondition is evaluated after the boolean has been set, it will execute and invoke the Execute action that will requery the ViewObject.

Now we have arranged for the Table in the browser to be refreshed with fresh data whenever a request (any request) is handled by the server after the database has notified the application of the changes. Such new requests can originate in a number of ways: simply refreshing the browser, firing a poller, sending a server event or executing a partialSubmit.

Note: because the Table is updatable and the ViewObject is based on Entity Objects, data is cached in the session. Any request that involves updating the data in the table will cause a rowset to be set aside for the table, with the data as it currently is. Requerying the ViewObject will in that case not have any impact on the data shown in the page. You will get this effect for example when you include a button in the page to initiate that request; the button will send data from the table to the server and even without actual changes made by the user, changes from the database will not become visible in the client.

Resources

Download the JDeveloper 11g (11.1.1.4) application:ADFApplicationWithDBQRCN-Step1 .

Blog article: How to force a table to refresh itself –  http://dkleppinger.blogspot.nl/2009/05/how-to-force-table-to-refresh-itself.html.