Inline.. On Tue, May 9, 2017 at 1:58 AM, Lukas Eder <[email protected]> wrote:
> Hi Samir, > > I'll comment inline > > 2017-05-09 4:57 GMT+02:00 Samir Faci <[email protected]>: > >> So I ran into this issue and was wondering if this was an implementation >> over sight of it's intended. >> >> I have the following snippet of code: >> >> For reference, the record has a serial ID, that has little value though >> for legacy reasons is the primary key of the record. >> > > I'm curious: Why is this a "legacy"? Usually, the serial ID is the primary > key. What would be a use-case not to do that? > There's nothing wrong with serial IDs, but in this case it conveys no real meaning. If the record is uniquely identified by a combination of N fields, then the PKey should be a composite of those N fields. Having an ID that just goes up and doesn't add value is something we're trying to shift away from. I'm sure there's varied opinion on this but this our DBA's preference and our approach moving forward. > ApiSettingParameterRecord record = > dslContext.newRecord(INTEGRATION.API_SETTING_PARAMETER); >> record.setActive(true); >> record.setDataSource(entity.getDataSource()); >> record.setName(entity.getName()); >> record.setType(entity.getType()); >> record.setLength(entity.getLength()); >> record.setUiOrder(entity.getUiOrder()); >> record.setUiLabel(entity.getUiLabel()); >> record.setIsPopulated(entity.getIsPopulated()); >> record.setUrl(entity.getUrl()); >> record.setAutoPopulated(entity.getAutoPopulated()); >> record.setVisible(entity.getVisible()); >> record.setVersion(entity.getVersion()); >> >> dslContext.insertInto(record.getTable()) >> .set(record) >> .onConflict(INTEGRATION.API_SETTING_PARAMETER.DATA_SOURCE, >> INTEGRATION.API_SETTING_PARAMETER.VERSION, >> INTEGRATION.API_SETTING_PARAMETER.ACTIVE, >> INTEGRATION.API_SETTING_PARAMETER.UI_ORDER) >> .doUpdate() >> .set(record) >> .execute(); >> >> >> >> After the code executes, I'd like to be able to do >> >> >> record.refresh(); >> >> >> >> Which in this case doesn't work since the PKey is null. >> > > Yes, that works as designed. The set(Record) clause you've used doesn't > imply that the record will be modified by / tied to the specific INSERT > statement, so the refresh() call wouldn't know what particular database > record it should refresh itself to. > > >> record.refresh() to get the ID of the record that was inserted, >> >> Instead, I'm forced to do a select using the onConflict conditional. >> >> ie. >> >> >> record = >> dslContext.select(record.fields()) >> .from(INTEGRATION.API_SETTING_PARAMETER) >> >> .where(INTEGRATION.API_SETTING_PARAMETER.NAME.eq(entity.getName())) >> >> .and(INTEGRATION.API_SETTING_PARAMETER.DATA_SOURCE.eq(entity.getDataSource())) >> >> .and(INTEGRATION.API_SETTING_PARAMETER.VERSION.eq(entity.getVersion())) >> >> .and(INTEGRATION.API_SETTING_PARAMETER.ACTIVE.eq(Boolean.TRUE)) >> >> .orderBy(INTEGRATION.API_SETTING_PARAMETER.ACTIVE.desc()) >> .fetchOneInto(ApiSettingParameterRecord.class); >> >> > That's one option right now. > >> >> > > >> If this was a normal insert I could simply do a .returning(id); but >> that's not supported with upserts, that's not feasible. >> > > That will be supported in jOOQ 3.10, finally: > https://github.com/jOOQ/jOOQ/issues/2123 > > But you can work around the issue in one of three ways: > > 1. Leverage the fact that the statement building DSL API is mutable, so > you could assign the intermediate steps to a local variable and then call > onConflict()... and returning() on that individually (this may break in > jOOQ 4.0) > 2. Leverage the fact that internally, the statement building DSL API is > implemented by a single class InsertImpl, which implements all the DSL Step > APIs, and thus offers all the required methods. This means you could cast > the type returned by doUpdate().set(...) to InsertReturningStep and call > returning() on that (this may break in jOOQ 4.0 as well) > 3. Use the model API through DSLContext.insertQuery() > I wasn't able to get DSLContext.insertQuery() but I'll play with this a bit more when I have a chance. > > 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. > -- Thank you Samir Faci https://keybase.io/csgeek -- 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.
