我一直在尝试获取总行数,为此我使用了JPA Criteria API,但是在
以下是我的代码片段:
Long count = em.createQuery(sc).getSingleResult();
这一行中出现错误,报告java.lang.IllegalStateException: No criteria query roots were specified
。我做了一些研究,但无法缩小问题范围。以下是我的代码片段:
@PersistenceContext
public EntityManager em;
......
public Page<UserDTO> findByCriteria(String filters, Pageable pageable) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<UserDTO> cq = cb.createQuery(UserDTO.class);
Root<UserDTO> iRoot = cq.from(UserDTO.class);
List<Predicate> predicates = new ArrayList<Predicate>();
if (StringUtils.isNotEmpty(filters)) {
predicates.add(cb.like(cb.lower(iRoot.<String>get("login")), "%" + filters.toLowerCase() + "%"));
}
Predicate[] predArray = new Predicate[predicates.size()];
predicates.toArray(predArray);
CriteriaQuery<Long> sc = cb.createQuery(Long.class);
sc.select(cb.count(iRoot));
sc.where(predArray);
Long count = em.createQuery(sc).getSingleResult();
cq.where(predArray);
List<Order> orders = new ArrayList<Order>(2);
orders.add(cb.asc(iRoot.get("name")));
orders.add(cb.asc(iRoot.get("desc")));
cq.orderBy(orders);
TypedQuery<UserDTO> query = em.createQuery(cq);
Page<UserDTO> result = new PageImpl<UserDTO>(query.getResultList(), pageable, count);
return result;
}
编辑后且可工作的代码:
public Page<UserDTO> findByCriteria(String columnName, String filters, Pageable pageable) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<UserDTO> cq = cb.createQuery(UserDTO.class);
Root<UserDTO> iRoot = cq.from(UserDTO.class);
List<Predicate> predicates = new ArrayList<Predicate>();
if (StringUtils.isNotEmpty(filters)) {
predicates.add(cb.like(cb.lower(iRoot.<String>get(columnName)), "%" + filters.toLowerCase() + "%"));
}
Predicate[] predArray = new Predicate[predicates.size()];
predicates.toArray(predArray);
Long count = calculateCount(filters);
cq.where(predArray);
List<Order> orders = new ArrayList<Order>(2);
orders.add(cb.asc(iRoot.get("firstName")));
orders.add(cb.asc(iRoot.get("lastName")));
cq.orderBy(orders);
TypedQuery<UserDTO> query = em.createQuery(cq);
Page<UserDTO> result = new PageImpl<UserDTO>(query.getResultList(), pageable, count);
return result;
}
public Long calculateCount(String filters) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Long> sc = cb.createQuery(Long.class);
Root<UserDTO> iRoot = sc.from(UserDTO.class);
List<Predicate> predicates = new ArrayList<Predicate>();
if (StringUtils.isNotEmpty(filters)) {
predicates.add(cb.like(cb.lower(iRoot.<String>get("login")), "%" + filters.toLowerCase() + "%"));
}
Predicate[] predArray = new Predicate[predicates.size()];
predicates.toArray(predArray);
sc.select(cb.count(iRoot));
sc.where(predArray);
Long count = em.createQuery(sc).getSingleResult();
return count;
}
cq.select(qb.count(cq.from(MyEntity.class)));
。 - Scary Wombatcount
操作的方法? - Scary Wombatsc.from(UserDTO.class);
,那么它也会起作用。这行代码在旧代码中缺失,并指定查询应从哪个“Root”类选择。 - XtremeBaumer