java—如何使用querydsl在查询中按id删除重复行

whlutmcx  于 2021-07-03  发布在  Java
关注(0)|答案(1)|浏览(535)

我被困在了获取查询中唯一一行代码的查询中。

public JPQLQuery<RecurrenceErrorVO> getQueryErrorRecurrence(Integer companyCode, Collection<SearchModelDTO<Object>> searchModelDTOs) {
    //Entities
    QRecurrencyEntity qRecurrencyEntity = QRecurrencyEntity.recurrencyEntity;
    QSubscriptionEntity qSubscriptionEntity = QSubscriptionEntity.subscriptionEntity;
    QRecurrencyDetailsEntity qRecurrencyDetailsEntity = QRecurrencyDetailsEntity.recurrencyDetailsEntity;
    QSubcriptionProductEntity qSubcriptionProductEntity = QSubcriptionProductEntity.subcriptionProductEntity;
    QArticleEntity qArticleEntity = QArticleEntity.articleEntity;
    QCatalogoValorDTO qStatusGeneral = new QCatalogoValorDTO("qStatusGeneral");
    QCatalogoValorDTO qrecurrenceType = new QCatalogoValorDTO("qrecurrenceType");
    QCatalogoValorDTO qbusinessTypeDTO = new QCatalogoValorDTO("qbusinessTypeDTO");
    QCatalogoValorDTO qcausalCatalogType = new QCatalogoValorDTO("qcausalCatalogType");
    //data
    JPQLQuery<RecurrenceErrorVO> query = from(qRecurrencyDetailsEntity).select(
            Projections.bean(RecurrenceErrorVO.class, qRecurrencyEntity.codeRecurrency.as("codeRecurrence"),
                    qRecurrencyDetailsEntity.codeDetailsTransaction.as("codeRecurrenceDetail"),
                    qRecurrencyEntity.statusGeneralValue.as("statusGeneralValue"),
                    qRecurrencyEntity.processDate.as("dateRecurrence"),
                    qRecurrencyDetailsEntity.transactionDate.as("dateTransaction"),
                    qSubscriptionEntity.contractIdentifier,
                    qSubcriptionProductEntity.articleEntity.itemDescription.as("productName"),
                    qSubscriptionEntity.numberDocumentClient.as("clientDocNumber"),
                    qSubscriptionEntity.customerName.as("clientName"),
                    qSubscriptionEntity.subscriptionValue.as("subscriptionValue"),
                    qSubscriptionEntity.statusSubscriptionValue,
                    qStatusGeneral.nombreCatalogoValor.as("statusGeneralDescription"),
                    qRecurrencyEntity.originRecurrenceValue.as("originRecurrenceValue"),
                    qRecurrencyEntity.value,
                    qRecurrencyEntity.status,
                    qRecurrencyDetailsEntity.causalValue.as("codeCausalValue"),
                    qRecurrencyDetailsEntity.causalType.as("codeCausalType"),
                    qbusinessTypeDTO.nombreCatalogoValor.as("marca"),
                    qcausalCatalogType.nombreCatalogoValor.as("causalValue"),
                    qrecurrenceType.nombreCorto.as("nombreCorto"),
                    qrecurrenceType.nombreCatalogoValor.as("transactionType")))
    //join
    query.leftJoin(qRecurrencyDetailsEntity.recurrencyEntity, qRecurrencyEntity)
            .leftJoin(qRecurrencyEntity.subscriptionEntity, qSubscriptionEntity)
            .leftJoin(qRecurrencyEntity.subcriptionProductEntity, qSubcriptionProductEntity)
            .innerJoin(qSubcriptionProductEntity.articleEntity, qArticleEntity)
            .innerJoin(qRecurrencyEntity.statusGeneral, qStatusGeneral)
            .innerJoin(qSubcriptionProductEntity.businessTypeDTO, qbusinessTypeDTO)
            .innerJoin(qRecurrencyDetailsEntity.recurrenceType, qrecurrenceType)
            .innerJoin(qRecurrencyDetailsEntity.causalCatalogType, qcausalCatalogType)
    ;
    BooleanBuilder where = new BooleanBuilder();
    where.and(qRecurrencyEntity.statusGeneralValue.ne(SirConstants.RECURRENCE_STATUS_FIN));
    where.and(qRecurrencyEntity.statusGeneralValue.ne(SirConstants.RECURRENCE_STATUS_SIN_CUPO));
    where.and(qRecurrencyEntity.statusGeneralValue.ne(SirConstants.RECURRENCE_STATUS_FIN_WITH_OBSERVATION));
    where.and(qRecurrencyDetailsEntity.statusRecurrencyDetailsValue.ne(SirConstants.ZERO.toString()));
    where.and(qRecurrencyEntity.originRecurrenceValue.eq(SirConstants.RECURRENCE_ORIGIN_INTERNO));
    where.and(qRecurrencyEntity.companyCode.eq(companyCode));
    where.and(qRecurrencyEntity.status.isTrue());
    SearchModelUtil.addDynamicWhere(searchModelDTOs, where, RecurrencyEntity.class, "recurrencyEntity");
    query.where(where);
    query.orderBy(qRecurrencyEntity.codeRecurrency.desc(), qRecurrencyDetailsEntity.codeDetailsTransaction.desc());
    return query;
}

