Trigger java events upon external Postgres database update

Question

Description: I have a java web-application (jsp + tomcat) on linux without any framework. I use postgresql as DBMS.

I have a table (options) and a Singleton Class (Options) that encapsulate it. The Options instance is loaded at application startup and remains there indefinitely.

If a user modifies the options a method (.refreshData()) updates the instance kept in memory.

Now the troubles: there is a remote service who has direct acces to the DB and updates some fields in the options table. I don't have control on this piece of code.

I would like to trigger the refresh method when the external service updates the options table. I also know that the service starts once per day at 3PM but i don't know when it ends.

The LISTEN - NOTIFY feature offered by postgresql (Postgres trigger to update Java cache) seems to me the most elegant way to reach this goal. Following this topic I am trying a simple listener and "adapted" for my needs (Sample of code in Postgress Documentation).

EDITED after @Craig suggestions:

public class OptionsListener extends Thread {
    private int threadMills = 1000;
    private Connection conn;
    private org.postgresql.PGConnection pgconn;
    private Options optionsInstance;
    private static final String DB_URL;
    private static final String DB_USERNAME;
    private static final String DB_PASSWORD;

    static {
        try {
            Context initContext = new InitialContext();
            Context envContext = (Context) initContext.lookup("java:/comp/env");

            DB_URL = (String) envContext.lookup("application/DB/url");
            DB_USERNAME = (String) envContext.lookup("application/DB/username");
            DB_PASSWORD = (String) envContext.lookup("application/DB/password");
        } catch (NamingException e) {
            throw new RuntimeException(e);
        }
    }

    OptionsListener(Options instance, int threadMillis) {
        optionsInstance = instance;
        this.threadMills = threadMillis;

        try {
            Class.forName("org.postgresql.Driver");
            conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
            pgconn = (PGConnection) DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

            Statement stmt = conn.createStatement();
            stmt.execute("LISTEN otionsUpdate");
            stmt.close();
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage(), e);
        }
    }

    @Override
    public void run() {
        while (true) {
            Log.addItem("Polling ?");
            try {
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery("SELECT 1");
                rs.close();
                stmt.close();

                PGNotification notifications[] = pgconn.getNotifications();
                if (notifications != null) {
                    Log.addItem("NOTIFY received");
                    optionsInstance.loadDbData();
                }

                Thread.sleep(threadMills); //tempo di attesa in millisecondi
            } catch (Exception e) {
                Log.addItem(getClass().getName() + " " + e.getMessage());
            }
        }
    }
}

In the Options Class I manually start the listener with this method:

public static void startExternalChangesListener(Options instance, int millis) {
    OptionsListener listener = new OptionsListener(instance, millis);
    listener.start();
}

And finally

Options.startExternalChangesListener(options, 5000);

This is the first time I tamper with Threads...

I created an AFTER UPDATE trigger that notifies the channel, and tested it throug PGAdmin3. It works like a charm but java seems not to notice...

Azathoth · Accepted Answer

Gotcha, it seems that the issue was in an unnecessary double connection (named pgcon).

I'm using jdbc-postgresql 9.2_p1003 and it works like a charm.

Here is the final code:

OptionsListener(Options instance, int threadMillis) {
    optionsInstance = instance;
    this.threadMills = threadMillis;

    try {
        Class.forName("org.postgresql.Driver");
        conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

        Statement stmt = conn.createStatement();
        stmt.execute("SET application_name = 'myapp'; LISTEN optionsupdate");
        stmt.close();
    } catch (SQLException e) {
        Log.addItem(getClass().getName() + " sql error:" + e.getMessage());
    } catch (ClassNotFoundException e) {
        throw new RuntimeException(e.getMessage(), e);
    }
}

@Override
public void run() {
    while (true) {
        try {
            PGNotification notifications[] = ((PGConnection) conn).getNotifications();
            if (notifications != null) {
                optionsInstance.loadDbData();
            }

            Thread.sleep(threadMills); 
        } catch (SQLException sqle) {
            Log.addItem(getClass().getName() + " sql error:" + sqle.getMessage());
        } catch (InterruptedException ie) {
            Log.addItem(getClass().getName() + " thread error: " + ie.getMessage());
        } 
    }
}

Trigger java events upon external Postgres database update

Answers (2)

Related Questions