如何将甲骨文转换为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;

在此：

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 .This查询，但是在Oracle中，它为exception.In Oracle DB提供了order子句下面生成的查询，给予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版本，那么您的查询应该运行得很好。