2015-01-13 0:48 GMT+01:00 Robert DiFalco <[email protected]>: > I have a couple of comments with regards to inserting records. > > With JPA I have a sequence generator. Hibernate uses these like pools so > the generator skips a range of values. > > Here are my questions: > > 1. Under JOOQ is their a performance advantage in replicating this > behavior? >
Compared to using PostgreSQL's native sequences, or compared to not keeping pools in Java memory? > 2. If not, should I just change the field default to grab a sequence > on insert when the ID is null? > This is what many PostgreSQL users do - e.g. the serial type is a convenient choice as it auto-generates all the DDL for the sequence and the default clause. > If I opt for #2 how do I insert a record and get the generated ID back? I > can see how to do it with a create.insert() query but I was hoping to > simply insert the JOOQ generated record I already have and that approach > does not allow a #returning chain. > UpdatableRecord.store() (or insert()) always fetches generated keys. This is documented here: http://www.jooq.org/javadoc/latest/org/jooq/UpdatableRecord.html#store-- - *IDENTITY columns* If there is an IDENTITY column defined on the record's underlying table (see Table.getIdentity() <http://www.jooq.org/javadoc/3.5.x/org/jooq/Table.html#getIdentity-->), then the auto-generated IDENTITY value is refreshed automatically on INSERT's. Refreshing is done using Statement.getGeneratedKeys() <http://java.sun.com/javase/6/docs/api/java/sql/Statement.html?is-external=true#getGeneratedKeys-->, where this is supported by the JDBC driver. See also InsertQuery.getReturnedRecord() <http://www.jooq.org/javadoc/3.5.x/org/jooq/InsertQuery.html#getReturnedRecord--> for more details Then I hoped I could do something like > create.insert(TABLE).using(record).returning, etc. But if I do an "insert" > call then I cannot use my record as an object. So there is only the long > way around (adding calls to #values for each field in the record). But > again, I could EASILY be mistaken. > > If it is recommended that I do #1, does JOOQ have any basic classes for > this? Or it is assume I will implement an ID server myself? > jOOQ being a very DB-centric API, so far, we were assuming that most people would use mainly DB-driven sequence generation, not application driven generation. The main advantage of this is the fact that the single source of truth for sequence values is encapsulated at the place where ID values are really consumed. This will allow for several applications (not necessarily written in Java) to reuse the same sequence numbers and prevent duplicate values entirely. Of course, there are use-cases for application-driven sequence generation, but getting that right will require much more discipline. You have a couple of options to implement that with jOOQ, namely using RecordListener (which works only with UpdatableRecord.store(), insert(), update(), refresh(), etc.), or VisitListener (which works with all statements): - http://www.jooq.org/doc/latest/manual/sql-execution/crud-with-updatablerecords/crud-record-listener/ - http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-sql-transformation/ Unless you have a very complex ID generation algorithm that produces UUIDs across multiple systems or something like that, I would personally strongly recommend moving ID generation into the database. 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