这是我当前的代码,它返回值​​作为下一张table

CODERECURRENCY|CODEDCURRENCYDETAILS|STATUSGENERALVALUE|PROCESSDATE        |TRANSACTIONDATE    
--------------|--------------------|------------------|-------------------|-------------------
         16202|               14510|ERR               |2020-10-23 12:08:26|2020-11-27 16:53:57
         16202|               14094|ERR               |2020-10-23 12:08:26|2020-10-23 12:08:47
         16202|               14093|ERR               |2020-10-23 12:08:26|2020-10-23 12:08:41
         16201|               14088|EXR               |2020-10-22 23:51:58|2020-10-22 23:53:43
         16201|               14087|EXR               |2020-10-22 23:51:58|2020-10-22 23:53:37
         16201|               14083|EXR               |2020-10-22 23:51:58|2020-10-22 23:53:15
         16201|               14082|EXR               |2020-10-22 23:51:58|2020-10-22 23:53:09
         16201|               14078|EXR               |2020-10-22 23:51:58|2020-10-22 23:52:47
         16201|               14077|EXR               |2020-10-22 23:51:58|2020-10-22 23:52:41
         16201|               14073|EXR               |2020-10-22 23:51:58|2020-10-22 23:52:19
         16201|               14072|EXR               |2020-10-22 23:51:58|2020-10-22 23:52:13
         16123|               13675|ERR               |2020-10-01 17:06:17|2020-10-01 17:06:28
         16050|               13511|ERR               |2020-09-21 14:11:31|2020-09-21 14:11:31
         16043|               13470|EXR               |2020-09-16 10:04:20|2020-09-16 10:07:00

需要一行一行的代码重现,我用代码删除重复行,但如果我把jpqlquery转换成一个列表,这个改变结果的值显示在webservice,我需要使用分页。
这段代码显示了查询的用法。

private PageResultVO<RecurrenceErrorVO> findPagedRE(JPQLQuery<RecurrenceErrorVO> query, Pageable pageable) {
        List<RecurrenceErrorVO> list = Objects.requireNonNull(getQuerydsl()).applyPagination(
                pageable, query).fetch();
          Page<RecurrenceErrorVO> page = PageableExecutionUtils.getPage(list,
                pageable,
                query::fetchCount);
        return new PageResultVO<>(page.getContent(), page.getPageable(), page.getTotalElements());
    }

我将这段代码作为带分页的webservice使用。
我该怎么办?

kg7wmglp

kg7wmglp1#

您需要将聚合函数与GROUPBY子句一起使用(不能与 query::fetchCount 在querydsl for jpa中)或具有窗口函数的不同元组(需要注册自定义函数)。即使这样,你也可能会在你的投影中与实体投影斗争。例如,你不能 MAX(someEntity) 获取实体 MAX(someEntity.id) .
使用普通的hibernate和querydsl无法做到这一点,但是可以使用针对querydsl的blaze持久性集成来做到这一点。使用这个hibernate扩展,您可以使用窗口函数或 fetchCount 在指定 GROUP BY 或者 HAVING 条款。您还可以使用blaze persistence cte或子查询横向连接来创建查询。

相关问题