英文:
How to get results of JPA custom query as Page
问题
我有一个仓库，返回一个 Page<Mind>：
public interface MindRepository extends PagingAndSortingRepository<Mind, Integer> {Page<Mind> findByCountry(String country, Pageable pageable);}
还有一个使用它的控制器：
private MindRepository mindRepository;@GetMapping(path = "/minds", produces = "application/json")public Page<Mind> getMinds(String country, Integer page, Integer size) {Pageable pageable = PageRequest.of(page, size);return mindRepository.findByCountry(country, pageable);}
一切都正常。控制器以适合前端的 JSON 格式返回 Page<Mind>。
但现在我必须使查询更复杂，带有多个动态更改的筛选器。我想像这样使用 createQuery：
public interface CustomizedMindRepository<T> {Page<T> findByCountry(String country, Pageable pageable);}public interface MindRepository extends PagingAndSortingRepository<Mind, Integer>, CustomizedMindRepository {Page<Mind> findByCountry(String country, Pageable pageable);}public class CustomizedMindRepositoryImpl implements CustomizedMindRepository {@PersistenceContextprivate EntityManager em;@Overridepublic Page<Mind> findByCountry(String country, Pageable pageable) {return em.createQuery("from minds where <动态筛选条件> AND <另一个动态筛选条件> AND <...等等>", Mind.class).getResultList();}}
但 getResultList() 返回的是 List，而不是 Page 
解决这个问题的最佳方法是什么呢？
英文:
I have a repository, that returns a Page<Mind>:
public interface MindRepository extends PagingAndSortingRepository<Mind, Integer> {Page<Mind> findByCountry(String country, Pageable pageable);}
And a controller that use it:
private MindRepository mindRepository;@GetMapping(path = "/minds", produces = "application/json")public Page<Mind> getMinds(String country, Integer page, Integer size) {Pageable pageable = PageRequest.of(page,size);return mindRepository.findByCountry(country,pageable);}
And everything is ok. Controller returns Page<Mind> in json that suits FrontEnd.
But now I have to make the query more complicated, with several filters, changing dynamically. I would like to use createQuery like this:
public interface CustomizedMindRepository<T> {Page<T> findByCountry(String country, Pageable pageable);}public interface MindRepository extends PagingAndSortingRepository<Mind, Integer>,CustomizedMindRepository {Page<Mind> findByCountry(String country, Pageable pageable);}public class CustomizedMindRepositoryImpl implements CustomizedMindRepository {@PersistenceContextprivate EntityManager em;@Overridepublic Page<Mind> findByCountry(String country, Pageable pageable) {return em.createQuery("from minds where <dynamical filter> AND <another dynamical filter> AND <...etc>", Mind.class).getResultList();}}
But getResultList() returns List, not Page
What is the best way to solve it?
答案1
得分: 1
@Overridepublic Page<Mind> findByCountry(String country, Pageable pageable) {long offset = pageable.getPageNumber() * pageable.getPageSize();long limit = pageable.getPageSize();List<Item> itemsInNextPage = em.createQuery(query).setFirstResult(offset).setMaxResults(limit).getResultList();long total = // 另一个查询以获取总数List<Mind> results = em.createQuery("from minds ...", Mind.class).getResultList();return new PageImpl(results, pageable, total);}
英文:
- Page repository call executes two calls, one to get the results and another to get the total size. So you have to replicate that behaviour by doing a count query as well
@Overridepublic Page<Mind> findByCountry(String country, Pageable pageable) {long offset = pageable.getPageNumber() * pageable.getPageSize();long limit = pageable.getPageSize();List<Item> itemsInNextPage = em.createQuery(query).setFirstResult(offset).setMaxResults(limit).getResultList();long total = // Another query to get the total countList<Mind> results = em.createQuery("from minds ...", Mind.class).getResultList();return new PageImpl(results, pageable, total);}
答案2
得分: 0
如果您想使用EntityManager.createQuery，您可以使用setFirstResult和setMaxResults方法来实现相同的结果。
@Overridepublic List<Mind> findByCountry(String country, Pageable pageable) {return em.createQuery("from minds where <动态筛选条件> AND <另一个动态筛选条件> AND <...等等>", Mind.class).setFirstResult(startPosition).setMaxResults(size).getResultList();}
在这种情况下，size 的含义与您的情况相同，但是startPosition 不是一个“页”，而是通过以下方式计算：
startPosition = page * size
但是，如果您需要构建动态查询，请考虑使用Specifications或者JPA Criteria API。
英文:
If you want to use EntityManager.createQuery, you are given setFirstResult and setMaxResults methods to achieve the same results.
@Overridepublic List<Mind> findByCountry(String country, Pageable pageable) {return em.createQuery("from minds where <dynamical filter> AND <another dynamical filter> AND <...etc>", Mind.class).setFirstResult(startPosition).setMaxResults(size).getResultList();}
In this case size have the same meaning as in your case, but startPosition is not a page, but is calculated as:
startPosition = page * size
But, if you need to build dymanic query - consider using Specifications or JPA Criteria API.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论