我有一個Tag表（device_id，customer_id 是這個表中的外鍵）：
------------------------------------------
| name | value | device_id | customer_id |
------------------------------------------
| a | a | 10 | 2389 |
------------------------------------------
| a | a | 20 | 2389 |
------------------------------------------
| a | a | 30 | 2389 |
------------------------------------------
|tag-n | tag-v | 10 | 2389 |
------------------------------------------
我正在嘗試獲取帶有名稱和值的標簽（作為搜索過濾器）并回傳device_id計數，name并且value.
示例：如果我按 搜索name = a and value = a，則回應應為：
{
"customer_id": "2389",
"tags": [
{
"name": "a",
"value": "a",
"device_count": 3 //since 3 devices have same name/value pairs
}
],
"pagination": {
"offset": 0,
"page": 0,
"count_per_page": 1,
"total_count": 1
}
}
我的服務邏輯中的條件查詢是：
PageRequest pageRequest = (!StringUtils.isBlank(sortBy)) ? PageRequest.of(page, limit, Sort.by(sortBy)) : PageRequest.of(page, limit);
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();
Root<TagEntity> root = query.from(TagEntity.class);
// add predicates
Predicate[] predArray = new Predicate[predicates.size()];
predicates.toArray(predArray);
query.multiselect(root.get(TagEntity_.name), root.get(TagEntity_.value), criteriaBuilder.count(deviceJoin.get(DeviceEntity_.ID)))
.where(predArray).groupBy(root.get(TagEntity_.name), root.get(TagEntity_.value));
TypedQuery<Tuple> typedQuery = em.createQuery(query);
//this works fine
List<Tuple> result = typedQuery
.setFirstResult((int) pageRequest.getOffset())
.setMaxResults(pageRequest.getPageSize())
.getResultList();
//This code fails with InvalidPathException: 'generatedAlias2.customerId'
CriteriaQuery<Long> countQuery = criteriaBuilder.createQuery(Long.class);
Root<TagEntity> tagCountRoot = countQuery.from(TagEntity.class);
countQuery.select(criteriaBuilder.count(tagCountRoot)).where(predArray);
Long count = em.createQuery(countQuery).getResultList().get(0);
Page<Tuple> tagEntities = new PageImpl<>(result, pageRequest, count);
我的問題是如何撰寫總計數查詢，因為在我的情況下，我得到的Tuple不是整個TagEntity. 任何幫助將不勝感激。
TIA。
PS我已經創建了如下Predicates相同的：method
List<Predicate> predicates = new ArrayList<>();
Join<TagEntity, DeviceEntity> deviceJoin = root.join(TagEntity_.deviceEntity, JoinType.LEFT);
Join<TagEntity, CustomerEntity> customerJoin = root.join(TagEntity_.customerEntity, JoinType.LEFT);
predicates.add(criteriaBuilder.equal(customerJoin.get(CustomerEntity_.customerId), customerId));
//search by exact name and exact value
predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get(TagEntity_.name), exactName), criteriaBuilder.equal(root.get(TagEntity_.value), exactValue)));
uj5u.com熱心網友回復：
即使在默認情況下，您也必須始終使用分頁排序。
計數distinct(name, value)對更簡單。不要忘記對它們進行排序。
最好在這里使用內部連接
Join<TagEntity, CustomerEntity> customerJoin = root.join(TagEntity_.customerEntity, JoinType.LEFT);
uj5u.com熱心網友回復：
問題已解決。我predicates對可能導致exception. 一個更好的方法是創建specification如下：
private Specification<TagEntity> getTagEntitySpecification(String customerId, String name, String value) {
return (Root<TagEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) -> {
List<Predicate> predicates = new ArrayList<>();
Join<TagEntity, CustomerEntity> customerJoin = root.join(TagEntity_.customerEntity, JoinType.INNER);
predicates.add(criteriaBuilder.equal(customerJoin.get(CustomerEntity_.customerId), customerId));
//search by name and value
predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get(TagEntity_.name), exactName),
criteriaBuilder.equal(root.get(TagEntity_.value), exactValue)));
return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
};
}
使用specification.toPredicate(root, criteriaQuery, criteriaBuilder)inwhere子句如下所示：
final Specification<TagEntity> specification = getTagEntitySpecification(customerId, name, value);
// query 1
query.multiselect(root.get(TagEntity_.name), root.get(TagEntity_.value),criteriaBuilder.count(deviceJoin.get(DeviceEntity_.ID))).where(specification.toPredicate(root, query, criteriaBuilder)).groupBy(root.get(TagEntity_.name), root.get(TagEntity_.value));
//count query
countQuery.select(cbCount.count(tagCountRoot)).where(specification.toPredicate(tagCountRoot, countQuery, cbCount));
Long count = em.createQuery(countQuery).getResultList().get(0);
tagEntities = new PageImpl<>(result, pageRequest, count);
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/432644.html