JPA子查询
单表查询、不太复杂的关联查询适合用JPA,除此以为,一些复杂的SQL,比如自定义动态分页查询真的不建议用JPA,实现起来比较麻烦,还不如MyBatis来得直接
以下面的子查询为例:
SELECT
t1.*
FROM approval_task t1
WHERE t1.approver_username = 'yangxiao'
AND NOT EXISTS (
SELECT
t2.id
FROM
approval_task t2
WHERE
t2.apply_id = t1.apply_id
AND t2.approver_username = t1.approver_username
AND t2.create_time > t1.create_time
)
ORDER BY
t1.task_type ASC,
t1.create_time DESC
LIMIT 20;
对应的jpa代码如下:
public Page<ApprovalTask> pageList(ApprovalTaskQueryCriteria criteria, Pageable pageable) {
return approvalTaskRepository.findAll(new Specification<ApprovalTask>() {
@Override
public Predicate toPredicate(Root<ApprovalTask> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
Subquery<ApprovalTask> subquery = query.subquery(ApprovalTask.class);
Root<ApprovalTask> subRoot = subquery.from(ApprovalTask.class);
List<Predicate> subList = new ArrayList<>();
subList.add(criteriaBuilder.equal(subRoot.get("applyId"), root.get("applyId")));
subList.add(criteriaBuilder.equal(subRoot.get("approverUsername"), root.get("approverUsername")));
subList.add(criteriaBuilder.gt(subRoot.get("createTime"), root.get("createTime")));
subquery.where(subList.toArray(new Predicate[subList.size()]));
subquery.select(subRoot.get("id"));
List<Predicate> list = new ArrayList<>();
list.add(criteriaBuilder.not(criteriaBuilder.exists(subquery)));
if (StringUtils.isNoneBlank(criteria.getApproverUsername())) {
list.add(criteriaBuilder.equal(root.get("approverUsername"), criteria.getApproverUsername()));
}
if (StringUtils.isNotBlank(criteria.getApplicantName())) {
list.add(criteriaBuilder.like(root.get("applicantName"), criteria.getApplicantName()));
}
if (StringUtils.isNotBlank(criteria.getIdCard())) {
list.add(criteriaBuilder.like(root.get("idCard"), criteria.getIdCard()));
}
if (null != criteria.getStatus()) {
list.add(criteriaBuilder.equal(root.get("status"), criteria.getStatus()));
}
if (null != criteria.getApplyStartTime() && null != criteria.getApplyEndTime()) {
list.add(criteriaBuilder.between(root.get("applyTime"), criteria.getApplyStartTime(), criteria.getApplyEndTime()));
}
return criteriaBuilder.and(list.toArray(new Predicate[list.size()]));
}
}, pageable);
}