JPA CriteriaQuery 实现 Spring Data Pageable.getSort()

Question

我使用了JPA CriteriaQuery来构建我的动态查询，并将Spring Data Pageable对象传递进去：

sort=name,desc

在后端，我的代码仓库中有一个支持动态查询的方法：

public Page<User> findByCriteria(String username, Pageable page) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<User> cq = cb.createQuery(User.class);
    Root<User> iRoot = cq.from(User.class);
    List<Predicate> predicates = new ArrayList<Predicate>();

    if (StringUtils.isNotEmpty(username)) {
        predicates.add(cb.like(cb.lower(iRoot.<String>get("username")), "%" + username.toLowerCase() + "%"));
    }

    Predicate[] predArray = new Predicate[predicates.size()];
    predicates.toArray(predArray);

    cq.where(predArray);

    TypedQuery<User> query = em.createQuery(cq);

    int totalRows = query.getResultList().size();

    query.setFirstResult(page.getPageNumber() * page.getPageSize());
    query.setMaxResults(page.getPageSize());

    Page<User> result = new PageImpl<User>(query.getResultList(), page, totalRows);

    return result;
}

注意：我只为演示目的放置了一个参数。

然而，返回的数据是未排序的，因此我想问是否有办法在CriteriaQuery中实现Pageable。

答案:

注意：我只为演示目的放置了一个参数。

然而，返回的数据是未排序的，因此我想问是否有办法在CriteriaQuery中实现Pageable。

- Javatar

---

为了获取结果集大小而获取整个结果集似乎浪费资源 - 这样会传输大量数据然后将其丢弃。相反，应该在JPA中调用什么方法来告诉查询只获取计数？ - chrisinmtown

#计算总行数CriteriaQuery cq = cb.createQuery(Long.class); cq.select(cb.count(cq.from(User.class))); Long totalRows = entityManager.createQuery(cq).getSingleResult();计算总行数的方法如上所示。首先，我们创建一个CriteriaQuery<Long>对象，并使用cb.createQuery(Long.class)进行初始化。然后，我们使用cq.select(cb.count(cq.from(User.class)))来选择并计算User类的总行数。最后，我们通过entityManager.createQuery(cq).getSingleResult()执行查询，并将结果存储在totalRows变量中。 - Shashikant Sharma

Answer 1

您可以使用Spring的QueryUtils添加排序：

query.orderBy(QueryUtils.toOrders(pageable.getSort(), root, builder));

- Konrad Początek

---

什么是构建器？ - Thx Xth

@ThxXth 这是 CriteriaBuilder。 - Javatar

Answer 2

Op的问题和Angelo的答案在实际场景中都无法使用。

如果该表/查询返回数千条记录，则这种方法将执行缓慢，并且还可能会创建内存问题。

为什么？

• int totalRows = query.getResultList().size();（会运行同一查询两次）
• new PageImpl (query.getResultList(), page, totalRows);（会在这些集合中维护完整的表/查询数据，只是为了进行计算或切片，当你需要在前端显示少量记录时，这是没有意义的。）

我对Spring Data的建议是，在您的存储库中扩展JpaSpecificationExecutor接口。

@Repository
public interface UserRepository extends JpaRepository<User, Integer>,
        JpaSpecificationExecutor {
}

该接口将使您的存储库能够使用findAll(Specification, Pageable)方法。

之后，一切都很容易。您只需要创建一个Java闭包，将谓词注入动态JPA查询中即可。

public Page<User> listUser( String name, int pageNumber, int pageSize ) {

Sort sort = Sort.by("id").ascending();
Pageable pageable = PageRequest.of(pageNumber, pageSize, sort);


return this.reservationRepository.findAll((root, query, builder) -> {
            List<Predicate> predicates = new ArrayList<>();
            if name!= null ) {
                predicates.add(builder.equal(root.get("name"), name));
            }
            // More simple/complex conditions can be added to 
            // predicates collection if needed.

            return builder.and(predicates.toArray(new Predicate[]{}));

        }, pageable);

}

如您所见，该解决方案支持排序/筛选和数据库端分页。

- Alejandro Abate

Answer 3

您的筛选查询中没有排序信息。我会这样写:

     CriteriaBuilder cb = em.getCriteriaBuilder();
     CriteriaQuery<User> cq = cb.createQuery(User.class);
     Root<User> iRoot = cq.from(User.class);
     List<Predicate> predicates = new ArrayList<Predicate>();

     if (StringUtils.isNotEmpty(username)) {
         predicates.add(cb.like(cb.lower(iRoot.<String>get("username")), "%" + username.toLowerCase() + "%"));
     }

     Predicate[] predArray = new Predicate[predicates.size()];
     predicates.toArray(predArray);

     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<User> query = em.createQuery(cq);
     Page<User> result = new PageImpl<User>(query.getResultList(), page, totalRows);

     return result;

