英文:
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 {
@PersistenceContext
private EntityManager em;
@Override
public 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 {
@PersistenceContext
private EntityManager em;
@Override
public 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
@Override
public 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
@Override
public 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 count
List<Mind> results = em.createQuery("from minds ...", Mind.class)
.getResultList();
return new PageImpl(results, pageable, total);
}
答案2
得分: 0
如果您想使用EntityManager.createQuery
,您可以使用setFirstResult
和setMaxResults
方法来实现相同的结果。
@Override
public 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.
@Override
public 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论