Hibernate postgresql notify functionality

Question

I`m writing an app that is using hibernate + JPA as an ORM and postgresql 9.3 as a database backend and I need to react with some java code to some database events.
To be more precise I want to build an trigger that uses pg_notify() when a new row is inserted into the table.
I have read about that but all the tutorials were with a direct jdbc connection and not through hibernate.
I (think that I) cannot use hibernate events as the rows are not inserted through hibernate but by a 3rd party app.

Is there any way that I can receive notifications send with pg_notify through hibernate ?

-- update
Right now I have an classCastException :

java.lang.ClassCastException: com.sun.gjc.spi.jdbc40.ConnectionWrapper40 cannot be cast to org.postgresql.PGConnection
    at com.xxx.core.impl.dao.PostgresqlLowLevelNotificationDAOImpl$1.execute(PostgresqlLowLevelNotificationDAOImpl.java:36)
    at com.xxx.core.impl.dao.PostgresqlLowLevelNotificationDAOImpl$1.execute(PostgresqlLowLevelNotificationDAOImpl.java:1)

I have to mention that I use Glassfish 4.0 as AS. The connection pool is created on glassfish and accessed by app through jndi. Also the EntityManager is injected by the container with Spring. Here is my code:

@Named
public class PostgresqlLowLevelNotificationDAOImpl implements PostgresqlLowLevelNotificationDAO{

    @PersistenceContext(type =PersistenceContextType.TRANSACTION,synchronization=SynchronizationType.SYNCHRONIZED,unitName="CCPU")
    private EntityManager em;

@Override
public ArrayList<PGNotification> getNotifications(){

    Session session = em.unwrap(Session.class);

    PGNotification[] notifications = session.doReturningWork(new ReturningWork<PGNotification[]>() {

        @Override
        public PGNotification[] execute(Connection connection) throws SQLException {
            PGNotification[] notifications = ((PGConnection) connection).getNotifications();
            return notifications;
        }

    });

    return (ArrayList) Arrays.asList(notifications);
}

}

-- update
I have fixed the classcast exception :

@Override
public ArrayList<PGNotification> getNotifications(){

    Session session = em.unwrap(Session.class);


    PGNotification[] notifications = session.doReturningWork(new ReturningWork<PGNotification[]>() {

        @Override
        public PGNotification[] execute(Connection connection) throws SQLException {

            PGConnection pgc = null; 

            if (connection.isWrapperFor(PGConnection.class)) {
                pgc = (PGConnection) connection.unwrap(PGConnection.class);
            }                               

            PGNotification[] notifications = pgc.getNotifications();                
            return notifications;
        }
    });

But it still seems that i do not receive the notifications.

update ---
After i have implemented solution proposed by Neil i have this error in glassfish logs when i undeploy app:

    2014-06-27T11:03:24.278+0300|SEVERE: The web application [/myApp] created a ThreadLocal    with key of type [io.netty.buffer.PooledByteBufAllocator$1] (value [io.netty.buffer.PooledByteBufAllocator$1@28ad6479]) and a value of type [io.netty.buffer.PoolThreadCache] (value [io.netty.buffer.PoolThreadCache@f9f58cc]) but failed to remove it when the web application was stopped. Threads are going to be renewed over time to try and avoid a probable memory leak.
2014-06-27T11:03:24.279+0300|SEVERE: The web application [/myApp] created a ThreadLocal with key of type [io.netty.util.Recycler$1] (value [io.netty.util.Recycler$1@267ec117]) and a value of type [io.netty.util.Recycler.Stack] (value [io.netty.util.Recycler$Stack@4bb6e0bf]) but failed to remove it when the web application was stopped. Threads are going to be renewed over time to try and avoid a probable memory leak.
2014-06-27T11:03:24.279+0300|SEVERE: The web application [/myApp] created a ThreadLocal with key of type [io.netty.util.Recycler$1] (value [io.netty.util.Recycler$1@535d426e]) and a value of type [io.netty.util.Recycler.Stack] (value [io.netty.util.Recycler$Stack@fb46e84]) but failed to remove it when the web application was stopped. Threads are going to be renewed over time to try and avoid a probable memory leak.
2014-06-27T11:03:24.280+0300|SEVERE: The web application [/myApp] created a ThreadLocal with key of type [io.netty.util.internal.ThreadLocalRandom$2] (value [io.netty.util.internal.ThreadLocalRandom$2@ec3a42a]) and a value of type [io.netty.util.internal.ThreadLocalRandom] (value [io.netty.util.internal.ThreadLocalRandom@4e4ec8f8]) but failed to remove it when the web application was stopped. Threads are going to be renewed over time to try and avoid a probable memory leak.
2014-06-27T11:03:24.280+0300|SEVERE: The web application [/myApp] created a ThreadLocal with key of type [io.netty.util.Recycler$1] (value [io.netty.util.Recycler$1@166c39f2]) and a value of type [io.netty.util.Recycler.Stack] (value [io.netty.util.Recycler$Stack@1b504a5e]) but failed to remove it when the web application was stopped. Threads are going to be renewed over time to try and avoid a probable memory leak.
2014-06-27T11:03:24.281+0300|SEVERE: The web application [/myApp] created a ThreadLocal with key of type [io.netty.util.Recycler$1] (value [io.netty.util.Recycler$1@34426f54]) and a value of type [io.netty.util.Recycler.Stack] (value [io.netty.util.Recycler$Stack@759b0e99]) but failed to remove it when the web application was stopped. Threads are going to be renewed over time to try and avoid a probable memory leak.
2014-06-27T11:03:24.282+0300|SEVERE: The web application [/myApp] created a ThreadLocal with key of type [io.netty.util.concurrent.DefaultPromise$1] (value [io.netty.util.concurrent.DefaultPromise$1@16db9b21]) and a value of type [java.lang.Integer] (value [0]) but failed to remove it when the web application was stopped. Threads are going to be renewed over time to try and avoid a probable memory leak.
2014-06-27T11:03:24.282+0300|SEVERE: The web application [/myApp] created a ThreadLocal with key of type [io.netty.util.Recycler$1] (value [io.netty.util.Recycler$1@166c39f2]) and a value of type [io.netty.util.Recycler.Stack] (value [io.netty.util.Recycler$Stack@2ba59f40]) but failed to remove it when the web application was stopped. Threads are going to be renewed over time to try and avoid a probable memory leak.
2014-06-27T11:03:24.282+0300|SEVERE: The web application [/myApp] created a ThreadLocal with key of type [io.netty.buffer.PooledByteBufAllocator$1] (value [io.netty.buffer.PooledByteBufAllocator$1@28ad6479]) and a value of type [io.netty.buffer.PoolThreadCache] (value [io.netty.buffer.PoolThreadCache@67a3923]) but failed to remove it when the web application was stopped. Threads are going to be renewed over time to try and avoid a probable memory leak.
2014-06-27T11:03:24.283+0300|SEVERE: The web application [/myApp] created a ThreadLocal with key of type [io.netty.util.Recycler$1] (value [io.netty.util.Recycler$1@166c39f2]) and a value of type [io.netty.util.Recycler.Stack] (value [io.netty.util.Recycler$Stack@423d2c27]) but failed to remove it when the web application was stopped. Threads are going to be renewed over time to try and avoid a probable memory leak.
2014-06-27T11:03:24.283+0300|SEVERE: The web application [/myApp] created a ThreadLocal with key of type [io.netty.util.Recycler$1] (value [io.netty.util.Recycler$1@535d426e]) and a value of type [io.netty.util.Recycler.Stack] (value [io.netty.util.Recycler$Stack@3e1dd66a]) but failed to remove it when the web application was stopped. Threads are going to be renewed over time to try and avoid a probable memory leak.
2014-06-27T11:03:24.283+0300|SEVERE: The web application [/myApp] created a ThreadLocal with key of type [io.netty.buffer.PooledByteBufAllocator$1] (value [io.netty.buffer.PooledByteBufAllocator$1@28ad6479]) and a value of type [io.netty.buffer.PoolThreadCache] (value [io.netty.buffer.PoolThreadCache@18e7e902]) but failed to remove it when the web application was stopped. Threads are going to be renewed over time to try and avoid a probable memory leak.

I have to mention that the destroy() method is invoked when the app is undeployed. And if I spy with visualVM the thread is still alive after the application was undeployed.

public void destroy(){

    try{
        Statement statement = pgConnection.createStatement();                  
        statement.addBatch("UNLISTEN xxxTest");
        statement.executeBatch();
        statement.close();          
    }catch(SQLException sqle)   {
        sqle.printStackTrace(); 
    }

}

Neil McGuigan · Accepted Answer · 2014-06-27 08:27:49Z

5

Why do you need Hibernate here? Just use JDBC. You probably want to fire an application-wide event anyways (perhaps to send a websocket msg or bust an ehcache), and if you need to do something w hibernate, subscribe to that event and do something with Hibernate.
http://impossibl.github.io/pgjdbc-ng/ is your friend here. No polling required.

See http://blog.databasepatterns.com/2014/04/postgresql-nofify-websocket-spring-mvc.html

edited Jun 27, 2014 at 8:27

answered Jun 26, 2014 at 16:04

Neil McGuigan

48.5k12 gold badges130 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Videanu Adrian Over a year ago

I need hibernate because i wanted to be a common setup with the application and to use the same resources. Thanks for the hints, i`l take a look.

