开发涉及到JPQL子查询,网上找半天资料没找着,自己动手实验了一波,如下,为老系统的sql查询,是用mybatis实现的。系统重构技术选型spring data jpa,没有好的实现方法,所以仍然采用老系统的查询逻辑。老系统的查询逻辑如下所示:
选出每种类型中时间最大的那条数据
解决方案为:
public Page<ContractInterBaseRate> findcontraceQuery(Pageable pageable, ContractInterBaseRateDTO cibr) {
QContractInterBaseRate qcontracr = QContractInterBaseRate.contractInterBaseRate;
QContractInterBaseRate q = new QContractInterBaseRate("temp");
// 利率管理信息查询
JPQLQuery<ContractInterBaseRate> query = this.repository.createDslQuery().select(qcontracr).from(qcontracr);
BooleanExpression exp = qcontracr.id.goe(0);
// 最新/历史(根据老系统查询方式查询)
if (StringUtils.isNotBlank(cibr.getNewOrHistory())) {
this.repository.createDslQuery().select(q.executeStartDate).from(q);
exp = exp.and(qcontracr.executeStartDate.eq(this.repository.createDslQuery().select(q.executeStartDate.max())
.from(q).where(q.interestRateTypeCd.eq(qcontracr.interestRateTypeCd))));
}
final JPQLQuery<ContractInterBaseRate> countQuery = query.where(exp);;
JPQLQuery<ContractInterBaseRate> queryResult = this.repository.getQuerydsl(ContractInterBaseRate.class)
.applyPagination(pageable, countQuery);
List<ContractInterBaseRate> contractList = queryResult.fetch();
Page<ContractInterBaseRate> contracrPage = PageableExecutionUtils.getPage(contractList, pageable, () -> countQuery.fetchCount());
return contracrPage;
}
遇到的问题为:
- 1、构造Q对象的时候,不能用
QContractInterBaseRate qcontracr = QContractInterBaseRate.contractInterBaseRate;
因为QContractInterBaseRate中contractInterBaseRate属性是static final的,如果都直接获取的话,子查询里边的
QContractInterBaseRate q = QContractInterBaseRate.contractInterBaseRate;(q.interestRateTypeCd.eq(qcontracr.interestRateTypeCd)
会变成自己关联自己,有问题