Hi Lukas, Using a trigger _could be_ an option for some scenario, but I don't this is a viable approach with a clustered database. Regaring Jooq limitation, I understand your concern, but could be not an issue.
RDBS: mariadb 5.5 Id generation process use hilo algorithm, as described at https://stackoverflow.com/a/24368478 but is not really relevant which algorithm I use to create a new id, usually in testing I use a FakeIdGenerator that create sequential id in memory. I can create a small project using jooq-mcve is this ok os do you prefer having some code snippet just to have an idea? Cheers On Thursday, December 20, 2018 at 10:17:51 AM UTC+1, Lukas Eder wrote: > > Hi Daniele, > > My first thought in such a case is always: Is using a trigger an option? > Because triggers will be the most reliable way to guarantee the correct ID > assignment across database clients. Of course, you can do something with > jOOQ, but that will force you to use jOOQ for every single database > interaction, and it might even limit your possible usage of jOOQ (e.g. no > more plain SQL queries). > > Indeed, returning (in some databases) works only if there is an identity > column. That is very unfortunate, but if it is the case, it means that the > RDBMS or the JDBC driver do not offer any other way to fetch a "generated" > key. But maybe there's a way to work around this, or even fix something in > jOOQ. Could you please provide: > > - Your RDBMS vendor and version > - Some code including DDL and jOOQ code that shows how your ID generation > works > > Thanks, > Lukas > > On Wed, Dec 19, 2018 at 10:45 PM Daniele Antonini <[email protected] > <javascript:>> wrote: > >> Hi, >> >> I have a database which use global unique row ID instead of >> (autoincrement) ID per table. >> I'm trying to encapsulate this aspect so I don't need to manually use an >> ID generator every time. >> I implemented a VisitorListener to "inject" ID on insert query if not >> specified and works fine unless I trying to return a record. >> >> I tried to use returning() but it has a different behavior than expected >> (but in this case my expectation is probably wrong). >> >> Seems that returning() works only for database auto-generated stuff, and >> is hard to alter this bahavior. >> >> My attempt was the following: >> I used VisitorListener to inject id in the query, the implementation >> collect the generated ID and implement also the ExecuteListener interface >> which allow to manipulate the statement before the execution. So I create a >> prepared statement wrapper which is ids aware and return they when >> getGeneratedKeys() is invoked. I did it, but then jooq ask for >> table.getIdentity() and I stopped. >> >> My concerns are: >> - am I right simulating auto-generated key behavior? >> - how can I obtain a record automatically without executing an explicit >> select? Moreover in this case I don't even know the ID to use for the >> select because is injected by the visitor >> >> At this very moment, I discarded this idea, set id at application level >> and manually execute a select to obtain a record. >> >> Any thoughts? >> >> Cheers >> >> >> -- >> 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] <javascript:>. >> For more options, visit https://groups.google.com/d/optout. >> > -- 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]. For more options, visit https://groups.google.com/d/optout.
