代码之家 › 专栏 › 技术社区 › nehacharya

使用Criteria API计算元组查询的总计数?

criteria-api hibernate-criteria jpa spring-boot java

nehacharya · 技术社区 · 4 年前

我有一个 Tag 表(设备id、客户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);

我的问题是如何编写total count查询,因为在我的例子中,我得到了一个 Tuple 不是全部 TagEntity .任何帮助都将不胜感激。

蒂娅。

另外,我已经创造了 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)));

1 回复 | 直到 4 年前

v.ladynev 4 年前

即使是在默认情况下,也必须始终使用分页排序。

计数更简单 distinct(name, value) 对。别忘了把它们分类。

最好在这里使用内部连接

Join<TagEntity, CustomerEntity> customerJoin = root.join(TagEntity_.customerEntity, JoinType.LEFT);

nehacharya 4 年前

问题解决了。我用的是同样的 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) 在里面 where 条款如下所示:

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);