1

I need to create a Java agent that can be aware and execute its instruccions as soon as any update for particular tables in a Mysql or Psql Database occurr. Everything needs to be done automaticaly.

I was wondering given Im a novice in Java you guys could give me any advice..

My options are:

1) Having a trigger that after a commit could awake my java application. (using Pg_notify and others)

2) or Having the java application subscribed to a particular ID in a database (not sure if this can be done given asynchronous updates are not possible and I might need to have my agent asking xx second to the dabatase for changes)

Thanks!

Improve this question
4
  • 1
    2 database with same structure or not? Using JDBC or? Commented May 4, 2012 at 2:37
  • Nope structures might be different..but that should not be an issue. I think I could deal with that even if I need an app per DB. I should use JADE but if I have to go with JDBC that is fine. Commented May 4, 2012 at 2:45
  • What I want to suggest, if you using JDBC than you must make your own library to make it more easy for you. You can look here for snapshot. Or maybe you can learn about Hibernate + Spring very useful I think for your problem. Commented May 4, 2012 at 2:56
  • Thank you! Ready to start reading and learning again then. Commented May 4, 2012 at 3:14

1 Answer 1

Reset to default
1

Yes, a trigger that uses NOTIFY is a good way to do it in PostgreSQL. The important problem when using the JDBC driver is that there is no way to receive notifications asynchronously, you have to poll. This is usually fine as the NOTIFY/LISTEN mechanism is very light-weight: if you want to poll 10 (100?) times a second, then you can do so without causing performance problems. See http://jdbc.postgresql.org/documentation/83/listennotify.html for more.

MySQL is a little less helpful; you'll need to have triggers INSERT rows into a monitoring table and repeatedly poll that table with SELECT * (and then DELETE). This will work, but you are more likely to end up in a latency/performance trade-off.

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

3 Comments

I was hoping I could get around pushing rather than pulling the data changes I need from the DB. That is my main aim..but after trying I thought the trigger idea was the best option. Thank you!
@user1373972: PostgreSQL's "notify/listen" is very lightweight "pull" mechanism. Don't be affraid using it.
A generalized trigger for notifying regarding table changes will be included in 9.2; if you don't mind compiling C code from source, it works fine with 9.0 and 9.1. (We have been using it in production for almost a year.) postgresql.org/docs/devel/static/tcn.html git.postgresql.org/gitweb/?p=postgresql.git;a=tree;f=contrib/… Full disclosure: I wrote it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.