7.7 Asynchronous Notification Handling with NoticeListener

The 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 is 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.

Listing 1.18-a 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). Listing 1.18-b will demonstrate how to create a NoticeListener that intercepts each notification.

Listing 1.18-a

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;

Listing 1.18-b shows how to intercept notifications in a JDBC application.

Listing 1.18-b

 

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, a Connection object, from the caller:

public void NoticeExample(Connection con)

NoticeExample() begins by preparing a call to the adjustSalary procedure shown in example 1.10-a. 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, 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.

Notice that 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 is not empty, the JDBC driver delivers an SQLWarning to each listener, in the order in which the listeners were added to the Statement.