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

4

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:

image

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.

image

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:

image

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):

image

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

image

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

image

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.

image

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)

image

that registers with the DatabaseNotificationHandler in its constructor:

image

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

image

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).

image

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.

image

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

image

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

image

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.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

4 Comments

  1. Hi Lucas,

    I am trying to develop a system which notifies the application according to modification in database like your sample.I m using JDeveloper 11.1.1.4.0 version and Oracle Database 11g Enterprise Edition(remote). I have followed the instructions, downloaded the demo application (step1) edited db configurations, created the DB_NOTIFICATIONS table and the trigger on EMP table
    The trigger works and adds rows to DB_NOTIFICATIONS about the modifications. I also added the CHANGE NOTIFICATION privilege to my current user.
    When I execute the project with Employees.jspx It registers the listeners to DB. There is 2 logs on the console screen:

    - Register for table DB_NOTIFICATIONS select id, event_context from db_notifications where event_type=’EMP_DML’
    - HR.DB_NOTIFICATIONS is part of the registration.

    and I can see the data of EMP table and data of its on the browser. When I made a modification on EMP table (changing sal value as in the example)
    The trigger adds a row to DB_NOTIFICATION table. But it does not reach to onDatabaseChangeNotification() method to handle the modification in the middle tier.

    The query registration process is being passed
    without any exception. The only problem is DB does not notify the application over jdbc. I am running the application
    on my local weblogic server. Only the DB is remote.

    I have created a thread in oracle forums about this sample maybe you look at.

    https://forums.oracle.com/forums/message.jspa?messageID=10699089

    I do not understand why it does not work. Do you have any idea about the problem?

    Thanks.

  2. Hi Lucas. Great post, especially because you use the db_notifications table to register and push database changes. Much cleaner then registering all queries on by one in the ADF Application as I was doing. Curious about part II

  3. Did you ever tried connecting JMS from Oracle database to ADF? We’ll must to do in a development something similar to this article, and we were thinking on using JMS.

    • Lucas Jellema on

      Hi Hal,

      Connecting to JMS from the database seems at first like something fairly trivial. Unfortunately, on closer inspection, it is not trivial at all.

      Publishing however from the database to JMS is easy enough: you can expose AQ as a JMS queue on WebLogic and from ADF you can register on that JMS queue. That gives a fairly straightforward route from database to ADF application as well. The question remains what and when to publish on AQ – what is the ‘notification trigger’.
      This article has some information: http://technology.amis.nl/2010/09/18/oracle-advanced-queuing-and-jms-bridging-from-aq-to-jms-and-vice-versa/. The suggestion however of using Java Stored Procedure to call out JMS turned out to be non-trivial, so I suggest you stick with AQ based approach.

      kind regards,

      Lucas

Leave a Reply