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.
