4

Here is my requirement:

My java service is continuous running process. When the java service start, it loads the configuration from one of the table in MySql database.

When there are any changes (can be insert/update/delete, outside the java service also possible), java service should reloaded the configuration immediately.

I saw a post in stackoverflow which is for Oracle database, but I am looking for Mysql database.

I can able to achieve this using separate thread, which polling the database table with some interval. But, polling for change is a not a good way to do. Instead, I am looking for any Watcher/Listener which will notify when there is any change in the MySql table.

Can anybody help me to achieve this?

Note: If this question is already answered somewhere, please post the link in the comment, I will remove this question.

Amaresh Narayanan
  • 4,159
  • 3
  • 20
  • 22
  • it appears like this might help you. after all, it is an sql statement, that will return a result set: https://serverfault.com/questions/112468/mysql-database-listener – Instinct Jul 25 '18 at 07:22
  • @instinct, Thanks for the response, but again I have to poll the database with some interval right. I am trying to avoid it. – Amaresh Narayanan Jul 25 '18 at 07:28
  • Have you considered using a configuration file or a properties file (instead of a database table)? One can detect the file change using a [java.nio.file.WatchService](https://docs.oracle.com/javase/7/docs/api/java/nio/file/WatchService.html). – prasad_ Jul 25 '18 at 07:48
  • There is nothing that works out of the box, you might be interseted in the answers here: https://stackoverflow.com/questions/23031723/mysql-listen-notify-equivalent – StephaneM Jul 25 '18 at 07:59
  • @prasad, for some other reason, we have to use mysql as configuration source. I had thought about the file as configuration. Anyway, thanks for the comment. – Amaresh Narayanan Jul 25 '18 at 08:20
  • Have a look at [mysql-notification](https://github.com/Cyclonecode/mysql-notification). – Mick Mnemonic Jul 25 '18 at 08:28
  • How about a `RowSetListener`? It can detect changes to a `RowSet` (e.g., `JdbcRowset`, which is a connected rowset). The listener is added to the rowset; and this will detect changes to the rowset within the app (or service) immediately. But, changes done externally still need some kind of rowset refresh mechanism within the app (e.g., do table query periodically using a timer). – prasad_ Jul 25 '18 at 09:34

1 Answers1

1

You want to do a Change Data Capture system. Perhaps you could use one based on Kafka.

You can use kafka-connect with Debezium on it. This connector first snapshot your table, then read the mysql binlog to be able to have a consistent kafka topic with insert/modify/delete on the table.

If you don't want use it, perhaps you can fork the code to use the same thing as they do, just modifying the output to be your application listener.

wargre
  • 4,575
  • 1
  • 19
  • 35
  • No need really to fork; if needed you also can use Debezium as a [library embedded](http://debezium.io/docs/embedded/) into your Java applications, allowing you to manually handle change messages as needed. (Disclaimer: I'm the Debezium project lead) – Gunnar Jul 25 '18 at 13:30
  • Hey, I didn't know this nice feature to embed debezium. Really nice. – wargre Jul 25 '18 at 13:34