check if DB has updated

[ganmo]

Hi,
I wonder if it's possible to check whether a MySQL DB has updated. e.g. new information is added to a table?
and if it detect an update. it will perform search or something else.
Main question is if it is possible to listen for changes in the db.

[stephen84s]

Hi,
I wonder if it's possible to check whether a MySQL DB has updated. e.g. new information is added to a table?
and if it detect an update. it will perform search or something else.
Main question is if it is possible to listen for changes in the db.

And how is this connected to Java ?

[masijade]

You would need to occassionally "poll" the DB (and hopefully you have a timestamp field to make it "easy").

[ganmo]

You would need to occassionally "poll" the DB (and hopefully you have a timestamp field to make it "easy").

Dunno what you meant by the word poll.
But do you mean I have to write a method that occassionally send a SQL query for example every 10min to the database. To check if a certain table has increased in row number?

[stephen84s]

But do you mean I have to write a method that occassionally send a SQL query for example every 10min to the database. To check if a certain table has increased in row number?

Alternatively you could use database triggers, but beware of their performance as triggers may affect the performance of your Inserts, Updates etc. Also then all your code would be PL/SQL and not in Java.

[masijade]

A trigger will not be able to notify the DB. The "easiest" way is to set an autoupdating timestamp field on the concerned table, then, every so often, retrieve all records with a timestamp newer than the last time you checked.

To repeat, the DB cannot (at least in the frame of JDBC and "normal" Java) notify the program, so the program needs to poll (i.e. periodically query) the DB.

[ganmo]

Thanks, I think I have overall idea now. I have been searching for ages for a trigger solution. And never thought just having it auto query from java instead.

[stephen84s]

A trigger will not be able to notify the DB. The "easiest" way is to set an autoupdating timestamp field on the concerned table, then, every so often, retrieve all records with a timestamp newer than the last time you checked.

To repeat, the DB cannot (at least in the frame of JDBC and "normal" Java) notify the program, so the program needs to poll (i.e. periodically query) the DB.

Yep acknowledged doc, thats why I mentioned

Also then all your code would be PL/SQL and not in Java

So whatever he wished to mean by notifying, if it was **just some other db operation** he can accomplish it using PL/SQL in his triggers.

Of course yours is best solution applicable to almost all situations.

[masijade]

I just say it, because I am assuming (you know what they say about that word, although I feel it is correct this time) that the OP is attempting to find someway to get his GUI (whatever form that may take) to update whenever something happens to the backend DB.

And, I can only hope, that it is not one client making a change and another client hoping to catch it. If the server is written properly (using an MVC architecture and registering and using listeners or observers or whatever) then the clients should not need to poll anything, as the server should be contacting every concerned client everytime one of them changes something.