How do I get asynchronous / event-driven LISTEN/NOTIFY support in Java using a Postgres database?
Use the pgjdbc-ng driver.
http://impossibl.github.io/pgjdbc-ng/
It supports async notifications, without polling. I have used it successfully.
See https://database-patterns.blogspot.com/2014/04/postgresql-nofify-websocket-spring-mvc.html
Oleg has a nice example answer as well
Here's an asynchronous pattern using com.impossibl.postgres.api (pgjdbc-ng-0.6-complete.jar) with JDK 1.8:
import com.impossibl.postgres.api.jdbc.PGConnection;
import com.impossibl.postgres.api.jdbc.PGNotificationListener;
import com.impossibl.postgres.jdbc.PGDataSource;
import java.sql.Statement;
public static void listenToNotifyMessage(){
PGDataSource dataSource = new PGDataSource();
dataSource.setHost("localhost");
dataSource.setPort(5432);
dataSource.setDatabase("database_name");
dataSource.setUser("postgres");
dataSource.setPassword("password");
PGNotificationListener listener = (int processId, String channelName, String payload)
-> System.out.println("notification = " + payload);
try (PGConnection connection = (PGConnection) dataSource.getConnection()){
Statement statement = connection.createStatement();
statement.execute("LISTEN test");
statement.close();
connection.addNotificationListener(listener);
while (true){ }
} catch (Exception e) {
System.err.println(e);
}
}
Create a trigger function for your database:
CREATE OR REPLACE FUNCTION notify_change() RETURNS TRIGGER AS $$
BEGIN
SELECT pg_notify('test', TG_TABLE_NAME);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Assign a trigger for every table you want to track:
CREATE TRIGGER table_change
AFTER INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW EXECUTE PROCEDURE notify_change();