个人中心
文章发布
退出
作者热门文章
- html - 出于某种原因，IE8 对我的 Sass 文件中继承的 html5 CSS 不友好？
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景？
25
4
我碰巧有一个非常有趣的查询和问题。我们正在运行 Oracle 12c(大约一年前从 11g 迁移)，最近刚刚从 jOOq 3.4.x 迁移到 3.7.3，我不想使用现在已弃用的 DSL.fieldByName ，但我很难找到真正的就像从 DSL.field 选项中替换一样。以下是 3.4.x 中生成的查询(为了安全起见，模式名称和表名称被隐藏):
select
"v0" "pTitle",
"v1" "pCode",
"v2" "description",
"v3" "coid",
"v4" "viewCount",
"v5" "fileId",
"v6" "displayPos"
from (
select
"x"."v0",
"x"."v1",
"x"."v2",
"x"."v3",
"x"."v4",
"x"."v5",
"x"."v6",
rownum "rn"
from (
select
"pTitle" "v0",
"pCode" "v1",
"description" "v2",
"coid" "v3",
"viewCount" "v4",
"fileId" "v5",
rownum "v6"
from (
select
"SCHEMA_A"."LIBRARY"."TITLE" "pTitle",
"LibraryType"."CODE" "pCode",
"SCHEMA_A"."LIBRARY"."DESCRIPTION" "description",
"SCHEMA_A"."AC_ASSOC_TABLE"."OBJECT_ID" "coid",
"SCHEMA_A"."AC_ASSOC_TABLE"."VIEW_COUNT" "viewCount",
"SCHEMA_A"."FILES"."FILE_ID" "fileId",
row_number() over (partition by "SCHEMA_A"."AC_ASSOC_TABLE"."OBJECT_ID" order by "SCHEMA_A"."FILES"."FILE_ID" desc nulls last) "maxFileId"
from "SCHEMA_A"."AC_ASSOC_TABLE"
join (
"SCHEMA_A"."LKU_CODE"
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"SCHEMA_A"."LKU_CODE"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'OBJECT_TYPE'
)
)
on (
"SCHEMA_A"."AC_ASSOC_TABLE"."OBJECT_TYPE_ID" = "SCHEMA_A"."LKU_CODE"."LKU_CODE_ID"
and "SCHEMA_A"."LKU_CODE"."CODE" = 'PORTFOLIOS'
)
join "SCHEMA_A"."AC_RELEASE_TYPE"
on (
"SCHEMA_A"."AC_RELEASE_TYPE"."AC_OBJECT_ID" = "SCHEMA_A"."AC_ASSOC_TABLE"."AC_OBJECT_ID"
and "SCHEMA_A"."AC_RELEASE_TYPE"."SECTION" = 1
and "SCHEMA_A"."AC_RELEASE_TYPE"."RELEASE_TYPE" in (
6, 7
)
)
join "SCHEMA_A"."AC_SENSITIVITIES"
on (
"SCHEMA_A"."AC_SENSITIVITIES"."AC_OBJECT_ID" = "SCHEMA_A"."AC_ASSOC_TABLE"."AC_OBJECT_ID"
and "SCHEMA_A"."AC_SENSITIVITIES"."SECTION" = 0
)
join "SCHEMA_A"."USERS"
on (
bitand(
"SCHEMA_A"."AC_SENSITIVITIES"."SENSITIVITIES",
"SCHEMA_A"."USERS"."SENSITIVITIES") = "SCHEMA_A"."AC_SENSITIVITIES"."SENSITIVITIES"
and "SCHEMA_A"."USERS"."USER_ID" = 456920
)
join (
"SCHEMA_A"."LIBRARY"
join (
"SCHEMA_A"."LKU_CODE"
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"SCHEMA_A"."LKU_CODE"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'STATUS'
)
)
on (
"SCHEMA_A"."LIBRARY"."LIBRARY_STATUS_ID" = "SCHEMA_A"."LKU_CODE"."LKU_CODE_ID"
and "SCHEMA_A"."LKU_CODE"."CODE" = 'ACTIVE'
)
join (
"SCHEMA_A"."LKU_CODE" "LibraryType"
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"LibraryType"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'PORTFOLIO_TYPE'
)
)
on (
"SCHEMA_A"."LIBRARY"."PORTFOLIO_TYPE_ID" = "LibraryType"."LKU_CODE_ID"
and "LibraryType"."CODE" in (
'ELEMENT', 'PROJECT', 'PROGRAM'
)
)
join (
"SCHEMA_A"."LKU_CODE"
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"SCHEMA_A"."LKU_CODE"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'ACTIVITY_TYPE'
)
)
on (
"SCHEMA_A"."LIBRARY"."ACTIVITY_TYPE_ID" = "SCHEMA_A"."LKU_CODE"."LKU_CODE_ID"
and "SCHEMA_A"."LKU_CODE"."CODE" in (
'MISSION_WITH_TECHNOLOGY', 'TECHNOLOGY_ONLY'
)
)
)
on "SCHEMA_A"."LIBRARY"."LIBRARY_ID" = "SCHEMA_A"."AC_ASSOC_TABLE"."OBJECT_ID"
left outer join (
"SCHEMA_A"."LIBRARY_ITEMS"
join "SCHEMA_A"."FILES"
on "SCHEMA_A"."LIBRARY_ITEMS"."LIBRARY_ITEM_ID" = "SCHEMA_A"."FILES"."PARENT_OBJECT_ID"
join (
"SCHEMA_A"."LKU_CODE"
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"SCHEMA_A"."LKU_CODE"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'LIBRARY_ITEM_TYPE'
)
)
on (
"SCHEMA_A"."LIBRARY_ITEMS"."LIBRARY_ITEM_TYPE_ID" = "SCHEMA_A"."LKU_CODE"."LKU_CODE_ID"
and "SCHEMA_A"."LKU_CODE"."CODE" = 'OBJECT_IMAGE'
)
)
on (
"SCHEMA_A"."LIBRARY_ITEMS"."LIBRARY_ID" = "SCHEMA_A"."AC_ASSOC_TABLE"."OBJECT_ID"
and "SCHEMA_A"."LIBRARY_ITEMS"."IS_PRIMARY" = 1
)
where "SCHEMA_A"."AC_ASSOC_TABLE"."VIEW_COUNT" is not null
order by "SCHEMA_A"."AC_ASSOC_TABLE"."VIEW_COUNT" desc
) "alias_101861728"
where "maxFileId" = 1
) "x"
where rownum <= (0 + 10)
)
where "rn" > 0
order by "rn"
下面是我可以使用 DSL.field(Name,Class<T>) 从 3.7.x 中获得的内容:
select
"v0" "pTitle",
"v1" "pCode",
"v2" "description",
"v3" "coid",
"v4" "viewCount",
"v5" "fileId",
"v6" "displayPos"
from (
select
"x"."v0",
"x"."v1",
"x"."v2",
"x"."v3",
"x"."v4",
"x"."v5",
"x"."v6",
rownum "rn"
from (
select
pTitle "v0",
pCode "v1",
description "v2",
coid "v3",
viewCount "v4",
fileId "v5",
rownum "v6"
from (
select
"SCHEMA_A"."LIBRARY"."TITLE" "pTitle",
"LibraryType"."CODE" "pCode",
"SCHEMA_A"."LIBRARY"."DESCRIPTION" "description",
"SCHEMA_A"."AC_ASSOC_TABLE"."OBJECT_ID" "coid",
"SCHEMA_A"."AC_ASSOC_TABLE"."VIEW_COUNT" "viewCount",
"SCHEMA_A"."FILES"."FILE_ID" "fileId",
row_number() over (partition by "SCHEMA_A"."AC_ASSOC_TABLE"."OBJECT_ID" order by "SCHEMA_A"."FILES"."FILE_ID" desc nulls last) "maxFileId"
from "SCHEMA_A"."AC_ASSOC_TABLE"
join (
"SCHEMA_A"."LKU_CODE"
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"SCHEMA_A"."LKU_CODE"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'OBJECT_TYPE'
)
)
on (
"SCHEMA_A"."AC_ASSOC_TABLE"."OBJECT_TYPE_ID" = "SCHEMA_A"."LKU_CODE"."LKU_CODE_ID"
and "SCHEMA_A"."LKU_CODE"."CODE" = 'PORTFOLIOS'
)
join "SCHEMA_A"."AC_RELEASE_TYPE"
on (
"SCHEMA_A"."AC_RELEASE_TYPE"."AC_OBJECT_ID" = "SCHEMA_A"."AC_ASSOC_TABLE"."AC_OBJECT_ID"
and "SCHEMA_A"."AC_RELEASE_TYPE"."SECTION" = 1
and "SCHEMA_A"."AC_RELEASE_TYPE"."RELEASE_TYPE" in (
6, 7
)
)
join "SCHEMA_A"."AC_SENSITIVITIES"
on (
"SCHEMA_A"."AC_SENSITIVITIES"."AC_OBJECT_ID" = "SCHEMA_A"."AC_ASSOC_TABLE"."AC_OBJECT_ID"
and "SCHEMA_A"."AC_SENSITIVITIES"."SECTION" = 0
)
join "SCHEMA_A"."USERS"
on (
bitand(
"SCHEMA_A"."AC_SENSITIVITIES"."SENSITIVITIES",
"SCHEMA_A"."USERS"."SENSITIVITIES") = "SCHEMA_A"."AC_SENSITIVITIES"."SENSITIVITIES"
and "SCHEMA_A"."USERS"."USER_ID" = 456920
)
join (
"SCHEMA_A"."LIBRARY"
join (
"SCHEMA_A"."LKU_CODE"
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"SCHEMA_A"."LKU_CODE"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'STATUS'
)
)
on (
"SCHEMA_A"."LIBRARY"."LIBRARY_STATUS_ID" = "SCHEMA_A"."LKU_CODE"."LKU_CODE_ID"
and "SCHEMA_A"."LKU_CODE"."CODE" = 'ACTIVE'
)
join (
"SCHEMA_A"."LKU_CODE" "LibraryType"
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"LibraryType"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'PORTFOLIO_TYPE'
)
)
on (
"SCHEMA_A"."LIBRARY"."PORTFOLIO_TYPE_ID" = "LibraryType"."LKU_CODE_ID"
and "LibraryType"."CODE" in (
'ELEMENT', 'PROJECT', 'PROGRAM'
)
)
join (
"SCHEMA_A"."LKU_CODE"
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"SCHEMA_A"."LKU_CODE"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'ACTIVITY_TYPE'
)
)
on (
"SCHEMA_A"."LIBRARY"."ACTIVITY_TYPE_ID" = "SCHEMA_A"."LKU_CODE"."LKU_CODE_ID"
and "SCHEMA_A"."LKU_CODE"."CODE" in (
'MISSION_WITH_TECHNOLOGY', 'TECHNOLOGY_ONLY'
)
)
)
on "SCHEMA_A"."LIBRARY"."LIBRARY_ID" = "SCHEMA_A"."AC_ASSOC_TABLE"."OBJECT_ID"
left outer join (
"SCHEMA_A"."LIBRARY_ITEMS"
join "SCHEMA_A"."FILES"
on "SCHEMA_A"."LIBRARY_ITEMS"."LIBRARY_ITEM_ID" = "SCHEMA_A"."FILES"."PARENT_OBJECT_ID"
join (
"SCHEMA_A"."LKU_CODE"
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"SCHEMA_A"."LKU_CODE"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'LIBRARY_ITEM_TYPE'
)
)
on (
"SCHEMA_A"."LIBRARY_ITEMS"."LIBRARY_ITEM_TYPE_ID" = "SCHEMA_A"."LKU_CODE"."LKU_CODE_ID"
and "SCHEMA_A"."LKU_CODE"."CODE" = 'OBJECT_IMAGE'
)
)
on (
"SCHEMA_A"."LIBRARY_ITEMS"."LIBRARY_ID" = "SCHEMA_A"."AC_ASSOC_TABLE"."OBJECT_ID"
and "SCHEMA_A"."LIBRARY_ITEMS"."IS_PRIMARY" = 1
)
where "SCHEMA_A"."AC_ASSOC_TABLE"."VIEW_COUNT" is not null
order by "SCHEMA_A"."AC_ASSOC_TABLE"."VIEW_COUNT" desc
) "alias_101861728"
where "maxFileId" = 1
) "x"
where rownum <= (0 + 10)
)
where "rn" > 0
order by "rn"
上面的重要一点是我们丢失了别名周围的引号。现在 Java 代码本身必须很有趣。似乎 jOOq(在 3.4.x 和 3.7.x 中)在为 Oracle 进行(精彩的)限制和偏移模拟时“忘记”使用别名，所以我不得不做一些诡计(3.4.x) :
final LkuCode libraryType = LkuCode.LKU_CODE.as("LibraryType");
final Field<Integer> maxFileId = DSL.rowNumber()
.over(DSL.partitionBy(co.OBJECT_ID).orderBy(f.FILE_ID.desc().nullsLast()))
.as("maxFileId");
final Field<String> pTitle = p.TITLE.as("pTitle");
final Field<String> pCode = libraryType.CODE.as("pCode");
final Field<String> description = p.DESCRIPTION.as("description");
final Field<BigDecimal> coid = co.OBJECT_ID.as("coid");
final Field<BigDecimal> viewCount = co.VIEW_COUNT.as("viewCount");
final Field<BigDecimal> fileId = f.FILE_ID.as("fileId");
final Field<String> pTitleAliasField = DSL.fieldByName(pTitle.getDataType(), pTitle.getName());
final Field<String> pCodeAliasField = DSL.fieldByName(pCode.getDataType(), pCode.getName());
final Field<String> descriptionAliasField = DSL.fieldByName(description.getDataType(), description.getName());
final Field<BigDecimal> coidAliasField = DSL.fieldByName(coid.getDataType(), coid.getName());
final Field<BigDecimal> viewCountAliasField = DSL.fieldByName(viewCount.getDataType(), viewCount.getName());
final Field<BigDecimal> fileIdAliasField = DSL.fieldByName(fileId.getDataType(), fileId.getName());
final Field<Integer> displayPos = (DSL.rownum().as("displayPos"));
final BigDecimal[] rts = new BigDecimal[] {RELEASE_TYPE_PUBLIC,
RELEASE_TYPE_INTERNAL};
final Condition limitByRelease = (cort.SECTION.equal(BigDecimal
.valueOf(AccessControlConstants.CORE_OBJECT_CHAR_ARRAY_PUBLIC_DATA)).and(cort.RELEASE_TYPE
.in(rts)));
final BigDecimal uid = new BigDecimal(DatabaseManager.getUid());
final Result<Record7<String, String, String, BigDecimal, BigDecimal, BigDecimal, Integer>> result;
try (Connection readConn = DatabaseManager.getConnection(false);
final DSLContext readContext = DatabaseManager.getBuilder(readConn)) {
final Select<Record7<String, String, String, BigDecimal, BigDecimal, BigDecimal, Integer>> query = readContext
.select(pTitleAliasField, pCodeAliasField, descriptionAliasField, coidAliasField,
viewCountAliasField, fileIdAliasField, displayPos)
.from(readContext
.select(pTitle, pCode, description, coid, viewCount, fileId, maxFileId)
.from(co
.join(lc.join(lct).onKey(Keys.LC_LCT___FK)
.and(lct.CODE_TYPE.equal(LkuCodeTypeLookup.OBJECT_TYPE)))
.onKey(Keys.CO_LC_OTI___FK)
.and(lc.CODE.equal(LkuCodeLookup.OBJECTTYPE_PORTFOLIOS))
.join(cort)
.onKey(Keys.CORT_CO___FK)
.and(limitByRelease)
.join(cos)
.onKey(Keys.COS_CO___FK)
.and(cos.SECTION.equal(BigDecimal
.valueOf(AccessControlConstants.CORE_OBJECT_CHAR_ARRAY_SELF)))
.join(tu)
.on(DSL.bitAnd(cos.SENSITIVITIES, tu.SENSITIVITIES)
.equal(cos.SENSITIVITIES).and(tu.USER_ID.equal(uid))))
.join(p
.join(lc.join(lct).onKey(Keys.LC_LCT___FK)
.and(lct.CODE_TYPE.equal(LkuCodeTypeLookup.PORTFOLIO_STATUS)))
.onKey(Keys.P_LC_PSI___FK)
.and(lc.CODE.equal(LkuCodeLookup.PORTFOLIO_STATUS_ACTIVE))
.join(libraryType.join(lct)
.on(libraryType.LKU_CODE_TYPE_ID.equal(lct.LKU_CODE_TYPE_ID))
.and(lct.CODE_TYPE.equal(LkuCodeTypeLookup.PORTFOLIO_TYPE)))
.on(p.PORTFOLIO_TYPE_ID.equal(libraryType.LKU_CODE_ID))
.and(libraryType.CODE
.in(new String[] {LkuCodeLookup.PORTFOLIO_TYPE_ELEMENT,
LkuCodeLookup.PORTFOLIO_TYPE_PROJECT,
LkuCodeLookup.PORTFOLIO_TYPE_PROGRAM}))
.join(lc.join(lct).onKey(Keys.LC_LCT___FK)
.and(lct.CODE_TYPE.equal(LkuCodeTypeLookup.ACTIVITY_TYPE)))
.onKey(Keys.P_LC_ATI___FK)
.and(lc.CODE.in(LkuCodeLookup.PORTFOLIO_ACTIVITY_TYPE_TECHNOLOGY)))
.on(p.PORTFOLIO_ID.equal(co.OBJECT_ID))
.leftOuterJoin(
li
.join(f)
.on(li.LIBRARY_ITEM_ID.equal(f.PARENT_OBJECT_ID))
.join(lc
.join(lct)
.onKey(Keys.LC_LCT___FK)
.and(lct.CODE_TYPE
.equal(LkuCodeTypeLookup.LIBRARY_ITEM_TYPE)))
.onKey(Keys.LI_LC_LITI___FK)
.and(lc.CODE.equal(LkuCodeLookup.LIBRARY_ITEM_TYPE_OBJECT_IMAGE)))
.on(li.PORTFOLIO_ID.equal(co.OBJECT_ID).and(
li.IS_PRIMARY.equal(byteOne)))
.where(co.VIEW_COUNT.isNotNull()).orderBy(co.VIEW_COUNT.desc()))
.where(maxFileId.equal(Integer.valueOf(1))).limit(numberOfObjects).offset(startNumber - 1);
result = query.fetch();
现在，我确实明白我可以使用 SQL EXISTS 而不是加入 SENSITVITIES 和 RELEASE_TYPES 表(以及其他几个表)，但这就是我现在正在做的事情，与此无关问题。如果还有什么我能做的，我很想听听。我尝试过完全不使用任何别名，但是事情变得一团糟:
final Select<Record7<String, String, String, BigDecimal, BigDecimal, BigDecimal, Integer>> query = readContext
.select(HomeDAO.p.PORTFOLIO_TITLE, portfolioType.CODE, HomeDAO.p.BRIEF_DESCRIPTION,
HomeDAO.co.OBJECT_ID, HomeDAO.co.VIEW_COUNT, HomeDAO.f.FILE_ID, displayPos)
.from(readContext
.select(HomeDAO.p.PORTFOLIO_TITLE, portfolioType.CODE, HomeDAO.p.BRIEF_DESCRIPTION,
HomeDAO.co.OBJECT_ID, HomeDAO.co.VIEW_COUNT, HomeDAO.f.FILE_ID, maxFileId)
.from(HomeDAO.co
.join(HomeDAO.lc.join(HomeDAO.lct).onKey(Keys.LC_LCT___FK)
.and(HomeDAO.lct.CODE_TYPE.equal(LkuCodeTypeLookup.OBJECT_TYPE)))
.onKey(Keys.CO_LC_OTI___FK)
.and(HomeDAO.lc.CODE.equal(LkuCodeLookup.OBJECTTYPE_PORTFOLIOS))
.join(HomeDAO.cort)
.onKey(Keys.CORT_CO___FK)
.and(limitByRelease)
.join(HomeDAO.cos)
.onKey(Keys.COS_CO___FK)
.and(HomeDAO.cos.SECTION.equal(BigDecimal
.valueOf(AccessControlConstants.CORE_OBJECT_CHAR_ARRAY_SELF)))
.join(HomeDAO.tu)
.on(DSL.bitAnd(HomeDAO.cos.SENSITIVITIES, HomeDAO.tu.SENSITIVITIES)
.equal(HomeDAO.cos.SENSITIVITIES).and(HomeDAO.tu.USER_ID.equal(uid))))
.join(HomeDAO.p
.join(HomeDAO.lc.join(HomeDAO.lct).onKey(Keys.LC_LCT___FK)
.and(HomeDAO.lct.CODE_TYPE.equal(LkuCodeTypeLookup.PORTFOLIO_STATUS)))
.onKey(Keys.P_LC_PSI___FK)
.and(HomeDAO.lc.CODE.equal(LkuCodeLookup.PORTFOLIO_STATUS_ACTIVE))
.join(portfolioType.join(HomeDAO.lct)
.on(portfolioType.LKU_CODE_TYPE_ID.equal(HomeDAO.lct.LKU_CODE_TYPE_ID))
.and(HomeDAO.lct.CODE_TYPE.equal(LkuCodeTypeLookup.PORTFOLIO_TYPE)))
.on(HomeDAO.p.PORTFOLIO_TYPE_ID.equal(portfolioType.LKU_CODE_ID))
.and(portfolioType.CODE
.in(new String[] {LkuCodeLookup.PORTFOLIO_TYPE_ELEMENT,
LkuCodeLookup.PORTFOLIO_TYPE_PROJECT,
LkuCodeLookup.PORTFOLIO_TYPE_PROGRAM}))
.join(HomeDAO.lc.join(HomeDAO.lct).onKey(Keys.LC_LCT___FK)
.and(HomeDAO.lct.CODE_TYPE.equal(LkuCodeTypeLookup.ACTIVITY_TYPE)))
.onKey(Keys.P_LC_ATI___FK)
.and(HomeDAO.lc.CODE.in(LkuCodeLookup.PORTFOLIO_ACTIVITY_TYPE_TECHNOLOGY)))
.on(HomeDAO.p.PORTFOLIO_ID.equal(HomeDAO.co.OBJECT_ID))
.leftOuterJoin(
HomeDAO.li
.join(HomeDAO.f)
.on(HomeDAO.li.LIBRARY_ITEM_ID.equal(HomeDAO.f.PARENT_OBJECT_ID))
.join(HomeDAO.lc
.join(HomeDAO.lct)
.onKey(Keys.LC_LCT___FK)
.and(HomeDAO.lct.CODE_TYPE
.equal(LkuCodeTypeLookup.LIBRARY_ITEM_TYPE)))
.onKey(Keys.LI_LC_LITI___FK)
.and(HomeDAO.lc.CODE.equal(LkuCodeLookup.LIBRARY_ITEM_TYPE_OBJECT_IMAGE)))
.on(HomeDAO.li.PORTFOLIO_ID.equal(HomeDAO.co.OBJECT_ID).and(
HomeDAO.li.IS_PRIMARY.equal(HomeDAO.byteOne)))
.where(HomeDAO.co.VIEW_COUNT.isNotNull()).orderBy(HomeDAO.co.VIEW_COUNT.desc()))
.where(maxFileId.equal(Integer.valueOf(1))).limit(numberOfObjects).offset(startNumber - 1);
其产量:
select
"v0" "TITLE",
"v1" "CODE",
"v2" "DESCRIPTION",
"v3" "OBJECT_ID",
"v4" "VIEW_COUNT",
"v5" "FILE_ID",
"v6" "displayPos"
from (
select
"x"."v0",
"x"."v1",
"x"."v2",
"x"."v3",
"x"."v4",
"x"."v5",
"x"."v6",
rownum "rn"
from (
select
"SCHEMA_A"."LIBRARY"."TITLE" "v0",
"PortfolioType"."CODE" "v1",
"SCHEMA_A"."LIBRARY"."DESCRIPTION" "v2",
"SCHEMA_A"."AC_ASSOC_TABLE"."OBJECT_ID" "v3",
"SCHEMA_A"."AC_ASSOC_TABLE"."VIEW_COUNT" "v4",
"SCHEMA_A"."FILES"."FILE_ID" "v5",
row_number "v6"
from (
select
"SCHEMA_A"."LIBRARY"."TITLE",
"PortfolioType"."CODE",
"SCHEMA_A"."LIBRARY"."DESCRIPTION",
"SCHEMA_A"."AC_ASSOC_TABLE"."OBJECT_ID",
"SCHEMA_A"."AC_ASSOC_TABLE"."VIEW_COUNT",
"SCHEMA_A"."FILES"."FILE_ID",
row_number() over (partition by "SCHEMA_A"."AC_ASSOC_TABLE"."OBJECT_ID" order by "SCHEMA_A"."FILES"."FILE_ID" desc nulls last) "maxFileId"
from "SCHEMA_A"."AC_ASSOC_TABLE"
join (
"SCHEMA_A"."LKU_CODE"
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"SCHEMA_A"."LKU_CODE"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'OBJECT_TYPE'
)
)
on (
"SCHEMA_A"."AC_ASSOC_TABLE"."OBJECT_TYPE_ID" = "SCHEMA_A"."LKU_CODE"."LKU_CODE_ID"
and "SCHEMA_A"."LKU_CODE"."CODE" = 'PORTFOLIOS'
)
join "SCHEMA_A"."AC_RELEASE_TYPE"
on (
"SCHEMA_A"."AC_RELEASE_TYPE"."AC_OBJECT_ID" = "SCHEMA_A"."AC_ASSOC_TABLE"."AC_OBJECT_ID"
and "SCHEMA_A"."AC_RELEASE_TYPE"."SECTION" = 1
and "SCHEMA_A"."AC_RELEASE_TYPE"."RELEASE_TYPE" in (
6, 7
)
)
join "SCHEMA_A"."AC_SENSITIVITIES"
on (
"SCHEMA_A"."AC_SENSITIVITIES"."AC_OBJECT_ID" = "SCHEMA_A"."AC_ASSOC_TABLE"."AC_OBJECT_ID"
and "SCHEMA_A"."AC_SENSITIVITIES"."SECTION" = 0
)
join "SCHEMA_A"."USERS"
on (
bitand(
"SCHEMA_A"."AC_SENSITIVITIES"."SENSITIVITIES",
"SCHEMA_A"."USERS"."SENSITIVITIES") = "SCHEMA_A"."AC_SENSITIVITIES"."SENSITIVITIES"
and "SCHEMA_A"."USERS"."USER_ID" = 456920
)
join (
"SCHEMA_A"."LIBRARY"
join (
"SCHEMA_A"."LKU_CODE"
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"SCHEMA_A"."LKU_CODE"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'STATUS'
)
)
on (
"SCHEMA_A"."LIBRARY"."PORTFOLIO_STATUS_ID" = "SCHEMA_A"."LKU_CODE"."LKU_CODE_ID"
and "SCHEMA_A"."LKU_CODE"."CODE" = 'ACTIVE'
)
join (
"SCHEMA_A"."LKU_CODE" "PortfolioType"
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"PortfolioType"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'PORTFOLIO_TYPE'
)
)
on (
"SCHEMA_A"."LIBRARY"."LIBRARY_ITEM_ID" = "PortfolioType"."LKU_CODE_ID"
and "PortfolioType"."CODE" in (
'ELEMENT', 'PROJECT', 'PROGRAM'
)
)
join (
"SCHEMA_A"."LKU_CODE"
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"SCHEMA_A"."LKU_CODE"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'ACTIVITY_TYPE'
)
)
on (
"SCHEMA_A"."LIBRARY"."ACTIVITY_TYPE_ID" = "SCHEMA_A"."LKU_CODE"."LKU_CODE_ID"
and "SCHEMA_A"."LKU_CODE"."CODE" in (
'MISSION_WITH_TECHNOLOGY', 'TECHNOLOGY_ONLY'
)
)
)
on "SCHEMA_A"."LIBRARY"."LIBRARY_ITEM_ID" = "SCHEMA_A"."AC_ASSOC_TABLE"."OBJECT_ID"
left outer join (
"SCHEMA_A"."LIBRARY_ITEMS"
join "SCHEMA_A"."FILES"
on "SCHEMA_A"."LIBRARY_ITEMS"."LIBRARY_ITEM_ID" = "SCHEMA_A"."FILES"."PARENT_OBJECT_ID"
join (
"SCHEMA_A"."LKU_CODE"
join "SCHEMA_A"."LKU_CODE_TYPE"
on (
"SCHEMA_A"."LKU_CODE"."LKU_CODE_TYPE_ID" = "SCHEMA_A"."LKU_CODE_TYPE"."LKU_CODE_TYPE_ID"
and "SCHEMA_A"."LKU_CODE_TYPE"."CODE_TYPE" = 'LIBRARY_ITEM_TYPE'
)
)
on (
"SCHEMA_A"."LIBRARY_ITEMS"."LIBRARY_ITEM_TYPE_ID" = "SCHEMA_A"."LKU_CODE"."LKU_CODE_ID"
and "SCHEMA_A"."LKU_CODE"."CODE" = 'OBJECT_IMAGE'
)
)
on (
"SCHEMA_A"."LIBRARY_ITEMS"."LIBRARY_ITEM_ID" = "SCHEMA_A"."AC_ASSOC_TABLE"."OBJECT_ID"
and "SCHEMA_A"."LIBRARY_ITEMS"."IS_PRIMARY" = 1
)
where "SCHEMA_A"."AC_ASSOC_TABLE"."VIEW_COUNT" is not null
order by "SCHEMA_A"."AC_ASSOC_TABLE"."VIEW_COUNT" desc
) "alias_62833117"
where "maxFileId" = 1
) "x"
where rownum <= (0 + 10)
)
where "rn" > 0
order by "rn"
这种情况是一个 SQL 异常(ORA-00904: "SCHEMA_A"."FILES"."FILE_ID": invalid identifier - 错误位于行:26 列:7)。
仅使用第一组别名会导致错误的 SQL 查询，因为 jOOq 的模拟 LIMIT 不使用别名，而是使用全名。
final LkuCode libraryType = LkuCode.LKU_CODE.as("LibraryType");
final Field<Integer> maxFileId = DSL.rowNumber()
.over(DSL.partitionBy(co.OBJECT_ID).orderBy(f.FILE_ID.desc().nullsLast()))
.as("maxFileId");
final Field<String> pTitle = p.TITLE.as("pTitle");
final Field<String> pCode = libraryType.CODE.as("pCode");
final Field<String> description = p.DESCRIPTION.as("description");
final Field<BigDecimal> coid = co.OBJECT_ID.as("coid");
final Field<BigDecimal> viewCount = co.VIEW_COUNT.as("viewCount");
final Field<BigDecimal> fileId = f.FILE_ID.as("fileId");
final Select<Record7<String, String, String, BigDecimal, BigDecimal, BigDecimal, Integer>> query = readContext
.select(pTitle, pCode, description, coid, viewCount, fileId, displayPos)
.from(readContext
.select(pTitle, pCode, description, coid, viewCount, fileId, maxFileId)
产生片段:
select
"v0" "pTitle",
"v1" "pCode",
"v2" "description",
"v3" "coid",
"v4" "viewCount",
"v5" "fileId",
"v6" "displayPos"
from (
select
"x"."v0",
"x"."v1",
"x"."v2",
"x"."v3",
"x"."v4",
"x"."v5",
"x"."v6",
rownum "rn"
from (
select
"SCHEMA_A"."LIBRARY"."TITLE" "v0",
"LibraryType"."CODE" "v1",
"SCHEMA_A"."LIBRARY"."DESCRIPTION" "v2",
"SCHEMA_A"."AC_ASSOC_TABLE"."OBJECT_ID" "v3",
"SCHEMA_A"."AC_ASSOC_TABLE"."VIEW_COUNT" "v4",
"SCHEMA_A"."FILES"."FILE_ID" "v5",
rownum "v6"
from (
select
"SCHEMA_A"."LIBRARY"."TITLE" "pTitle",
"LibraryType"."CODE" "pCode",
"SCHEMA_A"."LIBRARY"."DESCRIPTION" "description",
"SCHEMA_A"."AC_ASSOC_TABLE"."OBJECT_ID" "coid",
"SCHEMA_A"."AC_ASSOC_TABLE"."VIEW_COUNT" "viewCount",
"SCHEMA_A"."FILES"."FILE_ID" "fileId",
row_number() over (partition by "SCHEMA_A"."AC_ASSOC_TABLE"."OBJECT_ID" order by "SCHEMA_A"."FILES"."FILE_ID" desc nulls last) "maxFileId"
最佳答案
据我在评论中所知，您可能会感到困惑field(Name,Class<T>)与 field(String,Class<T>)
field(Name,Class<T>) ) 生成一个带有正式的、可能限定的列名的字段，默认情况下由 jOOQ 引用该列名。这是您应该在这里使用的。更多信息请点击:http://www.jooq.org/doc/latest/manual/sql-building/names field(String,Class<T>) ) 生成一个包含一些纯 SQL 内容的字段。因此，jOOQ 无法引用该字符串，因为该字符串的语义是任意 SQL 表达式，不一定是标识符。更多信息请点击:http://www.jooq.org/doc/latest/manual/sql-building/plain-sql 关于java - jOOq 替代 DSL.fieldByName，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/36648558/
25
4
0
我正在编写一个具有以下签名的 Java 方法。 void Logger(Method method, Object[] args); 如果一个方法(例如 ABC() )调用此方法 Logger，它应该
我是 Java 新手。 我的问题是我的 Java 程序找不到我试图用作的图像文件一个 JButton。 (目前这段代码什么也没做，因为我只是得到了想要的外观第一的)。这是我的主课 代码: packag
好的，今天我在接受采访，我已经编写 Java 代码多年了。采访中说“Java 垃圾收集是一个棘手的问题，我有几个 friend 一直在努力弄清楚。你在这方面做得怎么样？”。她是想骗我吗？还是我的一生都
我的 friend 给了我一个谜语让我解开。它是这样的: There are 100 people. Each one of them, in his turn, does the following
如果我将使用 Java 5 代码的应用程序编译成字节码，生成的 .class 文件是否能够在 Java 1.4 下运行？ 如果后者可以工作并且我正在尝试在我的 Java 1.4 应用程序中使用 Jav
有关于why Java doesn't support unsigned types的问题以及一些关于处理无符号类型的问题。我做了一些搜索，似乎 Scala 也不支持无符号数据类型。限制是Java和S
我只是想知道在一个 java 版本中生成的字节码是否可以在其他 java 版本上运行 最佳答案 通常，字节码无需修改即可在 较新 版本的 Java 上运行。它不会在旧版本上运行，除非您使用特殊参数 (
我有一个关于在命令提示符下执行 java 程序的基本问题。 在某些机器上我们需要指定 -cp 。 (类路径)同时执行java程序 (test为java文件名与.class文件存在于同一目录下) jav
我已经阅读 StackOverflow 有一段时间了，现在我才鼓起勇气提出问题。我今年 20 岁，目前在我的家乡(罗马尼亚克卢日-纳波卡)就读 IT 大学。足以介绍:D。 基本上，我有一家提供簿记应用
我有 public JSONObject parseXML(String xml) { JSONObject jsonObject = XML.toJSONObject(xml); r
我已经在 Java 中实现了带有动态类型的简单解释语言。不幸的是我遇到了以下问题。测试时如下代码: def main() { def ks = Map[[1, 2]].keySet()
一直提示输入 1 到 10 的数字 - 结果应将 st、rd、th 和 nd 添加到数字中。编写一个程序，提示用户输入 1 到 10 之间的任意整数，然后以序数形式显示该整数并附加后缀。 public
我有这个 DownloadFile.java 并按预期下载该文件: import java.io.*; import java.net.URL; public class DownloadFile {
我想在 GUI 上添加延迟。我放置了 2 个 for 循环，然后重新绘制了一个标签，但这 2 个 for 循环一个接一个地执行，并且标签被重新绘制到最后一个。 我能做什么？ for(int i=0;
我正在对对象 Student 的列表项进行一些测试，但是我更喜欢在 java 类对象中创建硬编码列表，然后从那里提取数据，而不是连接到数据库并在结果集中选择记录。然而，自从我这样做以来已经很长时间了，
我知道对象创建分为三个部分: 声明 实例化 初始化 classA{} classB extends classA{} classA obj = new classB(1,1); 实例化 它必须使用
我有兴趣使用 GPRS 构建车辆跟踪系统。但是，我有一些问题要问以前做过此操作的人: GPRS 是最好的技术吗？人们意识到任何问题吗？ 我计划使用 Java/Java EE - 有更好的技术吗？ 如果
我可以通过递归方法反转数组，例如:数组={1,2,3,4,5} 数组结果={5,4,3,2,1}但我的结果是相同的数组，我不知道为什么，请帮助我。 public class Recursion { p
有这样的标准方式吗？ 包括 Java源代码-测试代码- Ant 或 Maven联合单元持续集成(可能是巡航控制)ClearCase 版本控制工具部署到应用服务器 最后我希望有一个自动构建和集成环境。
我什至不知道这是否可能，我非常怀疑它是否可能，但如果可以，您能告诉我怎么做吗？我只是想知道如何从打印机打印一些文本。 有什么想法吗？ 最佳答案 这里有更简单的事情。 import javax.swin
我是一名优秀的程序员,十分优秀！