How to implement a db listener in Java

Question

I have a requirement where, if the record is inserted into the db table, then the java process should automatically run. What is the easiest way to implement a db listener?

Answer 1

I have a solution for Oracle. You do not need to create your own, because now that Oracle has purchased Java, it has released for this listener. As far as I know, this does not use polling internally, instead, notifications are sent to the Java side (possibly based on some kind of 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 {...} 

Suppose 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 statements or any exception handling.

Answer 2

A similar answer here: how to create a database listener using java?

You can do this with a message queue that supports transactions and simply turn off the message when the transaction ends or (close the connection) for databases that do not support notifications. This is for the most part you will have to manually notify and track what to notify.

Spring provides some automated transaction support for AMQP and JMS . A simpler alternative that you can use is Guava AsyncEventBus, but this will only work for one JVM. For all of the options below, I recommend that you tell the rest of your message queue platform.

Option - Non-settlement not related to the database

ORM option

Some libraries, such as the Hibernate JPA, have entity listeners that make this easier, but that is because they assume that they control all CRUDing.

For regular JDBC you will have to do your own bookkeeping. That is, after the connection is fixed or closed, you then send a message to MQ that something is updated.

JDBC parsing

One difficult option for doing accounting is to wrap / decorate your java.sql.DataSource and / or java.sql.Connection in your custom, for example, so that you send a message to commit() (and close). I believe that some federated caching systems do this. You can capture the executed SQL and parse it to see if its INSERT or UPDATE, but with very sophisticated parsing and metadata, you will not get row level listening. Unfortunately, I have to admit that this is one of the benefits provided by ORM, as it knows that you are updating.

Option Dao

The best option, if you are not using ORM, simply manually send a message to the DAO after the transaction is closed, that the row has been updated. Just make sure the transaction is closed before sending the message.

Option - non-database polling

Follow @GlenBest guidelines to some extent.

I am a couple of things that I would do differently. I would select a timer or make it so that only one server starts a timer (i.e. a scheduler). I would just use a ScheduledExecutorService (preferably wrapping it in a Guava ListenerScheduledExecutorService ) instead of Quartz (IMHO uses quartz to poll super overkill).

Far from all of your tables that you want to see must 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

With Postgres NOTIFY you still need to interrogate to some extent so that you complete most of the above and then send a message to the bus.

Answer 3

The general solution is likely to be to create a trigger in the table of interest, notifying listeners about INSERT events. Some databases have formalized facilities for this process notification. For example:

Oracle:

• DBMS_ALERT is a simple tool for this notification.
• Oracle AQ / Oracle Streams provide more sophisticated queuing mechanisms

Postgres:

• The NOTIFY statement is a simple means of notifying you.

Others:

• Other databases may have similar notification mechanisms that I do not know about.
• You can always implement your own event notification queue tables by inserting an event into the event table that is consumed / processed by the Java process. Law and enforcement can be quite complex.

Answer 4

Assumptions:

• Having standard portable code is more important than instantly executing a Java program in real time. You want to allow portability of alternative future technologies (for example, avoid proprietary database events, external triggers). The Java process may work a little after adding a record to the table (for example, after 10 seconds). that is, both graphs + polling or a trigger / message / event in real time are acceptable.

• If several rows are added to the table at once, you want to start one process, not many. A DB trigger starts a java process for each row - inappropriate.

• Quality of service is important. Even if there is a fatal hardware or software error, you want the java program to start again and process incomplete data.

• You want to apply strong security standards to your environment (for example, avoid directly executing Java commands or DBs)

• Do you want to minimize code

  • Standard Java kernel code without depending on the database’s own functionality:

    • Use the ScheduledExecutorService or Quartz scheduler (or the unix cron job or the Windows task scheduler) to run a Java program every minute (or can do it every 10 seconds). It acts as a scheduler and watchdog, ensuring that the program runs around the clock. Quartz can also be deployed to the application server.
    • Try to execute the java program in just 1 minute (or 10 seconds), looping, querying DB via JDBC and sleeping for a few seconds, and then finally quitting.

  • If you have an application on the application server: Create a Bean session that uses the timer service and will query the table again through the JDBC Bean timer service .

  • You have a DB trigger that writes / appends to a file. Use java 7 filewatcher to run logic when file changes Java 7 File Watcher

There is another option: using an open source ESB with the logic of starting the DB adapter (for example, Fuse or Mule or OpenAdapter), but this gives powerful functionality that does not depend on your stated requirements, and takes a lot of time and is difficult to install and learn.

EJB timer example using @Schedule:

 public class ABCRequest { // normal java bean with data from DB } @Singleton public class ABCProcessor { @Resource DataSource myDataSource; @EJB ABCProcessor abcProcessor; // runs every 3 minutes @Schedule(minute="*/3", hour="*") public void processNewDBData() { // run a JDBC prepared statement to see if any new data in table, put data into RequestData try { Connection con = dataSource.getConnection(); PreparedStatement ps = con.prepareStatement("SELECT * FROM ABC_FEED;"); ... ResultSet rs = ps.executeQuery(); ABCRequest abcRequest while (rs.hasNext()) { // population abcRequest } abcProcessor.processABCRequest(abcRequst); } ... } } @Stateless public class class ABCProcessor { public void processABCRequest(ABCRequest abcRequest) { // processing job logic } } 

See also: See this answer for sending CDI event objects from an EJB to a web container.

Answer 5

I am not sure how this solution meets your needs, but can be considered as an option. If you use oracle, then oracle you can write a java program and compile it as an oracle function. you can call your java program from the post insert trigger.

Java program in Oracle DB