Asynchronous notification handling with NoticeListener v42.7.3.2

The EDB Postgres Advanced Server JDBC Connector provides asynchronous notification handling functionality. A notification is a message generated by the server when an SPL (or PL/pgSQL) program executes a RAISE NOTICE statement. Each notification is sent from the server to the client application. To intercept a notification in a JDBC client, an application must create a NoticeListener object (or, more typically, an object derived from NoticeListener).

It's important to understand that a notification is sent to the client as a result of executing an SPL (or PL/pgSQL) program. To generate a notification, you must execute an SQL statement that invokes a stored procedure, function, or trigger. The notification is delivered to the client as the SQL statement executes. Notifications work with any type of statement object: CallableStatement objects, PreparedStatement objects, or simple Statement objects. A JDBC program intercepts a notification by associating a NoticeListener with a Statement object. When the Statement object executes an SQL statement that raises a notice, JDBC invokes the noticeReceived() method in the associated NoticeListener.

The following shows an SPL procedure that loops through the emp table and gives each employee a 10% raise. As each employee is processed, adjustSalary executes a RAISE NOTICE statement. (In this case, the message contained in the notification reports progress to the client application.)

CREATE OR REPLACE PROCEDURE adjustSalary
IS
  v_empno         NUMBER(4);
  v_ename         VARCHAR2(10);
  CURSOR emp_cur IS SELECT empno, ename FROM emp;
BEGIN
  OPEN emp_cur;
  LOOP
    FETCH emp_cur INTO v_empno, v_ename;
    EXIT WHEN emp_cur%NOTFOUND;

    UPDATE emp SET sal = sal * 1.10 WHERE empno = v_empno;
    RAISE NOTICE 'Salary increased for %', v_ename;
  END LOOP;
  CLOSE emp_cur;
END;

The following shows how to create a NoticeListener that intercepts notifications in a JDBC application:

public void NoticeExample(Connection con)
{
  CallableStatement stmt;
  try
  {
    stmt = con.prepareCall("{call adjustSalary()}");

    MyNoticeListener listener = new MyNoticeListener();
    ((BaseStatement)stmt).addNoticeListener(listener);
    stmt.execute();
    System.out.println("Finished");
  }
  catch (SQLException e)
  {
    System.out.println("An error has occurred.");
    System.out.println("See full details below.");
    e.printStackTrace();
  }
}
class MyNoticeListener implements NoticeListener
{
  public MyNoticeListener()
  {
  }

  public void noticeReceived(SQLWarning warn)
  {
    System.out.println("NOTICE: "+ warn.getMessage());
  }
}

The NoticeExample() method is straightforward. It expects a single argument from the caller, a Connection object:

public void NoticeExample(Connection con)

NoticeExample() begins by preparing a call to the adjustSalary procedure shown previously. As you would expect, con.prepareCall() returns a CallableStatement object. Before executing the CallableStatement, you must create an object that implements the NoticeListener interface and add that object to the list of NoticeListeners associated with the CallableStatement:

CallableStatement stmt = con.prepareCall("{call adjustSalary()}");
MyNoticeListener listener = new MyNoticeListener();
((BaseStatement)stmt).addNoticeListener(listener);

Once the NoticeListener is in place, the NoticeExample method executes the CallableStatement (invoking the adjustSalary procedure on the server) and displays a message to the user:

stmt.execute();
System.out.println("Finished");

Each time the adjustSalary procedure executes a RAISE NOTICE statement, the server sends the text of the message ("Salary increased for ...") to the Statement (or derivative) object in the client application. JDBC invokes the noticeReceived() method (possibly many times) before the call to stmt.execute() completes.

class MyNoticeListener implements NoticeListener
{
  public MyNoticeListener()
  {
  }

  public void noticeReceived(SQLWarning warn)
  {
    System.out.println("NOTICE: "+ warn.getMessage());
  }
}

When JDBC calls the noticeReceived() method, it creates an SQLWarning object that contains the text of the message generated by the RAISE NOTICE statement on the server.

Each Statement object keeps a list of NoticeListeners. When the JDBC driver receives a notification from the server, it consults the list maintained by the Statement object. If the list is empty, the notification is saved in the Statement object. (You can retrieve the notifications by calling stmt.getWarnings() once the call to execute() completes.) If the list isn't empty, the JDBC driver delivers an SQLWarning to each listener in the order in which the listeners were added to the Statement.