Videanu Adrian Over a year ago

I have implemented with pgjdbc-ng and without hibernate and it seems to work :) thanks Neil.

Videanu Adrian Over a year ago

There seems to be 2 issues here : 1. There is one thread that remains active even if the app is undeployed. 2. It only works if I deploy application twice. I guess it has something to do with the remaining open thread....

Neil McGuigan Over a year ago

Make sure you call UNLISTEN channelName, and deregister listener from pgconnection

Videanu Adrian Over a year ago

I`m sure that UNLISTEN is called. I have updated my question with log entries.

|

Community · Accepted Answer · 2017-05-23 10:30:42Z

4

Hibernate doesn't have direct support for PostgreSL notifications, because this is a specific database feature that's not supported by all DB vendors and it doesn't fit into the ORM framework responsibility.

Nevertheless you could still try hooking it into your current running transaction. You need access to the current database connection do add the listening hook.

This can be done with the Session.doWork() support:

session.doWork(new Work() {
    @Override
    public void execute(Connection connection) throws SQLException {
        //add some statement if it's required
        PGNotification notifications[] = ((PGConnection) connection).getNotifications();           
    }
});

edited May 23, 2017 at 10:30

CommunityBot

11 silver badge

answered Jun 26, 2014 at 7:19

Vlad Mihalcea

156k85 gold badges598 silver badges984 bronze badges

3 Comments

Videanu Adrian Over a year ago

Thanks Vlad, I`ll test that and come back with an accepted answer :)

Craig Ringer Over a year ago

Please see the note in jdbc.postgresql.org/documentation/92/listennotify.html though - you need a round-trip like an empty statement. I'm adding a function now that'll do a Sync protocol message instead.

Vlad Mihalcea Over a year ago

I updated the example, thanks for replying. It looks possible although I've never tried it before.

Collectives™ on Stack Overflow

Hibernate postgresql notify functionality

2 Answers 2

7 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related