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?
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.