问题描述
我已经在JPA中定义了模型,并为我的应用程序编写了一些查询,并且我正在使用JOOQ生成的类将所有表连接在一起,以检查所请求的资源是否实际上属于发出请求的用户.
I have defined my models in JPA and am writing some queries for my application and I am using JOOQ generated classes to join all the tables together to check if the requested resources actually belong to the requesting user.
但是,当我这样做时,会收到以下警告:
However, when I do this I get the following warning:
Ambiguous match found for ID. Both "alias_4548634"."ID" and "alias_47496750"."ID" match.
java.sql.SQLWarning: null
at org.jooq.impl.Fields.field(Fields.java:132) ~[jooq-3.11.10.jar:?]
... etc
这是我的代码
db.select(countField)
.from(thing)
.where(JThing.THING.thingBucket().bucket().organization().customer().ID.in(idList))
.orderBy(countField)
这是它生成的SQL
SELECT
count(PUBLIC.THING.ID) AS count
FROM (
PUBLIC.THING
LEFT OUTER JOIN (
PUBLIC.THING_BUCKET AS alias_72652126
LEFT OUTER JOIN (
PUBLIC.BUCKET AS alias_4548634
LEFT OUTER JOIN (
PUBLIC.ORGANISATION AS alias_43016761
LEFT OUTER JOIN PUBLIC.CUSTOMER AS alias_47496750
ON alias_43016761.CUSTOMER_ID = alias_47496750.ID
)
ON alias_4548634.ORGANISATION_ID = alias_43016761.ID
)
ON alias_72652126.ID = alias_4548634.ID
)
ON PUBLIC.THING.THING_BUCKET_ID = alias_72652126.ID
)
WHERE alias_47496750.ID IN (81353)
ORDER BY count
鉴于JOOQ正在生成SQL,我希望它能够理解它而不会引发错误.我想念什么?如何配置/查询/以任何方式解决SQLWarning?
Given that JOOQ is generating the SQL I'd expect it to be able to understand it without throwing an error. What am I missing? How do I do configure/query/whatever to resolve the SQLWarning?
更新
玩转之后,我确定了问题的根源.
After playing around I've identified the source of the issue.
THING_BUCKET是BUCKET的子类型,因此THING_BUCKET.ID = BUCKET.ID
THING_BUCKET is sub-type of BUCKET so that THING_BUCKET.ID = BUCKET.ID
如果我将查询重写为相同的结果,但没有错误
if I rewrite the query to I get the same results, but without the error
SELECT
count(PUBLIC.THING.ID) AS count
FROM (
PUBLIC.THING
LEFT OUTER JOIN (
PUBLIC.BUCKET AS alias_4548634
LEFT OUTER JOIN (
PUBLIC.ORGANISATION AS alias_43016761
LEFT OUTER JOIN PUBLIC.CUSTOMER AS alias_47496750
ON alias_43016761.CUSTOMER_ID = alias_47496750.ID
)
ON alias_4548634.ORGANISATION_ID = alias_43016761.ID
)
ON PUBLIC.THING.BUCKET_ID = alias_4548634.ID
)
WHERE alias_47496750.ID IN (81353)
ORDER BY count
所以我想做的就是去
db.select(countField)
.from(thing)
.where(JThing.THING.bucket().organization().customer().ID.in(idList))
.orderBy(countField)
直接将我的东西加入BUCKET,然后再加入THING_BUCKET,但是我不知道如何使用生成的类来完成此工作.
and join my THING directly to the BUCKET rather then the THING_BUCKET, but I do not know how to accomplish this with the generated classes.
推荐答案
这看起来像是jOOQ 3.14中已修复的错误,请参见#8659 ,#10603
This looks like a bug that has been fixed in jOOQ 3.14, see #8659, #10603
这篇关于链接生成的Jooq类时如何解决歧义匹配的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!