I need to monitor an oracle table for changes and I'm trying to choose between Database Change Notifications and Advanced Queuing.
I don't understand certain points in Database JDBC Developer's Guide and getDatabaseChangeRegistration javadoc
If I register a DB change registration (DCR) with the NTF_QOS_RELIABLE flag, I expect the notifications to persist while my jdbc application is down. However, I don't see a way to restore existing DCR after my app restarts: according to javadoc, getDatabaseChangeRegistration() is only for PLSQL listeners. And it seems that jdbc DCRs are destroyed when my app dies and I don't even have to unregister them.
After my program restarts I sometimes get notifications with a previous registration id. It is not necessary to call stmt.setDatabaseChangeRegistration() every time I start my app.
I never receive changes that happened while my app was down and this is the biggest problem. What does NTF_QOS_RELIABLE do then?
package org.foo;
import static oracle.jdbc.OracleConnection.*;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.Locale;
import java.util.Properties;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.dcn.DatabaseChangeEvent;
import oracle.jdbc.dcn.DatabaseChangeListener;
import oracle.jdbc.dcn.DatabaseChangeRegistration;
// CHECKSTYLE.OFF: Name|Reg
public final class TestDbListener {
private TestDbListener() {}
public static void main(final String[] args) throws Exception {
Locale.setDefault(Locale.US);
Class.forName("oracle.jdbc.OracleDriver");
final OracleConnection conn =
(OracleConnection) DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.56.150:1521:xe", "scott", "tiger");
final Properties props = new Properties();
props.setProperty(NTF_QOS_RELIABLE, "true");
final DatabaseChangeRegistration dcr = conn.registerDatabaseChangeNotification(props);
final DCNListener list = new DCNListener();
dcr.addListener(list);
if (true) {
// now you need to add whatever tables you want to monitor
final OracleStatement stmt = (OracleStatement) conn.createStatement();
// associate the statement with the registration:
stmt.setDatabaseChangeRegistration(dcr);
final String sql = "select * from a where 1=2";
final ResultSet rs = stmt.executeQuery(sql);
}
final String[] tableNames = dcr.getTables();
for (int i = 0; i < tableNames.length; i++) {
System.out.println(tableNames[i] + " has been registered.");
}
Thread.sleep(1000000);
// rs.close();
}
}
class DCNListener implements DatabaseChangeListener {
@Override
public void onDatabaseChangeNotification(final DatabaseChangeEvent event) {
System.out.println("onDatabaseChangeNotification: " + event);
}
}
SQL:
-- sysdba:
-- grant change notification to scott;
-- scott:
create table a ( a int );
insert into a values ( 1 );
commit;