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.
https://debezium.io/documentation/reference/stable/connectors/postgresql.html On Monday, 5 September 2022 at 19:17:40 UTC+2 Jan Vybíral wrote: > 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 [email protected] > napsal: > >> 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: >> >> https://www.jooq.org/doc/latest/manual/sql-building/model-api/model-api-traversal/ >> >> 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? >> >> https://blog.jooq.org/implementing-client-side-row-level-security-with-jooq/ >> >> Cheers, >> Lukas >> >> On Mon, Sep 5, 2022 at 5:40 PM Jan Vybíral <[email protected]> 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. >>> >>> Dne pondělí 5. září 2022 v 17:15:30 UTC+2 uživatel [email protected] >>> napsal: >>> >>>> 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) >>>> - Client side computed columns, which emulate computed columns in jOOQ: >>>> https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-forced-types/codegen-database-forced-types-computed/ >>>> >>>> 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 >>>> >>>> On Mon, Sep 5, 2022 at 3:03 PM Jan Vybíral <[email protected]> wrote: >>>> >>>>> 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 >>>>> <https://www.jooq.org/doc/latest/manual/sql-execution/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 >>>>> <https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/VisitListener.html> >>>>> 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? >>>>> >>>>> -- >>>>> 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 [email protected]. >>>>> To view this discussion on the web visit >>>>> https://groups.google.com/d/msgid/jooq-user/d0ef1e88-e803-413b-b3e5-5ceab0d8be9bn%40googlegroups.com >>>>> >>>>> <https://groups.google.com/d/msgid/jooq-user/d0ef1e88-e803-413b-b3e5-5ceab0d8be9bn%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>> . >>>>> >>>> -- >>> 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 [email protected]. >>> >> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/jooq-user/cf7ad901-6edf-4dec-a48a-af6cbbfa1138n%40googlegroups.com >>> >>> <https://groups.google.com/d/msgid/jooq-user/cf7ad901-6edf-4dec-a48a-af6cbbfa1138n%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >> -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/4d9c8bef-ea6f-4ce1-9e7a-74bbf536a700n%40googlegroups.com.
