15

Greetings all I want to do something like a trigger or a listener (I don't know what) that will listen on a specific database table, and with each new record inserted on this table, do some java code, I mean that it detects that a new record was inserted and get it's data if it's possible I need some guide about how this process can be accomplished ?

I am using Spring-Hibernate-PostgreSQL

Mahmoud Saleh
  • 33,303
  • 119
  • 337
  • 498
  • I think after reading all the comments and such that I am going to implement my own solution. I am going to use JMS anyway so when a client updates the database through JMS send, I will send out a JMS message to all clients that the data has changed.. seems like the only guaranteed way to do this, as triggers seem to be a nightmare and Polling is a horrible solution. – JPM Nov 15 '15 at 19:12

3 Answers3

12

This is what LISTEN/NOTIFY was created for.

The only drawback is that you will need to have some kind of background thread that polls the database on a regular basis to see if any notifications are available.

You can also use the code from the Postgres Wiki to have a starting point

  • Your not polling the database... your polling the client: http://jdbc.postgresql.org/documentation/publicapi/org/postgresql/PGConnection.html#getNotifications() – Adam Gent Nov 05 '12 at 23:24
  • @AdamGent: of course you are polling the server. The client (PGConnection) will ask the server "are there any messages?". –  Nov 06 '12 at 06:51
  • Maybe I'm reading wireshark wrong but I swear it looked as if the pg driver was receiving the notifications with out asking for them. I read that Postgres had added some asynchronous support and assumed that this might be some of that. – Adam Gent Nov 06 '12 at 12:08
  • 1
    @AdamGent: it is indeed transmitted "automatically", but only as a result of a request to the server. So as long as the client doesn't send a request, no notifications will be received. The request can be as simple as `select 42` though. See the examle in the JDBC manual: http://jdbc.postgresql.org/documentation/head/listennotify.html –  Nov 06 '12 at 12:23
  • *PQnotifies does not actually read data from the server; it just returns messages previously absorbed by another libpq function. In prior releases of libpq, the only way to ensure timely receipt of NOTIFY messages was to constantly submit commands, even empty ones, and then check PQnotifies after each PQexec. While this still works, it is deprecated as a waste of processing power.* http://www.postgresql.org/docs/9.1/static/libpq-notify.html. Update sorry didnt see last message – Adam Gent Nov 06 '12 at 12:34
  • @AdamGent: Sending requests to the server for polling seems to be the only reasonable means of receiving such notification. ojdbc, for instance, uses blocking `SELECT .. FOR UPDATE SKIP LOCKED` statements to poll Oracle AQ queue tables, internally... – Lukas Eder Nov 12 '12 at 12:32
  • @LukasEder While trying to write a pgnotify wrapper I was seeing weird behavior for the Postgresql client with Wireshark where I swear it was sending updates with out me issuing another statement. But I'm sure a_horse_with_no_name is right because eventually it stopped doing that. – Adam Gent Nov 12 '12 at 12:41
  • @AdamGent: Possibly, some TCP channel was left open and running in the background... – Lukas Eder Nov 12 '12 at 12:48
  • what if the DB is oracle...is there a way to do that..? – Pawan Kumar Jul 24 '15 at 11:56
  • @PawanKumar: http://docs.oracle.com/cd/E11882_01/java.112/e16548/dbchgnf.htm#JJDBC28815 –  Jul 24 '15 at 12:32
  • Polling is a horrible solution, wasted CPU cycles, you would think by now someone would have a better solution to listening to database changes. – JPM Nov 15 '15 at 19:08
  • @JPM: Apparently no one came up with something better. The two DBMS I know that support notifications like that, Oracle and Postgres, both rely on polling. Do you have have a better implementation of such a notification system? If you do, why don't you contribute it to the Postgres project? –  Nov 15 '15 at 20:32
  • I wish... I need something like that but not for Postgres but for MySQL. I'll probably make something up and use JMS so that I can separate the UI from the services. But also make any updates on the system then notify any subscribers. Easier that way. – JPM Nov 19 '15 at 21:34
  • @JPM: why don't you just upgrade to Postgres? –  Nov 19 '15 at 21:38
1

I assume you mean that the DB content is added through your hibernate code.

If so, consult this previous answer of mine for how to set up Hibernate Event Listeners with Spring.

Otherwise, a-horse-with-no-name's answer should be best.

Community
  • 1
  • 1
Sean Patrick Floyd
  • 292,901
  • 67
  • 465
  • 588
1

You could add an Interceptor to your Hibernate configuration to detect save events.

Sean Patrick Floyd
  • 292,901
  • 67
  • 465
  • 588
Simon Gibbs
  • 4,737
  • 6
  • 50
  • 80