Применить нумерацию страниц к сложному SQL-запросу Oracle с Row_Num и смещением ⇐ JAVA

[Anonymous]

У меня есть метод Java ниже, который используется для извлечения данных из базы данных Oracle с использованием собственного запроса, и он имеет несколько UNION в запросе, теперь я хотел применить разбиение на страницы с помощью ROW_NUMBER и смещения.
ниже мой Java метод:

Код: Выделить всё

public PagingResult fetch(PatDocumentSearchCriteria searchCriteria, Long practice, Long patient) {
final StringBuilder queryString = new StringBuilder();

queryString.append("WITH applicable_pat_documents AS (");
queryString.append("    SELECT * FROM");
queryString.append("        ( SELECT");
queryString.append("            pd.id id");
queryString.append("        FROM");
queryString.append("            pat_document pd");

if (!CollectionUtils.isEmpty(searchCriteria.getDocumentTypes())) {
queryString.append(" LEFT OUTER JOIN pat_document_type_def pdtd ON pd.patdocumenttypedef = pdtd.id ");
}

queryString.append("        WHERE");
queryString.append("            pd.practice = :" + QueryParams.PRACTICE_ID);
queryString.append("            AND pd.patient = :" + QueryParams.PATIENT_ID);
queryString.append("            AND pd.purgetype IS NULL");
queryString.append("            AND pd.reviewstatus IN ( 'REVIEWED', 'PENDING', 'NOT_REQUIRED' )");
queryString.append("            AND pd.draft = 0");
queryString.append("            AND pd.deleted = 0");
queryString.append("            AND (pd.patdocumentinternaltype in ('ClinicalNote', 'G1Attachment') OR exists (select 1 from PAT_DOCUMENT_ENTITY_REF pder where pder.PATDOCUMENT = pd.id))");
queryString.append("            AND NOT EXISTS (SELECT 1 from pat_result_attachment pra where pra.patdocument = pd.id)");

if (!CollectionUtils.isEmpty(searchCriteria.getDocumentTypes())) {
queryString.append("      AND ").append(DBUtils.generateNativeQueryInClause("PDTD.NAME", DOCUMENT_TYPE,
searchCriteria.getDocumentTypes().size()));
}

queryString.append("        ORDER BY");
queryString.append("            pd.dateofservice DESC,");
queryString.append("            pd.modifieddate DESC");
queryString.append("    )");
queryString.append(")");

queryString.append(" SELECT");
queryString.append("    pd.id           id,");
queryString.append("    DECODE(PD.PATDOCUMENTINTERNALTYPE");
queryString.append("        , 'ClinicalNote', PD.DOCUMENTNAME");
queryString.append("        , 'NurseNote', 'Nurse Note'");
queryString.append("        , PDTD.NAME) ");
queryString.append("            || DECODE(PD.patdocumentamendedstatus, 'Amended' , ' (Amended)', 'Replaced', ' (Replaced)')");
queryString.append("            || DECODE(cna.id,  null, '', ' (Addendum)' ) name,");
queryString.append("    pdtd.name       documenttype,");
queryString.append("    pd.dateOfService dateOfService,");
queryString.append("    trim(DECODE(PD.PATDOCUMENTINTERNALTYPE, '" + PatDocumentInternalType.ClinicalNote.name() + "', PKG_UTIL.PROVIDERNAME(UOW.ID) ,");
queryString.append("            '" + PatDocumentInternalType.NurseNote.name() + "' , pkg_orders.generateFullUserName(pd.ownerUser,2))) author, ");
queryString.append("    DECODE(PD.PATDOCUMENTINTERNALTYPE, '" + PatDocumentInternalType.ClinicalNote.name() + "', UOW.ID,");
queryString.append("            '" + PatDocumentInternalType.NurseNote.name() + "' , pd.ownerUser) authorId, ");
queryString.append("    pd.createddate  createddate,");
queryString.append("    pd.modifieddate modifieddate,");
queryString.append("    '").append(PatDocumentMetaDataDTO.EntityType.PatDocument).append("' entityType");
queryString.append(" FROM");
queryString.append("    applicable_pat_documents pr");
queryString.append("    JOIN pat_document pd ON pr.id = pd.id");
queryString.append("    LEFT OUTER JOIN PAT_DOCUMENT_TYPE_DEF PDTD ON PD.PATDOCUMENTTYPEDEF = PDTD.ID");
queryString.append("     LEFT OUTER JOIN USERS UOW ON PD.OWNERUSER = UOW.ID ");
queryString.append("    LEFT OUTER JOIN CLINICAL_NOTE_ADDENDUM cna on cna.patdocument = pd.id ");

queryString.append(" UNION");

queryString.append(" SELECT");
queryString.append("    id,");
queryString.append("    name,");
queryString.append("    NVL(documenttype,'").append(RESULTS_DOCUMENT_TYPE).append("') as documenttype,");
queryString.append("    reportDate dateofservice,");
queryString.append("    null author,");
queryString.append("    null authorId,");
queryString.append("    createddate,");
queryString.append("    modifieddate,");
queryString.append("    '").append(PatDocumentMetaDataDTO.EntityType.PatResultAttachment).append("' entityType");
queryString.append(" FROM");
queryString.append("    (");
queryString.append("    SELECT DISTINCT");
queryString.append("        pra.id id,");
queryString.append("        prh.reportDate,");
queryString.append("        fa.createdDate,");
queryString.append("        fa.modifieddate modifieddate,");
queryString.append("        pra.documentname name,");
queryString.append("        pdtd.name documenttype");
queryString.append("    FROM");
queryString.append("        pat_result_attachment pra");
queryString.append("        left join pat_document pd on pd.id = pra.patdocument");
queryString.append("        LEFT JOIN pat_document_type_def  pdtd ON pd.patdocumenttypedef = pdtd.id");
queryString.append("        INNER JOIN file_attachment fa ON fa.id = pra.fileattachment");
queryString.append("        INNER JOIN pat_result_header prh on prh.id = pra.patresultheader");
queryString.append("    WHERE");
queryString.append("        pra.practice = :" + QueryParams.PRACTICE_ID);
queryString.append("        and pra.patient = :" + QueryParams.PATIENT_ID);
queryString.append("        and prh.INTERFACEMESSAGEUID is null");
queryString.append("    )");

queryString.append(" UNION");

queryString.append(" SELECT");
queryString.append("    id,");
queryString.append("    conceptname as name,");
queryString.append("    '").append(RESULTS_DOCUMENT_TYPE).append("' documenttype,");
queryString.append("    reportDate  as dateofservice,");
queryString.append("    null author,");
queryString.append("    null authorId,");
queryString.append("    createddate,");
queryString.append("    modifieddate,");
queryString.append("    '").append(PatDocumentMetaDataDTO.EntityType.PatMolecularResult).append("' entityType");
queryString.append(" FROM ");
queryString.append("        pat_result_header prh");
queryString.append("    WHERE");
queryString.append("        prh.practice = :" + QueryParams.PRACTICE_ID);
queryString.append("        and prh.patient = :" + QueryParams.PATIENT_ID);
queryString.append("        and prh.INTERFACEMESSAGEUID is not null");

Query query = entityManager.createNativeQuery(queryString.toString())
.setParameter(QueryParams.PRACTICE_ID, practice)
.setParameter(QueryParams.PATIENT_ID, patient);

if (!CollectionUtils.isEmpty(searchCriteria.getDocumentTypes())) {
String queryVariable;
for (int i = 0; i < searchCriteria.getDocumentTypes().size(); i++) {
queryVariable = DOCUMENT_TYPE + i;
query.setParameter(queryVariable, searchCriteria.getDocumentTypes().get(i));
}
}

List
 results = DBUtils.generateList(query, PatDocumentMetaDataDTO.class,
new PatDocumentMetaDataDTO().propertyNames());

PagingResult  result = new PagingResult();
result.setResults(results);
result.setTotalResultCount(results.isEmpty() ? 0 : results.size());

return result;
}

Я пытался добавить его, но не понял, правильна ли моя примененная логика.
Пытался применить эту концепцию ROW_NUMBER и смещения, но кажется, что это так не работает.

Подробнее здесь: https://stackoverflow.com/questions/791 ... and-offset