INSERT ... RETURNING when doing CRUD with Record objects
I might be missing something obvious in the UpdatableRecord API, but I
didn't find a way to emulate INSERT ... RETURNING. Is there a way to do
that?
As an example, I'd like to mimic this behavior (inserting into a simple
table that only needs `name` specified):
txnContext.insertInto(WIDGETS, WIDGETS.NAME)
.values(name)
.returning()
.fetchOne()
with this:
txnContext.newRecord(WIDGETS).apply {
this.name = name
store() // INSERT
refresh() // SELECT -- ideally not needed
}
Without that `refresh()`, the new `WidgetsRecord` doesn't have, for
instance, its db-populated `createdAt`.
Thanks,
Marshall
--
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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: copy records with primary key from one db to another
thank you so much! Am Freitag, 29. März 2019 09:09:57 UTC+1 schrieb Lukas Eder: > > > > On Fri, Mar 29, 2019 at 8:36 AM Christian Master > wrote: > >> Perfect, thank you. (to copy it from one db to another, create2 was >> missing) >> > > Yes of course. > > >> For one row i do it this way: >> MyRecord my1 = create2.selectFrom(KTO).fetchAny(); >> my1.set(KTO.KTOID,null); >> create1.insertInto(KTO).set(my1).execute(); >> >> This works for some fields, but some are null. The ID gets generated, >> most of the data stored, but I dont understand why it works for some >> fields, and for some not. >> Is this wrong way to insert one Record from one DB to another? Or one >> Table to another? >> > > In UpdatableRecord, the primary key value is "special". If you set it to > null on an existing record, the record is assumed to be a "copy" of the > previous one, because what else could a null primary key mean other than > the record needs to be inserted rather than updated. Because of this > assumption, setting a primary key to null also sets all the other column > changed flags to true, to enforce them being inserted, despite the fact > that you didn't actually change them explicitly. > > With other values, setting them to null means you actually want the value > to be set to null. There's no reason for any further implicit behaviour. If > you want to make this more explicit, you could also write: > > MyRecord my1 = create2.selectFrom(KTO).fetchAny(); > my1.set(KTO.KTOID,null); > my1.changed(true); > my1.changed(KTO.KTOID, false); > create1.insertInto(KTO).set(my1).execute(); > > That's what happens behind the scenes, implicitly. > -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: copy records with primary key from one db to another
On Fri, Mar 29, 2019 at 8:36 AM Christian Master wrote: > Perfect, thank you. (to copy it from one db to another, create2 was > missing) > Yes of course. > For one row i do it this way: > MyRecord my1 = create2.selectFrom(KTO).fetchAny(); > my1.set(KTO.KTOID,null); > create1.insertInto(KTO).set(my1).execute(); > > This works for some fields, but some are null. The ID gets generated, most > of the data stored, but I dont understand why it works for some fields, and > for some not. > Is this wrong way to insert one Record from one DB to another? Or one > Table to another? > In UpdatableRecord, the primary key value is "special". If you set it to null on an existing record, the record is assumed to be a "copy" of the previous one, because what else could a null primary key mean other than the record needs to be inserted rather than updated. Because of this assumption, setting a primary key to null also sets all the other column changed flags to true, to enforce them being inserted, despite the fact that you didn't actually change them explicitly. With other values, setting them to null means you actually want the value to be set to null. There's no reason for any further implicit behaviour. If you want to make this more explicit, you could also write: MyRecord my1 = create2.selectFrom(KTO).fetchAny(); my1.set(KTO.KTOID,null); my1.changed(true); my1.changed(KTO.KTOID, false); create1.insertInto(KTO).set(my1).execute(); That's what happens behind the scenes, implicitly. -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: copy records with primary key from one db to another
Perfect, thank you. (to copy it from one db to another, create2 was missing) List> fields = new ArrayList<>(Arrays.asList(KTO.fields())); fields.removeAll(KTO.getPrimaryKey().getFields()); create1.insertInto(Tables.KTO).columns(fields).select( create2.select(fields).from(KTO) ).execute(); For one row i do it this way: MyRecord my1 = create2.selectFrom(KTO).fetchAny(); my1.set(KTO.KTOID,null); create1.insertInto(KTO).set(my1).execute(); This works for some fields, but some are null. The ID gets generated, most of the data stored, but I dont understand why it works for some fields, and for some not. Is this wrong way to insert one Record from one DB to another? Or one Table to another? Thx C Am Donnerstag, 28. März 2019 11:10:22 UTC+1 schrieb Lukas Eder: > > You can easily extract a set of columns without the primary key as follows: > > List> fields = new ArrayList<>(Arrays.asList(KTO.fields())); > fields.removeAll(KTO.getPrimaryKey().getFields()); > > create1.insertInto(KTO).columns(fields).select( > select(fields).from(KTO) > ).execute(); > > Hope this helps, > Lukas > > On Thu, Mar 28, 2019 at 9:39 AM Christian Master > wrote: > >> I try to copy the records from one database to another. >> >> create1.insertInto(Tables.KTO).select( >> create2.select().from(Tables.KTO) >> ).execute(); >> >> Since I have a primary key with auto_increment this wont work. >> >> In pure SQL it is not possible to exclude the primary key in create2. >> Is it possible with jooq? >> I don't want to write all the fields into a "insertInto" method. >> >> If it is not possible, is there a way to copy the values from SomeRecord >> a to SomeRecord b without doing it field by field? >> >> Thx >> Chris >> >> >> -- >> 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...@googlegroups.com . >> 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 jooq-user+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
