title: Trigger java events upon external Postgres database update
body: <p><strong>Description:</strong> I have a java web-application (jsp + tomcat) on linux without any framework. I use postgresql as DBMS.</p>
  
  <p>I have a table (<em>options</em>) and a Singleton Class (<em>Options</em>) that encapsulate it. The Options instance is loaded at application startup and remains there indefinitely.</p>
  
  <p>If a user modifies the options a method (<strong>.refreshData()</strong>) updates the <em>instance</em> kept in memory.</p>
  
  <p><strong>Now the troubles:</strong> there is a remote service who has direct acces to the DB and updates some fields in the <em>options</em> table. I don't have control on this piece of code.</p>
  
  <p>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.</p>
  
  <p>The <strong>LISTEN - NOTIFY</strong> feature offered by postgresql (<a href="https://stackoverflow.com/questions/13694778/postgres-trigger-to-update-java-cache">Postgres trigger to update Java cache</a>) 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 (<a href="http://jdbc.postgresql.org/documentation/91/listennotify.html" rel="nofollow noreferrer">Sample of code in Postgress Documentation</a>).</p>
  
  <p><strong>EDITED after @Craig suggestions:</strong></p>
  
  <pre><code>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());
              }
          }
      }
  }
  </code></pre>
  
  <p>In the Options Class I manually start the listener with this method:</p>
  
  <pre><code>public static void startExternalChangesListener(Options instance, int millis) {
      OptionsListener listener = new OptionsListener(instance, millis);
      listener.start();
  }
  </code></pre>
  
  <p>And finally</p>
  
  <pre><code>Options.startExternalChangesListener(options, 5000);
  </code></pre>
  
  <p>This is the first time I tamper with Threads... </p>
  
  <p>I created an <em>AFTER UPDATE trigger</em> that notifies the channel, and tested it throug PGAdmin3. It works like a charm but java seems not to notice...</p>