Database generic way of achieving Query Result Change Notification

Kaartic Sivaraam — Wed, 29 Jan 2020 09:45:40 +0000

There's a use case in which we would like a Java application to know when a result of a SELECT query changes as a consequence of a DML update in a database. On exploration, this seems to be achievable in the Oracle database using Oracle's "Query Result Change Notification" (at least with some limitations). As changing the datastore to Oracle database is not an option, we are looking for a solution that works with the following databases:

PostgreSQL
MySQL
MS SQL Server

Further exploration reveled that MS SQL Server has "Query Notifications" which is similar to Oracle's "Query Result Change Notification". PostgreSQL and MySQL equivalents could not be found.

Coming to the question, what could be a (possibly efficient) way to achieve this in PostgreSQL and MySQL databases? Is there any Java library that helps us know when a SELECT query result changes as a consequence of a DML update in a database?

PS: I've tried to provide enough information in the question. In case any information is required, kindly let me know.

DEV Community: Kaartic Sivaraam

Database generic way of achieving Query Result Change Notification