我没有测试过，但应该可以工作。

安吉洛

- Angelo Immediata

---

但是如何使它变得动态呢？你的代码中硬编码了，我猜想Pageable会自动进行排序吗？ - Javatar

Pageable不会自动进行排序; 必须是查询来执行。如果您希望动态地进行排序，应将Order（或等效DTO）传递给findByCriteria方法的参数。 - Angelo Immediata

谢谢，我已经通过手动使用Pageable和getSort()进行排序找到了解决方案。 - Javatar

在Spring中，“name，desc”这个双值字符串意味着“按名称降序排序”。上面的代码尝试对名为“desc”的列进行排序，我预计会出现错误。 - chrisinmtown

@chrisinmtown 值 "name,desc" 实际上是要求 Spring 使用名称和描述对结果集进行排序。 - Javatar

Answer 4

一个完整的示例，使用JpaSpecificationExecutor并将其封装在一个类中：

首先，将存储库定义为JpaRepository和JpaSpecificationExecutor的扩展，用于SomeEntity：

interface SomeEntityRepositoryPaged extends JpaRepository<SomeEntity, Integer>, JpaSpecificationExecutor<SomeEntity> {
 Page<SomeEntity> findAll(Specification<SomeEntity> spec, Pageable pageable);

}

FindByCriteria 类：

class FindByCriteria{
     FindByCriteria() {
            ApplicationContext appCtx = ApplicationContextUtils.getApplicationContext();
            repository = appCtx.getBean(SomeEntityRepositoryPaged.class);
        }
    
        private SomeEntityRepositoryPaged repository;
        
        public Page<SomeEntity> searchBy(Client client, SearchParams params,Pageable page){
            return repository.findAll(getCriteria(params), page);
        }
        private Specification<SomeEntity> getCriteria(SearchParams params){
              return (paymentRoot, query, cb)-> 
              { 
                    List<Predicate> predicates = new ArrayList<>(5);
            
                    filterByParams(params, cb, paymentRoot, predicates);

                    query.where(predicates.toArray(new Predicate[] {}));

                    //IMPORTANT: the order specify should be deterministic, meaning rows can never change order in 2 subsequent calls.
                    
                    List<Order> orders = new ArrayList<>(1);
                    orders.add(cb.asc(paymentRoot.get("date")));
                    
                    query.orderBy(orders);
                    
                    return  null;
              };
            }

        private void filterByParams(SearchParams params, CriteriaBuilder cb, Root<SomeEntity> payment,
                List<Predicate> predicates) {
        
            if (params.getInitialDate() != null)
                predicates.add(cb.greaterThan(payment.get("paymentDate"), params.getInitialDate()));

            if (params.getFinalDate() != null)
                predicates.add(cb.lessThanOrEqualTo(payment.get("paymentDate"), params.getFinalDate()));

            if (params.getState() != null)
                predicates.add(cb.equal(payment.get("state"), params.getState()));
            //... add here any criteria needed bases on params object
        }
    }

}

调用简单的函数：

@Test pagedQueryTest
{
     var page = PageRequest.of(2, 10);
     var params = new SearchParams (...);
     var list = new FindByCriteria().searchBy(params, page);
     assert...
     //The generated native query should be optimized.
     //For mssql the native query should end with "offset ? rows fetch first ? rows only" 
     //and that means the store engine will only fetch the rows after first parameter (=page*size), and to the max of the page size.
}

- MiguelSlv

---

对我来说，在getCriteria方法中删除query.orderBy(orders);后也起作用了。排序是Pageable接口的一部分，会被识别出来。 - undefined

Answer 5

 CriteriaBuilder cb = em.getCriteriaBuilder();
 CriteriaQuery<User> cq = cb.createQuery(User.class);
 Root<User> iRoot = cq.from(User.class);
 List<Predicate> predicates = new ArrayList<Predicate>();

 if (StringUtils.isNotEmpty(username)) {
     predicates.add(cb.like(cb.lower(iRoot.<String>get("username")), "%" + 
     username.toLowerCase() + "%"));
 }


 Predicate[] predArray = new Predicate[predicates.size()];
 predicates.toArray(predArray);

 cq.where(predArray);

 cq.orderBy(cb.desc(user.get("name")));

 TypedQuery<User> query = em.createQuery(cq);
 Page<User> result = new PageImpl<User>(query.getResultList(), page, totalRows);

 return result;

- Abhishek Gupta