我被困在了获取查询中唯一一行代码的查询中。
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使用。
我该怎么办?
1条答案
按热度按时间kg7wmglp1#
您需要将聚合函数与GROUPBY子句一起使用(不能与
query::fetchCount
在querydsl for jpa中)或具有窗口函数的不同元组(需要注册自定义函数)。即使这样,你也可能会在你的投影中与实体投影斗争。例如,你不能MAX(someEntity)
获取实体MAX(someEntity.id)
.使用普通的hibernate和querydsl无法做到这一点,但是可以使用针对querydsl的blaze持久性集成来做到这一点。使用这个hibernate扩展,您可以使用窗口函数或
fetchCount
在指定GROUP BY
或者HAVING
条款。您还可以使用blaze persistence cte或子查询横向连接来创建查询。