JDBC and Oracle Database Change Notification)

Database Change Notification automatically sends a notification to the application when the database is monitored for table changes (including changes to data and table structure. This is useful for updating the cache for reading, writing, and less applications, and avoiding poll databases.

Three steps are required:
1. Register.

2. Use a query to indicate which table to listen for modification notifications.

3. response.

In 11g, you can listen to the query result set, rather than trigger notification events for changes to all records.

The following code runs in the java1.6, ojdbc6.jar driver, and Oracle10g R2 environment.

<% @ Page Language = "Java" contenttype = "text/html; charset = GBK" iselignored = "false" %>
<% @ Page import = "javax. naming. initialcontext, Java. util. *, Java. math. bigdecimal, javax. SQL. datasource, Java. SQL. *, Oracle. SQL. *, Oracle. JDBC. *, Oracle. JDBC. DCN. * "%>

<%!
Connection getconnection (){
Try {
Initialcontext CTX = new initialcontext ();
Datasource DS = (datasource) CTX. Lookup ("auditdatasource ");
CTX. Close ();
Connection conn = Ds. getconnection ();
Return conn;
} Catch (exception e ){
Throw new runtimeexception (E. getmessage ());
}
}
%>

<% String type = request. getparameter ("type"); %>

<%
If ("register". Equals (type )){
Connection conn = getconnection ();
Oracleconnection oconn = (oracleconnection) Conn. Unwrap (oracleconnection. Class );
Databasechangeregistration DCR = (databasechangeregistration) application. getattribute ("databasechangeregistration ");
If (DCR! = NULL) {// Revoke Previous Registration
Try {
Oconn. unregisterdatabasechangenotification (DCR );
} Catch (Java. SQL. sqlexception e) {}// avoid ORA-29970: the specified registration ID does not exist
Application. removeattribute ("databasechangeregistration ");
System. Out. println ("unregisterdatabasechangenotification, regid =" + DCR. getregid ());
DCR = NULL;
}
Properties prop = new properties ();

// Rowids may be rolled-up if the total shared memory consumption due to rowids is too large (exceeds 1% of the dynamic shared pool size), or if too contains rows were modified
Prop. setproperty (oracleconnection. dcn_policy_rowids, "true"); // obtain the rowid of the Change Record
Prop. setproperty (oracleconnection. dcn_ignore_deleteop, "true"); // ignore Delete
// Default: prop. setproperty (oracleconnection. ntf_local_tcp_port, 47632); // you can set the TCP listening port of the program, which is 47632 by default.

// Specifies the time in seconds after which the registration will be automatically expunged by the database. If 0 or null, then the registration persists until the client explicitly unregisters it.
Prop. setproperty (oracleconnection. ntf_timeout, "3600"); // set timeout. This is an hour. Then, the database and drive resources are automatically released. If it is set to 0 or not, it never expires until the program stops listening. When the database sends an update notification, because there is no listening port, the database then releases the resource.

// Prop. setproperty (oracleconnection. ntf_qos_purge_on_ntfn, "true"); // after the first notification, the registration will be canceled.

DCR = oconn. registerdatabasechangenotification (PROP); // register
System. Out. println ("registerdatabasechangenotification, regid =" + DCR. getregid (); // print the registration ID. Select * From dba_change_icationication_regs in Oracle to view the record.
Application. setattribute ("databasechangeregistration", DCR );
Try
{
DCR. addlistener (New databasechangelistener () {// Add event listening
Public void ondatabasechangenotification (databasechangeevent e ){
Databasechangeevent. eventtype etype = E. geteventtype ();
System. Out. println ("receive" + etype + "event, regid =" + E. getregid ());
If (etype! = Databasechangeevent. eventtype. objchange) return; // if it is not a data change event, such as table structure change or table deletion, return.
Tablechangedescription [] tcds = E. gettablechangedescription ();

For (tablechangedescription TCM: tcds ){
System. Out. println ("changed table =" + TCM. gettablename ());
Java. util. enumset <tablechangedescription. tableoperation> tops = TCM. gettableoperations (); // obtain the table operation type
For (tablechangedescription. tableoperation top: tops)
System. Out. println ("... tableoperation =" + TOP );
Rowchangedescription [] RCDs = TCM. getrowchangedescription ();
For (rowchangedescription RCD: RCDs) {// obtain the Change Record description, including the change type insert, update, delete, and rowid.

System. Out. println ("... rowoperation =" + RCD. getrowoperation () + ", rowid =" + RCD. getrowid (). stringvalue ());
}
}
}
}
);
Statement stmt = oconn. createstatement ();
(Oraclestatement) stmt). setdatabasechangeregistration (DCR );
Resultset rs = stmt.exe cutequery ("select 1 from rpt_chenjun_ddjh_err_code where 1 = 2"); // indicates the change event associated with the rpt_chenjun_ddjh_err_code table.
// While (Rs. Next ()){}
For (string tablename: DCR. gettables ())
System. Out. println ("databasechangeregistration on" + tablename );
Rs. Close ();
Stmt. Close ();
}
Catch (sqlexception ex)
{
If (oconn! = NULL)
Oconn. unregisterdatabasechangenotification (DCR );
Throw ex;
}
Finally
{
Try
{Conn. Close ();} catch (exception innerex) {innerex. printstacktrace ();}
}

}
%>

<% IF ("unregister". Equals (type )){
Int regid = integer. parseint (request. getparameter ("regid "));
System. Out. println ("unregister regid =" + regid );
Connection conn = getconnection ();
Try {
Oracleconnection oconn = (oracleconnection) Conn. Unwrap (oracleconnection. Class );
// Oconn. unregisterdatabasechangenotification (regid); // unsupported features
// NPE, Bug!
Databasechangeregistration DCR = oconn. getdatabasechangeregistration (regid );
Oconn. unregisterdatabasechangenotification (DCR); // The registration will be destroyed in the server and in the driver
} Finally {
Try {conn. Close ();} catch (exception innerex) {innerex. printstacktrace ();}
}
}
%>

<%
If ("insert". Equals (type )){
Connection conn = NULL;
Try {
Conn = getconnection ();
Oracleconnection oconn = (oracleconnection) Conn. Unwrap (oracleconnection. Class );
Oconn. setautocommit (false );
Statement stmt2 = oconn. createstatement ();
Stmt2.executeupdate ("insert into rpt_chenjun_ddjh_err_code values (-21,'', null) ", statement. return_generated_keys );
Resultset autogeneratedkey = stmt2.getgeneratedkeys ();
If (autogeneratedkey. Next ())
System. Out. println ("inserted one row with rowid =" + autogeneratedkey. getstring (1 ));
Stmt2.close ();
Oconn. Commit ();
Oconn. Close ();
}
Catch (sqlexception ex) {ex. printstacktrace ();}
Finally {
If (Conn! = NULL) Conn. Close ();
}
}

%>