这是indexloc提供的服务，不要输入任何密码
Skip to content

BAEL-7988: Event-Driven LISTEN/NOTIFY Support in Java using PostgreSQL #18582

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 5 commits into from
Jun 8, 2025
Merged

BAEL-7988: Event-Driven LISTEN/NOTIFY Support in Java using PostgreSQL #18582

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
5 commits
Select commit Hold shift + click to select a range
958a69a
BAEL-7988: Event-Driven LISTEN/NOTIFY Support in Java using PostgreSQL
sazzer May 28, 2025
720f496
Updated the tests to actually assert on the notifications
sazzer May 29, 2025
74f97c1
Used Awaitility in pgsql test
sazzer May 30, 2025
c3f0d82
Swapped Thread.sleep for a blocking call
sazzer Jun 2, 2025
ad64241
Test to show notifications from triggers
sazzer Jun 3, 2025
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
18 changes: 18 additions & 0 deletions persistence-modules/core-java-persistence-4/pom.xml
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -47,13 +47,29 @@
<artifactId>spring-boot-starter</artifactId>
<version>${springframework.boot.spring-boot-starter.version}</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>${postgresql.version}</version>
</dependency>
<dependency>
<groupId>com.impossibl.pgjdbc-ng</groupId>
<artifactId>pgjdbc-ng</artifactId>
<version>${pgjdbc-ng.version}</version>
</dependency>

<dependency>
<groupId>org.mockito</groupId>
<artifactId>mockito-junit-jupiter</artifactId>
<version>5.16.0</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.awaitility</groupId>
<artifactId>awaitility</artifactId>
<version>${awaitility.version}</version>
<scope>test</scope>
</dependency>
</dependencies>

<build>
Expand All @@ -70,8 +86,10 @@
</build>

<properties>
<awaitility.version>4.3.0</awaitility.version>
<h2.version>2.3.230</h2.version>
<postgresql.version>42.7.3</postgresql.version>
<pgjdbc-ng.version>0.8.9</pgjdbc-ng.version>
<jdbc-stream.version>0.1.1</jdbc-stream.version>
<junit-bom.version>5.11.1</junit-bom.version>
<mysql-connecter-j.version>8.0.33</mysql-connecter-j.version>
Expand Down
155 changes: 155 additions & 0 deletions ...core-java-persistence-4/src/test/java/com/baeldung/listennotify/ListenNotifyLiveTest.java
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,155 @@
package com.baeldung.listennotify;

import com.impossibl.postgres.api.jdbc.PGNotificationListener;
import org.junit.jupiter.api.Test;
import org.postgresql.PGNotification;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.time.Duration;
import java.util.HashSet;
import java.util.Set;

import static org.awaitility.Awaitility.await;
import static org.junit.jupiter.api.Assertions.assertEquals;

