Call a Java method using MySQL triggers

Question

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?

suggest use Model, recomended architectural level for processing data — Jacek Cz
– Jacek Cz, Commented Jan 3, 2018 at 1:16

oakad · Accepted Answer · 2018-01-03 01:44:58Z

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 :-)

edited Jan 3, 2018 at 1:44

answered Jan 3, 2018 at 1:34

oakad

7,7851 gold badge25 silver badges35 bronze badges

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

Comments

Kaan Keklik · Accepted Answer · 2022-04-21 13:48:02Z

1

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

answered Apr 21, 2022 at 13:48

Kaan Keklik

111 bronze badge

1 Comment

Yogendra Joshi Over a year ago

Provided link is not working. Giving 404 Not Found.

Collectives™ on Stack Overflow

Call a Java method using MySQL triggers

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related