JDBC and Oracle Database Change Notification

Below is a simple example of a cutdown example of using DCN in Oracle with JDBC.

I plan on presenting this to our developers as an alternative to the ATG InventoryCache manager module.

import java.sql.*;
import java.util.*;
import oracle.jdbc.*;
import oracle.jdbc.dcn.*;
import oracle.jdbc.dcn.RowChangeDescription.*;

public class ATGInventoryChangeNotify {
  String URL = "jdbc:oracle:thin:test/[email protected]:1521/express.home";
  Properties prop;

  public static void main(String[] argv) {

    ATGInventoryChangeNotify dcn = new ATGInventoryChangeNotify();
    try {
      dcn.prop = new Properties();
      dcn.run();
    }
    catch(Exception e) {
      e.printStackTrace();
    }
  }

  void run() throws SQLException {
    OracleConnection conn = (OracleConnection)DriverManager.getConnection(URL,prop);

    Properties prop = new Properties();
    prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS,"true");
    DatabaseChangeRegistration dcr = conn.registerDatabaseChangeNotification(prop);

    try {
      dcnListener list = new dcnListener(this);
      dcr.addListener(list);

      Statement stmt = conn.createStatement();
      ((OracleStatement)stmt).setDatabaseChangeRegistration(dcr);
      ResultSet rs = stmt.executeQuery("select * from dept where 1 = 2");
      rs.close();
      stmt.close();
    }
    catch(Exception e) {
      //clean up our registration
      if(conn != null)
        conn.unregisterDatabaseChangeNotification(dcr);
      e.printStackTrace();
    }
    finally {
      try {
        conn.close();
      }
      catch(Exception e){
        e.printStackTrace();
      }
    }

    try {
      Thread.currentThread().join();
    }
    catch (Exception e) {
      e.printStackTrace();
    }

    finally {
      OracleConnection conn3 = (OracleConnection)DriverManager.getConnection(URL,prop);
      conn3.unregisterDatabaseChangeNotification(dcr);
      conn3.close();
    }
  }
}

class dcnListener implements DatabaseChangeListener {
  ATGInventoryChangeNotify dcn;
  dcnListener(ATGInventoryChangeNotify dem) {
    dcn = dem;
  }

  public void onDatabaseChangeNotification(DatabaseChangeEvent e) {
    TableChangeDescription[] tc = e.getTableChangeDescription();

    for (int i = 0; i < tc.length; i++) {
      RowChangeDescription[] rcds = tc[i].getRowChangeDescription();
      for (int j = 0; j < rcds.length; j++) {
    	System.out.println(rcds[j].getRowOperation() + " " + rcds[j].getRowid().stringValue());
	  }
	}
    synchronized( dcn ){
      dcn.notify();
    }
  }
}

2 comments for “JDBC and Oracle Database Change Notification

  1. Jason
    March 2, 2018 at 9:44 PM

    Thanks very much for this.

  2. Savani
    September 17, 2019 at 12:41 AM

    @All – Does this approach also works for Postgres DB ?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.