Using Data Change Notification

Purpose

This tutorial shows you how to use Data Change Notification(DCN) using the Java program, QCNSlider.java.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

	Overview
	Prerequisites
	Setting Up the Environment
	Setting JDeveloper Project Properties
	Creating a JDeveloper project containing QCNSlider.java
	Updating the connection string
	Executing QCNSlider.java
	Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

What Is DCN?

The Database Change Notification (DCN) is a system where the client registers its interest to the result of certain queries with the database. When the objects associated with these queries change, the database server notifies the client. Using JDBC driver’s DCN feature, multi-tier systems can maintain a data cache as updated as possible by receiving invalidation events from the JDBC drivers.

To perform DCN you need to perform following steps:

Creating a Registration

In order to use DCN, you first need to create a registration. You can use the registerDatabaseChangeNotification() method of the oracle.jdbc.OracleConnection interface to create a JDBC-style of registration. To activate query change notification instead of object change notification you can set the option, OracleConnection.DCN_QUERY_CHANGE_NOTIFICATION to "true". Using this option means that when you register a select query, what you register is the actual result of the query and not the database object that the query is based on. Query registration provides finer granularity than when you register the tables.

DatabaseChangeRegistration dcr = null;
Properties prop = new Properties();
prop.setProperty(OracleConnection.DCN_QUERY_CHANGE_NOTIFICATION,"true");
try
{
dcr = conn.registerDatabaseChangeNotification(prop);
...

Associating a Query With a Registration

After you have created a registration, you can associate a query with it. You can associate a query with registration using the Statement class method setDatabaseChangeRegistration(). This method takes an object of DatabaseChangeRegistration as a parameter. In this demo you associate the query select salary from employees where employee_id=108 to the registration.

Statement stmt = conn.createStatement();
String query = "select salary from employees where employee_id=108";
((OracleStatement)stmt).setDatabaseChangeRegistration(dcr);

Notifying Database Change Events

You need to attach a listener to the registration, using its addListener() method, to receive database change notifications.

DCNListener list = new DCNListener();
dcr.addListener(list);

The program QCNSlider.java creates a GUI with a pair of sliders. When the upper slider is moved a DML operation, update employees set salary=? where employee_id =108, is performed. On occurrence of the database change event, the database server notifies the JDBC driver. The driver then constructs a new Java event, identifies the registration to be notified, and notifies the listeners attached to the registration. QCNSlider handles the change event by moving the bottom progress bar in accordance to the salary of employee_id=108.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.	Install Oracle database 11g
2.	Download and Install JDK 6.0 from Sun
3	Download and install JDeveloper from OTN
4	Download and unzip the dcn.zip file into your working directory (i.e.wkdir)

Back to Topic List

Setting Up the Environment

To execute the QCNSlider.java you need to perform the following steps to set up the environment:

You need to grant the user hr the change notification privilege. Open a terminal window and execute the following commands:

sqlplus / as sysdba 
grant change notification to hr;
exit

Back to Topic List

Setting JDeveloper Project Properties

You need to set the project properties in JDeveloper. Perform the following steps:

1.	Open a terminal window and execute the following commands: cd <jdevhome>/jdev/bin ./jdev where <jdevhome> is the directory where Jdeveloper was installed/unzipped. Note: when you run JDeveloper for the first time, you need to enter the J2SE Installation directory and also are asked whether you want to migrate your existing applications.
2.	Open JDeveloper. When the Tip of the Day window appears, click Close.
3.	Select Tools > Manage Libraries.
4.	Click the J2SE Definitions tab.
5.	Click the Browse button.
6.	Navigate to the jdk1.5.0_11/bin directory and click Open.
7.	Change the J2SE Name to jdk1.5.0_11 and click OK.
8.	Click Yes.
9.	Select Tools > Default Project Properties.
10.	Click Libraries and click Add JAR/ Directory
11.	Ctrl-select all the jar files in the $ORACLE_HOME/jdbc/lib folder and click Select.
12.	Click OK.

Back to Topic List

Creating a JDeveloper project containing QCNSlider.java

Before executing the QCNSlider.java you need to add it to a JDeveloper project using the following steps:

1.	Right-click Application and select New Application.
2.	Enter the Application Name myApp and click OK.
3.	Enter the Project Name myProj and click OK.
4.	Select File > Open.
5.	Select the QCNSlider.java file from the /home/oracle/dcn directory (or wherever you unzipped dcn.zip) and click Open.
6.	Right-click the QCNSlider.java tab and select Add to myProj.jpr.
7.	Accept the default and click OK.

Back to Topic List

Reviewing the Connection String

Scroll down a little in the QCNSlider.java file and review the connection information.

Back to Topic List

Executing QCNSlider.java

Execute QCNSlider.java using the following setup steps:

1.	Expand myProj > Application Sources. Right-click QCNSlider.java and click Make.
1.	The QCNSlider.java file compiled successfully.
2.	Open a terminal window and execute the following commands: sqlplus hr/hr select salary from employees where employee_id=108;
3.	Right-click QCNSlider.java and click Run.
4.	You will see the following output
5.	Move the slider so that the value being displayed at the bottom left changes to 62.
6.	Reexecute the following query: select salary from employees where employee_id=108; Notice the change in the salary is now 62.
7.	Update the salary of employee_id=108 to 20 by executing the following commands: update employees set salary=20 where employee_id=108; commit; You notice the change in Progress Bar labeled receiver.
8.	The query notification is not only bound to the where clause employee_id=108, but also to any update that logically effects the same row. Execute the following command: update employees set salary=80 where last_name='Greenberg'; commit; Making an update to last_name='Greenberg' (which is employee_id=108) sends the notification and the Progress Bar on the window slides.
9.	However making an update to last_name='Ernst' does not effect employee_id=108, hence no notification is sent and the Progress Bar remains as is.Execute the following command: update employees set salary=40 where last_name='Ernst'; commit;

Back to Topic List

Summary

In this tutorial, you learned about Database Change Notifcation and how to use it.

Back to Topic List