java - 如何将oracle转换为JOOQ？

Question

我们面临与将 Oracle 查询转换为 JOOQ 对象相关的问题。

Oracle 查询在这里:

select v0 AddressLine1, v1 AddressLine2, v2 AddrCity, v3 AddrProvince, v4 AddrPostal, v5 NameFirst, v6 NameMiddle,
  v7 AddrStreet, v8 NameLast, v9 Phone1, v10 PeopleCode, v11 LicenceNumber, v12 FamilyRSN, v13 PeopleRSN, v14 EmailAddress,
  v15 AddrHouse, v16 StatusCode, v17 ParentRSN, v18 StatusType, v19 FullName 
  from (select x.v0, x.v1, x.v2, x.v3, x.v4, x.v5, x.v6, x.v7, x.v8, x.v9, x.v10, x.v11, x.v12, x.v13, x.v14, x.v15, x.v16, 
  x.v17, x.v18, x.v19, rownum rn from (select amanda7.People.AddressLine1 v0, amanda7.People.AddressLine2 v1, 
  amanda7.People.AddrCity v2, amanda7.People.AddrProvince v3, amanda7.People.AddrPostal v4, amanda7.People.NameFirst v5, 
  amanda7.People.NameMiddle v6, amanda7.People.AddrStreet v7, amanda7.People.NameLast v8, amanda7.People.Phone1 v9, 
  amanda7.People.PeopleCode v10, amanda7.People.LicenceNumber v11, amanda7.People.FamilyRSN v12, amanda7.People.PeopleRSN v13,
  amanda7.People.EmailAddress v14, amanda7.People.AddrHouse v15, amanda7.People.StatusCode v16, amanda7.People.ParentRSN v17,
  amanda7.People.StatusType v18, (nvl(amanda7.People.OrganizationName, '') || nvl(amanda7.People.NameTitle, '') 
  || nvl(amanda7.People.NameFirst, '') || nvl(amanda7.People.NameMiddle, '') || nvl(amanda7.People.NameLast, '') ||
  nvl(amanda7.People.NameSuffix, '')) v19 from amanda7.People order by v19 desc) x where rownum <= (0 + 200))  where rn > 0;

JOOQ在这里:

private SelectQuery<Record> getPeopleListQuery(Collection<SQLCondition<?>> conditions) {

        List<Field<?>> fields = new ArrayList<>();
        fields.add(People.PEOPLE.ADDRESS_LINE_1);
        fields.add(People.PEOPLE.ADDRESS_LINE_2);
        fields.add(People.PEOPLE.ADDR_CITY);
        fields.add(People.PEOPLE.ADDR_PROVINCE);
        fields.add(People.PEOPLE.ADDR_POSTAL);
        fields.add(People.PEOPLE.NAME_FIRST);
        fields.add(People.PEOPLE.NAME_MIDDLE);
        fields.add(People.PEOPLE.ADDR_STREET);
        fields.add(People.PEOPLE.NAME_LAST);
        fields.add(People.PEOPLE.PHONE_1);
        fields.add(People.PEOPLE.PEOPLE_CODE);
        fields.add(People.PEOPLE.LICENCE_NUMBER);
        fields.add(People.PEOPLE.FAMILY_RSN);
        fields.add(People.PEOPLE.PEOPLE_RSN);
        fields.add(People.PEOPLE.EMAIL_ADDRESS);
        fields.add(People.PEOPLE.ADDR_HOUSE);
        fields.add(People.PEOPLE.STATUS_CODE);
        fields.add(People.PEOPLE.PARENT_RSN);
        fields.add(People.PEOPLE.STATUS_TYPE);
        // Added to provide sort functionality on peopleName on header sort.
        fields.add(DSL.concat(DSL.isnull(People.PEOPLE.ORGANIZATION_NAME, ""), DSL.isnull(People.PEOPLE.NAME_TITLE, ""),
                DSL.isnull(People.PEOPLE.NAME_FIRST, ""), DSL.isnull(People.PEOPLE.NAME_MIDDLE, ""), DSL.isnull(People.PEOPLE.NAME_LAST, ""),
                DSL.isnull(People.PEOPLE.NAME_SUFFIX, "")).as("FullName"));
        advanceSearchSelectQuery.addFields(fields);
        advanceSearchSelectQuery.addOrderBy(SortDialogViewModelNew.createJSONString(userSortingChoice));
        return advanceSearchSelectQuery.getSelectQuery();
    }

此 JOOQ 查询给出异常无效的列名，因为我们使用 order by 子句并使用别名作为 FullName 。此查询对于 SQL 工作正常，但在 Oracle DB 中给出异常。在 Oracle DB 中order by 子句下面生成的查询给出 v19 而不是 FullName :

 select v0 AddressLine1, v1 AddressLine2, v2 AddrCity, v3 AddrProvince, v4 AddrPostal, v5 NameFirst, v6 NameMiddle,
  v7 AddrStreet, v8 NameLast, v9 Phone1, v10 PeopleCode, v11 LicenceNumber, v12 FamilyRSN, v13 PeopleRSN, v14 EmailAddress,
  v15 AddrHouse, v16 StatusCode, v17 ParentRSN, v18 StatusType, v19 FullName 
  from (select x.v0, x.v1, x.v2, x.v3, x.v4, x.v5, x.v6, x.v7, x.v8, x.v9, x.v10, x.v11, x.v12, x.v13, x.v14, x.v15, x.v16, 
  x.v17, x.v18, x.v19, rownum rn from (select amanda7.People.AddressLine1 v0, amanda7.People.AddressLine2 v1, 
  amanda7.People.AddrCity v2, amanda7.People.AddrProvince v3, amanda7.People.AddrPostal v4, amanda7.People.NameFirst v5, 
  amanda7.People.NameMiddle v6, amanda7.People.AddrStreet v7, amanda7.People.NameLast v8, amanda7.People.Phone1 v9, 
  amanda7.People.PeopleCode v10, amanda7.People.LicenceNumber v11, amanda7.People.FamilyRSN v12, amanda7.People.PeopleRSN v13,
  amanda7.People.EmailAddress v14, amanda7.People.AddrHouse v15, amanda7.People.StatusCode v16, amanda7.People.ParentRSN v17,
  amanda7.People.StatusType v18, (nvl(amanda7.People.OrganizationName, '') || nvl(amanda7.People.NameTitle, '') 
  || nvl(amanda7.People.NameFirst, '') || nvl(amanda7.People.NameMiddle, '') || nvl(amanda7.People.NameLast, '') ||
  nvl(amanda7.People.NameSuffix, '')) v19 from amanda7.People order by FullName desc) x where rownum <= (0 + 200))  where rn > 0;

任何人都可以帮我解决这个问题。

谢谢西坦苏

Answer 1

您遇到的问题已在 jOOQ 3.5.0 中修复:https://github.com/jOOQ/jOOQ/issues/2080

这是一个具有回归风险的重大更改，这就是该修复程序未向后移植到 jOOQ 3.4.x 版本的原因。如果您升级 jOOQ 版本，您的查询应该可以正常运行。