如何将JPA自定义查询的结果获取为Page

huangapple go评论101阅读模式
英文:

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 如何将JPA自定义查询的结果获取为Page

解决这个问题的最佳方法是什么呢?

英文:

I have a repository, that returns a Page&lt;Mind&gt;:

public interface MindRepository extends PagingAndSortingRepository&lt;Mind, Integer&gt; {
    Page&lt;Mind&gt; findByCountry(String country, Pageable pageable);
}

And a controller that use it:

private MindRepository mindRepository;

@GetMapping(path = &quot;/minds&quot;, produces = &quot;application/json&quot;)
public Page&lt;Mind&gt; 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&lt;Mind&gt; 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&lt;T&gt; {
    Page&lt;T&gt; findByCountry(String country, Pageable pageable);
}

public interface MindRepository extends PagingAndSortingRepository&lt;Mind, Integer&gt;,CustomizedMindRepository {
    Page&lt;Mind&gt; findByCountry(String country, Pageable pageable);
}

public class CustomizedMindRepositoryImpl implements CustomizedMindRepository {
    @PersistenceContext
    private EntityManager em;

    @Override
    public Page&lt;Mind&gt; findByCountry(String country, Pageable pageable) {
        return em.createQuery(&quot;from minds where &lt;dynamical filter&gt; AND &lt;another dynamical filter&gt; AND &lt;...etc&gt;&quot;, Mind.class)
                .getResultList();
    }
}

But getResultList() returns List, not Page 如何将JPA自定义查询的结果获取为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&lt;Mind&gt; findByCountry(String country, Pageable pageable) {
        long offset = pageable.getPageNumber() * pageable.getPageSize();
        long limit = pageable.getPageSize();
        
        List&lt;Item&gt; itemsInNextPage = em.createQuery(query)
                .setFirstResult(offset)
                .setMaxResults(limit)
                .getResultList();
        
        long total = // Another query to get the total count
                
        List&lt;Mind&gt;  results = em.createQuery(&quot;from minds ...&quot;, Mind.class)
                             .getResultList();
        return new PageImpl(results, pageable, total); 
                            
    }

答案2

得分: 0

如果您想使用EntityManager.createQuery,您可以使用setFirstResultsetMaxResults方法来实现相同的结果。

@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&lt;Mind&gt; findByCountry(String country, Pageable pageable) {
  return em.createQuery(&quot;from minds where &lt;dynamical filter&gt; AND &lt;another dynamical filter&gt; AND &lt;...etc&gt;&quot;, 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.

huangapple
  • 本文由 发表于 2020年7月24日 16:25:44
  • 转载请务必保留本文链接:https://go.coder-hub.com/63069770.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定