只有少数SQL方言(例如Oracle)支持未命名的派生表,而不是全部(例如PostgreSQL中不支持)。这就是为什么jooq为每个派生表生成别名的原因。既然派生表有别名,就不能再使用原始表名访问其列,但必须使用别名。您可以在生成的SQL查询中看到错误的地方:
select
"alias_88420990"."ledger_id", -- These are correctly referenced, because you used
"alias_88420990"."amount" -- select(), so jOOQ did the dereferencing for your
from (
select
"public"."test_safes_funds_allocation"."ledger_id",
sum("public"."test_safes_funds_allocation"."amount") as "amount"
from "public"."test_safes_funds_allocation"
group by "public"."test_safes_funds_allocation"."ledger_id"
) as "alias_88420990" -- This alias is generated by jOOQ
-- In these predicates, you're referencing the original column name with full qualification
-- when you should be referncing the column from alias_88420990 instead
where ("public"."test_safes_funds_allocation"."amount" >= ? and 1 = 1)
order by "public"."test_safes_funds_allocation"."amount" asc
limit ?
标准固定
因此,可以这样重写jooq查询,以便正确地获取表名:
// Create a local variable to contain your subquery. Ideally, provide an explicit alias
Table> t = table(
select(
TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID,
sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT)
.as(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT))
.from(TEST_SAFES_FUNDS_ALLOCATION)
.groupBy(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID)).as("t");
// Now, use t everywhere, instead of TEST_SAFES_FUNDS_ALLOCATION
context.select()
.from(t)
.where(t.field(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).greaterOrEqual(amount))
.and(t.field(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID).notIn(excludedLedgers))
.orderBy(t.field(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).asc())
.limit(1)
.fetchOne();
我在用
Table.field(Field)
从别名表中提取字段,而不丢失类型信息。
改进了修复,利用现有的表类型
考虑到派生的两列的名称也出现在原始表中,您可以使用“技巧”从jooq api中获得更多类型安全性,从而更方便地取消对列的引用。首先为表命名:
// This t reference now has all the column references like the original table
TestSafesFundsAllocation t = TEST_SAFES_FUNDS_ALLOCATION.as("t");
// The subquery is also named "t", but has a different definition
Table> subquery = table(
select(
TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID,
sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT)
.as(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT))
.from(TEST_SAFES_FUNDS_ALLOCATION)
.groupBy(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID)).as(t);
// Now, select again from the subquery, but dereference columns from the aliased table t
context.select()
.from(subquery)
.where(t.AMOUNT.greaterOrEqual(amount))
.and(t.LEDGER_ID.notIn(excludedLedgers))
.orderBy(t.AMOUNT.asc())
.limit(1)
.fetchOne();
记住,这是一个技巧,只要派生表的列与派生表选择的原始表具有相同的名称和类型,它就可以工作。
重写SQL
派生表(和公共表表达式)是一个领域,在这个领域中,jooq的DSL并没有本机SQL那么强大,因为jooq不能以通常的方式轻松地对派生表进行类型检查。这就是必须使用局部变量和类型不安全取消引用的原因。
通常,如果这是一个选项,那么这个警告是完全避免派生表的充分理由。在您的情况下,它是不需要派生表的。更好的查询(在本机SQL中更好)是:
context.select(
TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID,
sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).as(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT))
.from(TEST_SAFES_FUNDS_ALLOCATION)
.where(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID.notIn(excludedLedgers))
.groupBy(TEST_SAFES_FUNDS_ALLOCATION.LEDGER_ID)
.having(sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).greaterOrEqual(amount))
.orderBy(sum(TEST_SAFES_FUNDS_ALLOCATION.AMOUNT).asc())
.limit(1)
.fetchOne()