Jooq、Postgres 以及从 Multible 表中更新和返回数据失败

Question

我在更新其中一个表中的字段时，很难制定一个 jooq 查询以从这两个表中返回数据。(Postgres 9.6， jooq 3.11(

表 DEVICE 和 CUSTOMER 连接在外键约束上 装置。客户 ID = CUSTOMER.ID。

我想返回一个符合条件的 device.id 和设备客户的 customer.secret，并将 device.state 更新为 IN_PROGRESS。资格由各种条款评估。

我从

final Record task = db
.update(DEVICE)
.set(DEVICE.STATE, StateEnum.IN_PROGRESS)
.from(CUSTOMER)
.where(DEVICE.CUSTOMERID.eq(CUSTOMER.ID))
.and(DEVICE.STATE.eq(StateEnum.NEW))
.and(CUSTOMER.SECRETCONFIRMED.eq(true))
.returning(DEVICE.ID, CUSTOMER.SECRET)
.fetchOne();    

这导致了ERROR: Field ("public"."customer"."secret") is not contained in Row

我基于这个带有连接的 Postgres RETURN 子句对另一个查询进行了建模，但最终得到了同样的错误。

final Record task = db
.update(DEVICE)
.set(DEVICE.STATE, StateEnum.IN_PROGRESS)
.from(CUSTOMER, db
.select()
.from(CUSTOMER
.join(DEVICE).on(CUSTOMER.ID.eq(DEVICE.CUSTOMERID)))
.forUpdate()
)
.where(DEVICE.STATE.eq(StateEnum.NEW))
.and(CUSTOMER.SECRETCONFIRMED.eq(true))
.returning(DEVICE.ID, CUSTOMER.SECRET)
.fetchOne();
ERROR:  Field ("public"."customer"."secret") is not contained in Row  

我尝试了几种排列，最终出现了一些错误变化

• 多次指定表名"<customer/device>"
• 表"<customer/device>"缺少 FROM 子句条目

我有信心，可以进行这样的查询，但是我已经没有想法了。有什么建议吗？

我尝试过的变体的代表性列表

final Record task = db
.update(DEVICE)
.set(DEVICE.STATE, StateEnum.IN_PROGRESS)
.from(
DEVICE.join(CUSTOMER).on(DEVICE.CUSTOMERID.eq(CUSTOMER.ID)))
.where(DEVICE.STATE.eq(StateEnum.NEW))
.and(CUSTOMER.SECRETCONFIRMED.eq(true))
.returning(DEVICE.ID, CUSTOMER.SECRET)
.fetchOne();
ERROR: table name "device" specified more than once
final Record task = dbContext
.update(DEVICE.join(CUSTOMER).on(DEVICE.CUSTOMERID.eq(CUSTOMER.ID)))
.set(DEVICE.STATE, StateEnum.IN_PROGRESS)
.where(DEVICE.STATE.eq(StateEnum.NEW))
.and(CUSTOMER.SECRETCONFIRMED.eq(true))
.returning(DEVICE.ID, CUSTOMER.SECRET)
.fetchOne();
ERROR: syntax error at or near "join"
final Record task = db
.update(DEVICE)
.set(DEVICE.STATE, StateEnum.IN_PROGRESS)
.from(db.select().from(CUSTOMER,
DEVICE.join(CUSTOMER).on(DEVICE.CUSTOMERID.eq(CUSTOMER.ID))).forUpdate()
)
.where(DEVICE.STATE.eq(StateEnum.NEW))
.and(CUSTOMER.SECRETCONFIRMED.eq(true))
.returning(DEVICE.ID, CUSTOMER.SECRET)
.fetchOne();
ERROR: table name "customer" specified more than once
final Record task = db
.update(DEVICE)
.set(DEVICE.STATE, StateEnum.IN_PROGRESS)
.from(db.select().from(
DEVICE.join(CUSTOMER).on(DEVICE.CUSTOMERID.eq(CUSTOMER.ID))).forUpdate()
)
.where(DEVICE.STATE.eq(StateEnum.NEW))
.and(CUSTOMER.SECRETCONFIRMED.eq(true))
.returning(DEVICE.ID, CUSTOMER.SECRET)
.fetchOne();
ERROR: missing FROM-clause entry for table "customer"

final Record task = db
.update(DEVICE)
.set(DEVICE.STATE, StateEnum.IN_PROGRESS)          
.from(CUSTOMER.join(DEVICE).on(CUSTOMER.ID.eq(DEVICE.CUSTOMERID)))
.where(DEVICE.STATE.eq(StateEnum.NEW))
.and(CUSTOMER.SECRETCONFIRMED.eq(true))
.returning(DEVICE.ID, CUSTOMER.SECRET)
.fetchOne();
ERROR: table name "device" specified more than once

Answer 1

这是因为 jOOQ API 中的一个旧设计错误，其中returning()子句导致返回类型为Result<R>或R，而不是您正在获取的实际列。因此，您只能从正在更新的表中返回列，而不能从任何其他表或任意表达式中返回列。由于向后兼容性，这不容易改变。

但是，从 jOOQ 3.11 开始，您现在可以调用returningResult()作为解决方法，以获取您指定的确切行类型：https://github.com/jOOQ/jOOQ/issues/7475