public class ListenNotifyLiveTest {
private static final Logger LOG = LoggerFactory.getLogger(ListenNotifyLiveTest.class);

private static final String POSTGRES_URL = "jdbc:postgresql://localhost:5432/postgres";
private static final String PGJDBC_URL = "jdbc:pgsql://localhost:5432/postgres";
private static final String USERNAME = "postgres";
private static final String PASSWORD = "mysecretpassword";

private void sendNotifications() throws SQLException{
try (Connection connection = DriverManager.getConnection(POSTGRES_URL, USERNAME, PASSWORD)) {
try (Statement statement = connection.createStatement()) {
statement.execute("NOTIFY my_channel, 'Hello, NOTIFY!'");
}

try (PreparedStatement statement = connection.prepareStatement("SELECT pg_notify(?, ?)")) {
statement.setString(1, "my_channel");
statement.setString(2, "Hello, pg_notify!");
statement.execute();
}
}
}

@Test
void whenUsingPostgresqlDriver_thenNotificationsAreReceived() throws SQLException, InterruptedException {
try (Connection connection = DriverManager.getConnection(POSTGRES_URL, USERNAME, PASSWORD)) {
try (Statement statement = connection.createStatement()) {
statement.execute("LISTEN my_channel");
}

sendNotifications();

var pgConnection = connection.unwrap(org.postgresql.PGConnection.class);
Set<String> receivedNotifications = new HashSet<>();

while (receivedNotifications.size() < 2) {
PGNotification[] notifications = pgConnection.getNotifications(0);
if (notifications != null) {
LOG.info("Received {} notifications", notifications.length);
for (PGNotification notification : notifications) {
LOG.info("Received notification: Channel='{}', Payload='{}', PID={}",
notification.getName(),
notification.getParameter(),
notification.getPID());
receivedNotifications.add(notification.getParameter());
}
}
}

assertEquals(Set.of("Hello, NOTIFY!", "Hello, pg_notify!"), receivedNotifications);
}
}

@Test
void whenUsingPgsqlDriver_thenNotificationsAreReceivedViaListener() throws SQLException, InterruptedException {
try (Connection connection = DriverManager.getConnection(PGJDBC_URL, USERNAME, PASSWORD)) {
try (Statement statement = connection.createStatement()) {
statement.execute("LISTEN my_channel");
}

var pgConnection = connection.unwrap(com.impossibl.postgres.api.jdbc.PGConnection.class);

Listener pgNotificationListener = new Listener();
pgConnection.addNotificationListener(pgNotificationListener);

sendNotifications();

await()
.atMost(Duration.ofSeconds(5))
.until(() -> pgNotificationListener.receivedNotifications.size() == 2);

assertEquals(Set.of("Hello, NOTIFY!", "Hello, pg_notify!"), pgNotificationListener.receivedNotifications);

}
}

@Test
void whenUsingTriggers_thenNotificationsAreSent() throws SQLException {
try (Connection connection = DriverManager.getConnection(POSTGRES_URL, USERNAME, PASSWORD)) {
// First set up the database state
try (Statement statement = connection.createStatement()) {
statement.execute("CREATE TABLE IF NOT EXISTS listen_notify_trigger(id INT PRIMARY KEY)");
statement.execute("TRUNCATE listen_notify_trigger");

statement.execute("""
CREATE OR REPLACE FUNCTION notify_table_change() RETURNS TRIGGER AS $$
BEGIN
PERFORM pg_notify('table_change', TG_TABLE_NAME);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
""");

statement.execute("""
CREATE OR REPLACE TRIGGER table_change
AFTER INSERT OR UPDATE OR DELETE ON listen_notify_trigger
FOR EACH ROW EXECUTE PROCEDURE notify_table_change();
""");
}

try (Statement statement = connection.createStatement()) {
statement.execute("LISTEN table_change");
}

try (Statement statement = connection.createStatement()) {
statement.execute("INSERT INTO listen_notify_trigger(id) VALUES (1)");
}

var pgConnection = connection.unwrap(org.postgresql.PGConnection.class);
Set<String> receivedNotifications = new HashSet<>();

while (receivedNotifications.isEmpty()) {
PGNotification[] notifications = pgConnection.getNotifications(0);
if (notifications != null) {
LOG.info("Received {} notifications", notifications.length);
for (PGNotification notification : notifications) {
LOG.info("Received notification: Channel='{}', Payload='{}', PID={}",
notification.getName(),
notification.getParameter(),
notification.getPID());
receivedNotifications.add(notification.getName() + " - " + notification.getParameter());
}
}
}

assertEquals(Set.of("table_change - listen_notify_trigger"), receivedNotifications);
}
}

private static class Listener implements PGNotificationListener {
Set<String> receivedNotifications = new HashSet<>();

@Override
public void notification(int processId, String channelName, String payload) {
LOG.info("Received notification: Channel='{}', Payload='{}', PID={}",
channelName, payload, processId);
receivedNotifications.add(payload);
}
}
}