Database INSERT/UPDATE change detection

247 views
Skip to first unread message

Jan Vybíral

unread,
Sep 5, 2022, 9:03:17 AM9/5/22
to jOOQ User Group
Hello,

I have a CRUD application which writes data from different sources to PostgreSQL (REST API, kafka...) using jOOQ, and I'm trying to find a way how to implement logic like: "whenever attribute X of table Y is updated, do Z" (without putting lots of if's everywhere attribute X might be changed).

If I was using JPA, I'd use something like @PreUpdate entity listener and I'm wondering if there's a way how to do something similar in jOOQ. The app is not doing any complex updates with subselects, it's all simple "INSERT INTO xxx VALUES yyy" or "UPDATE x SET y WHERE z" queries.

I've found Execute Listeners which seems like the thing I'd want - I can get each query before it's executed, but it doesn't seem like I can do much with the query itself - ideally I'd want to get affected table, updated columns and maybe insert condition. Rendering the query into string and then parsing that seems like a bad solution.

I've also found VisitListener which seems useful (although somewhat difficult to implement) - I can parse the query programmatically. But it seems like it's called whenever the sql is rendered, so for example if I turn on logging, the listener is called multuple times for each query.

Is there a way how to do something like this in jOOQ? 

Lukas Eder

unread,
Sep 5, 2022, 11:15:30 AM9/5/22
to jOOQ User Group
Hi Jan,

You can pull this off with both ExecuteListener or VisitListener, although it's a ton of work, and very brittle. You'll have to handle so many edge cases...

A much better solution is to use any of:

- Computed columns (directly in PostgreSQL)
- Triggers (directly in PostgreSQL)

All of the above assume that your "do Z" implies some additional SQL action to be included in the same statement. If your "do Z" means you have to implement some other type of side effect in Java, then the client side computed columns could still be used, as the org.jooq.Generator implementation could implement such a side-effect. Perhaps, if you can share a specific example for "do Z", I might be able to help you a bit further.

I hope this helps,
Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/d0ef1e88-e803-413b-b3e5-5ceab0d8be9bn%40googlegroups.com.

Jan Vybíral

unread,
Sep 5, 2022, 11:40:08 AM9/5/22
to jOOQ User Group
Hi Lukas,

thanks for reply, unfortunately the actions often invole not only recomputing some columns in db, but also sending data to other services using REST/kafka messages, so triggers or computed columns won't cut it.

The  ExecuteListener looks promising, but I'm not sure how to get info about what table and what values were updated in query provided by the listener. My idea is that I'll register ExecuteListener, check if it's INSERT or UPDATE query and then I'd somehow traverse the query using VisitListener to get info about what is being updated, but can't find a way to do it. 

Dne pondělí 5. září 2022 v 17:15:30 UTC+2 uživatel lukas...@gmail.com napsal:

Lukas Eder

unread,
Sep 5, 2022, 11:52:34 AM9/5/22
to jOOQ User Group
I see, so indeed, computed columns won't be the right solution. The execute listener could use the new experimental model API traversal functionality, in the future:

Unfortunately, as it is still experimental, INSERT/UPDATE/DELETE statements aren't being traversed yet, so that won't help you right now. This leaves us with VisitListener (triggered from an ExecuteListener). I can help you with specific questions, perhaps a bit more specific than "can't find a way to do it," as explaining the approach from scratch would take too much time. But perhaps this article helps you get an idea?

Cheers,
Lukas

Rob Sargent

unread,
Sep 5, 2022, 12:10:21 PM9/5/22
to jooq...@googlegroups.com
On Sep 5, 2022, at 9:40 AM, Jan Vybíral <jan.vy...@gmail.com> wrote:

Hi Lukas,

thanks for reply, unfortunately the actions often invole not only recomputing some columns in db, but also sending data to other services using REST/kafka messages, so triggers or computed columns won't cut it.

The  ExecuteListener looks promising, but I'm not sure how to get info about what table and what values were updated in query provided by the listener. My idea is that I'll register ExecuteListener, check if it's INSERT or UPDATE query and then I'd somehow traverse the query using VisitListener to get info about what is being updated, but can't find a way to do it. ?


I’m always interested to see how other hand db interaction...

You started out saying this was a “REST” (which I take to be same as “RESTful”).  But it also sounds as though the clients are sending SQL (as opposed to using a matching http method (insert goes to post (iirc)).  If that is the case it pretty much behooves the client to also say something about the context of the statement - though I think this might only be a problem for updates and selects as the two which are not pre-force whole records while insert, delete are whole-record interactions.


Jan Vybíral

unread,
Sep 5, 2022, 1:17:40 PM9/5/22
to jOOQ User Group
What I'm trying to figure out is how exactly to use the VisitListener. I know how to globally register ExecuteListener, for each executed query I can check if it's UPDATE or INSERT, but then I don't know how to actually use the VisitListener to apply it to the Query i get from ExecuteContext. So far I've just created VisitListener bean in spring context and it automagically started to work for all queries, but I'd prefer it to only be executed for queries selected in ExecuteListener.

In the blog I've found only:

DSLContext restricted = DSL.using(
    configuration.derive(
        DefaultVisitListenerProvider.providers(
            new AccountIDFilter(1, 2)
        )
    )
);
restricted.fetch(ACCOUNTS)

But I don't really wan't to execute the query manually in the ExecuteListener, I just want to somehow inspect it using the VisitListener.

Dne pondělí 5. září 2022 v 17:52:34 UTC+2 uživatel lukas...@gmail.com napsal:

Alexander Openkowski

unread,
Sep 6, 2022, 1:10:40 AM9/6/22
to jOOQ User Group
As you're using Postgres and Kafka anyway, did you think about using Debezium for your use case? It reads like it does _exactly_ what you want.

Lukas Eder

unread,
Sep 6, 2022, 2:39:37 AM9/6/22
to jOOQ User Group
In the ExecuteListener, you just have to re-render the query with a given VisitListener

Jan Vybíral

unread,
Sep 6, 2022, 4:27:30 AM9/6/22
to jOOQ User Group
That's interesting but it's not usable - I can't change architecture or modify kafka cluster (corporate...), I also need the changes to other colums be done in same transaction (also exposing internal db schema over kafka doesn't sound like a good idea)

Dne úterý 6. září 2022 v 7:10:40 UTC+2 uživatel Alexander Openkowski napsal:
Reply all
Reply to author
Forward
0 new messages