'Call a Java method using MySQL triggers

I'm writing an application that retrieves data from a table in a MySQL database and displays it in a JTable. Whenever an insert/update/delete operation is made I want the changes to be displayed. As of now I'm using a thread that repeatdly checks the attribute "UPDATE_TIME" from the table "tables" in information_schema where "TABLE_SCHEMA" is the name of my schema and "TABLE_NAME" is the name of the table I need to check on, however I feel like this approach is very resource expensive since it launches a query every 200ms. I know this has probably been asked many times but all I could find was either a solution using Oracle or some outdated or incomplete stuff, so is there any way to call a Java method using a trigger in MySQL? I was thinking about something like this: 

void checkTableUpdate() {
    synchronized (tableUpdate) {
        try {
            tableUpdate.wait();
            /**
             * code executed to display the changes
             */
        } catch (InterruptedException e) {
            /**
             * 
             */
        }
    }
}

void notifyTableUpdate() {
    synchronized (tableUpdate) {
        tableUpdate.notify();
    }
}

The trigger should have to call the notifyTableUpdate() method. Is there a way to accomplish this or a solution that doesn't involve polling?

javamysqlmethodsdatabase-trigger


Solution 1:[1]

MySQL protocol (in its current form - not sure about X variety) has no provisions for server to initiate data transfer to client. It's purely client makes request - server replies type, so the only option for client to detect changes is by polling.

You can create a dedicated "events" table, which will get updated by triggers and poll it every now and then from a dedicated thread or whatever.

Curiously enough, Postgresql does have a client notification mechanism implemented as a LISTEN/NOTIFY command pair. So if you can switch to Postgres your problem will be somewhat easier to solve.

https://bugs.mysql.com/bug.php?id=19597 :-)

Solution 2:[2]

I had a similar need in a project where i used java. As a result of my research, I solved my problem using postgre, hope this document helps you https://jdbc.postgresql.org/documentation/head/listennotify.html

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Kaan Keklik

Related Questions