Using Data Change Notification
Using Data Change Notification
This tutorial shows you how to use Data Change Notification(DCN)
using the Java program, QCNSlider.java.
Approximately 30 minutes
This tutorial covers the following topics:
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.
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
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
To execute the QCNSlider.java you need to perform the
following steps to set up the environment:
| 1. |
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
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
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
| 1. |
Scroll down a little in the QCNSlider.java file and review the connection information.
|
Back to Topic List
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
In this tutorial, you learned about Database Change Notifcation
and how to use it.
Back to Topic List
Place the cursor over this icon to hide all screenshots.
| 
Bookmark
