Java: Do something on event in SQL Database?

Question

Hello

I'm building an application with distributed parts. Meaning, while one part (writer) maybe inserting, updating information to a database, the other part (reader) is reading off and acting on that information.

Now, i wish to trigger an action event in the reader and reload information from the DB whenever i insert something from the writer.

Is there a simple way about this?

Would this be a good idea? :

// READER
while(true) {
    connect();

    // reload info from DB
    executeQuery("select * from foo");

    disconnect();
}

EDIT : More info

This is a restaurant Point-of-sale system.

Whenever the cashier punches an order into the db - the application in the kitchen get's the notification. This is the kind of system you would see at McDonald's.

The applications needn't be connected in any way. But each part will connect to a single mySQL server.

And i believe i'd expect immediate notifications.

Answer 1

Well to start with you'd want some kind of wait timer in there or it is literally going to poll every instance of time it can which would be a pretty bad idea unless you want to simulate what it would be like if Google was hosted on one database.

What kind of environment do the apps run in? Are we talking same machine notification, cross-network, over the net?

How frequently do updates occur and how soon does the reader need to know about them?

Answer 2

The idea you are talking about is called "polling". As Graphain pointed out you must add a delay in the loop. The amount of delay should be decided based on factors like how quickly you want your reader to detect any changes in database and how fast the writer is expected to insert/update data.

Next improvement to your solution could be to have an change-indicator within the database. Your algo will look something like:

// READER
while(true) {
    connect();

    // reload info from DB
    change_count=executeQuery("select change_count from change_counters where counter=foo");
    if(change_count> last_change_count){
      last_change_count=change_count;
      reload();
    }

    disconnect();
}

The above change will ensure that you do not reload data unnecessarily. You can further tune the solution to keep a row level change count so that you can reload only the updated rows.

Answer 3

I have done something similar before using jGroups I don't remember the exact details as it was quite a few years ago but I had a listener on the "writer" end which would then use JGroups to send out notification of change which would cause the receivers to respond accordingly.

Answer 4

I don't think it's a good idea to use a database to synchronize processes. The parties using the database should synchronize directly, i.e., the writer should write its orders and then notify the kitchen that there is a new order. Then again the notification could be the order itself (or some ID for the database). The notifications can be sent via a message broker.

It's more or less like in a real restaurant. The kitchen rings a bell when meals are finished and the waiters fetch them. They don't poll unnecessarily.

If you really want to use the database for synchronization, you should look into triggers and stored procedures. I'm fairly sure that most RDBMS allow the creation of stored procedures in Java or C that can do arbitrary things like opening a Socket and communicating with another Computer. While this is possible and not as bad as polling I still don't think of it as a very good idea.

Answer 5

You might consider setting up an embedded JMS server in your application, I would recommend ActiveMQ as it is super easy to embed.

For what you want to do a JMS Topic is a perfect fit. When the cashier punches in an order the order is not written to the database but in a message on the Topic, let's name it newOrders.

On the topic there are 2 subscribers : NewOrderPersister and KitchenNotifier. These will each have an onMessage(Message msg) method which contains the details of the order. One saves it to the database, the other adds it to a screen or yells it through te kitchen with text-to-speech, whatever.

The nice part of this is that the poster does not need to know which and how many subscribers are there waiting for the messages. So if you want a NewOrderCOunter in the backoffice to keep an online count of how much money the owner has made today, or add a "FreanchFiresOrderListener" to have a special display near the deep frying pan, nothing has to change in the rest of the application. They just subscribe to the topic.