DEV Community

Kaartic Sivaraam
Kaartic Sivaraam

Posted on

3

Database generic way of achieving Query Result Change Notification

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:

  1. PostgreSQL
  2. MySQL
  3. 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.

Postgres on Neon - Get the Free Plan

No credit card required. The database you love, on a serverless platform designed to help you build faster.

Get Postgres on Neon

Top comments (1)

Collapse
 
sivaraam profile image
Kaartic Sivaraam

Just for the record, another option available for SQL server seems to be delta live tables: learn.microsoft.com/en-us/azure/da...

Qodo Takeover

Introducing Qodo Gen 1.0: Transform Your Workflow with Agentic AI

Rather than just generating snippets, our agents understand your entire project context, can make decisions, use tools, and carry out tasks autonomously.

Read full post

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay