小贝子编程

jOOq Replacement For DSL.fieldByName



我碰巧遇到了一个非常有趣的查询和问题。我们正在运行Oracle12c（大约一年前从11g迁移到现在），最近刚刚从jOOq3.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，而不是连接SENSITITIES和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

相关内容

  • 没有找到相关文章

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium