All geek questions in one place

Hibernate postgresql notifies functionality

I am writing an application using hibernate + JPA as ORM and postgresql 9.3 as a database, and I need to respond with some Java code to some database events.
To be more precise, I want to create a trigger that uses pg_notify () when a new table is inserted into the table.
I read about this, but all the tutorials were related to jdbc direct connection, not sleeping. I (I think) cannot use hibernation events, because the rows are not inserted through hibernation, but by a third-party application.

Is there a way that I can get notifications sent via pg_notify via sleep mode?

- update
Right now I have a 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 should mention that I am using Glassfish 4.0 as an AS. The connection pool is created on a glass fish and is accessible through the application through jndi. Also, the EntityManager is introduced by the container using 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 fixed the class 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 still it seems that I am not receiving notifications.

update ---
After I implemented the solution suggested by Neil, I have this error in Glassfish logs when I undeploy the application:

  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 should mention that the destroy () method is called when the application is not deployed. And if I spy with visualVM, the thread is still alive after the application has not been deployed.

 public void destroy(){ try{ Statement statement = pgConnection.createStatement(); statement.addBatch("UNLISTEN xxxTest"); statement.executeBatch(); statement.close(); }catch(SQLException sqle) { sqle.printStackTrace(); } }
+6
source share
2 answers

  • Why do you need Hibernate here? Just use JDBC. You probably want to fire up the application event anyway (maybe send a web feed or download ehcache), and if you need to do something in sleep mode, subscribe to this event and do something with Hibernate.

  • http://impossibl.imtqy.com/pgjdbc-ng/ is your friend here. No survey required.

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

+3
source

Hibernate does not have direct support for PostgreSL notifications, since it is a specific database function that is not supported by all database providers and does not fit into the responsibility for the ORM structure.

However, you can still try to connect it to the current current transaction. You need access to the current database connection do add a receipt for listening .

This can be done using Session.doWork () :

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

Source: https://habr.com/ru/post/971366/

More articles:


All Articles