How to implement a db listener in Java

Question

I have a requirement where if a record is inserted in a db table , then automatically a java process needs to be executed.What is the easiest way to implement a db listener ?

Answer 1

I have a solution for Oracle. You don't need to create your own since now that Oracle bought Java it released a listener for it. As far as I know this does not use polling internally, instead notifications are pushed to the Java side (probably based on some trigger):

public interface oracle.jdbc.dcn.DatabaseChangeListener 
extends java.util.EventListener {
    void onDatabaseChangeNotification(oracle.jdbc.dcn.DatabaseChangeEvent arg0);
}

And you can implement it like this (this is just a sample):

public class DBListener implements DatabaseChangeListener {
    private DbChangeNotification toNotify;

    public BNSDBListener(DbChangeNotification toNotify) {
        this.toNotify = toNotify;
    }

    @Override
    public void onDatabaseChangeNotification(oracle.jdbc.dcn.DatabaseChangeEvent e) {
        synchronized( toNotify ) {
            try {
                toNotify.notifyDBChangeEvent(e); //do sth
            } catch (Exception ex) {
                Util.logMessage(CLASSNAME, "onDatabaseChangeNotification", 
                    "Errors on the notifying object.", true);
                Util.printStackTrace(ex);
                Util.systemExit();                                       
            }
        }       
    }
}

EDIT:
You can use the following class to register: oracle.jdbc.OracleConnectionWrapper

public class oracle.jdbc.OracleConnectionWrapper implements oracle.jdbc.OracleConnection {...}

Say you create a method somewhere:

public void registerPushNotification(String sql) {
    oracle.jdbc.driver.OracleConnection oracleConnection = ...;//connect to db

    dbProperties.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "true");
    dbProperties.setProperty(OracleConnection.DCN_QUERY_CHANGE_NOTIFICATION, "true");

    //this is what does the actual registering on the db end
    oracle.jdbc.dcn.DatabaseChangeRegistration dbChangeRegistration= oracleConnection.registerDatabaseChangeNotification(dbProperties);

    //now you can add the listener created before my EDIT
    listener = new DBListener(this);
    dbChangeRegistration.addListener(listener);

    //now you need to add whatever tables you want to monitor
    Statement stmt = oracleConnection.createStatement();
    //associate the statement with the registration:
    ((OracleStatement) stmt).setDatabaseChangeRegistration(dbChangeRegistration); //look up the documentation to this method [http://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleStatement.html#setDatabaseChangeRegistration_oracle_jdbc_dcn_DatabaseChangeRegistration_]

    ResultSet rs = stmt.executeQuery(sql); //you have to execute the query to link it to the statement for it to be monitored
    while (rs.next()) { ...do sth with the results if interested... }

    //see what tables are being monitored
    String[] tableNames = dbChangeRegistration.getTables();
    for (int i = 0; i < tableNames.length; i++) {
        System.out.println(tableNames[i]    + " has been registered.");
    }
    rs.close();
    stmt.close();
}

This example does not include try-catch clauses or any exception handling.

Answer 2

A similar Answer here: How to make a database listener with java?

You can do this with a message queue that supports transactions and just fire off a message when the transaction is comitted or (connection closed) for databases that don't support notifications. That is for the most part you will have to manually notify and keep track of what to notify.

Spring provides some auto transaction support for AMQP and JMS. A simpler alternative you could use is Guava's AsyncEventBus but that will only work for one JVM. For all of the options below I recommend you notify the rest of your platform with a message queue.

Option - Non-polling non-database specific

ORM Option

Some libraries like Hibernate JPA have entity listeners that make this easier but thats because they assume that they manage all of the CRUDing.

For regular JDBC you'll have to do your own book keeping. That is after the connection is committed or closed you then send the message to MQ that something has been updated.

JDBC Parsing

One complicated option for book keeping is to wrap/decorate your java.sql.DataSource and/or java.sql.Connection in a custom one such that on commit() (and close) you then send a message. I believe some federated caching systems do this. You could trap the executed SQL and parse to see if its an INSERT or UPDATE but with out very complicated parsing and meta data you will not get row level listening. Sadly I have to admit this is one of the advantages an ORM provides in that it knows what your updating.

Dao Option

The best option if your not using an ORM is to just manually send a message in your DAO after the transaction is closed that a row has been updated. Just make sure the transaction is closed before you send the message.

Option - Polling non-database specific

Somewhat follow @GlenBest recommendation.

I couple of things that I would do differently. I would externalize the timer or make it so that only one server runs the timer (ie scheduler). I would just use ScheduledExecutorService (preferable wrapping it in Guava's ListenerScheduledExecutorService) instead of Quartz (IMHO using quartz for polling super overkill).

Far all of your tables you want to watch you should add a "notified" column.

Then you do something like:

// BEGIN Transaction
List<String> ids = execute("SELECT id FROM table where notified = 'f'");
//If db not transactional either insert ids in a tmp table or use IN clause
execute("update table set notified = 't' where notified = 'f'")
// COMMIT Transaction
for (String id : ids) { mq.sendMessage(table, id); }

Option - db specific

With Postgres NOTIFY you'll still need to poll to some extent so you'll be doing most of the above and then send the message to the bus.

Answer 3

A general solution would probably consist in creating a trigger on the table of interest, notifying any listeners about INSERT events. Some databases have formalised means for such inter-process notification. For instance:

Oracle:

• The DBMS_ALERT is a simple means for such notification
• Oracle AQ / Oracle Streams provide more sophisticated queue mechanisms

Postgres:

• The NOTIFY statement is a simple means for such notification

Others:

• There might be similar notification mechanisms in other databases, that I'm not aware of.
• You can always implement your own event notification queue tables by inserting an event in an event table, which is consumed / polled by a Java process. Getting this right and performant may be quite tricky, though.