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.
Top comments (1)
Just for the record, another option available for SQL server seems to be delta live tables: learn.microsoft.com/en-us/azure/da...