How to get results of JPA custom query as Page

Question

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?

Have you tried [link](https://stackoverflow.com/questions/27193337/dynamic-queries-in-spring-data-jpa)? — ruba, Jul 24 '20 at 08:36
@ruba Thanks! It must work. But for some reasons native SQL is more preferable for me now, so I'm trying Kavithakaran way. — Sasha77ru, Jul 24 '20 at 12:31
If you enable the Spring data web support extensions and QueryDSL extension you'll got all this (i.e. sort, filter (by dynamic criteria) and page) without having to write any any code. https://stackoverflow.com/questions/59027999/spring-rest-api-multiple-requestparams-vs-controller-implementation/59028315#59028315 — Alan Hay, Jul 24 '20 at 13:53

score 1 · Accepted Answer · answered Jul 24 '20 at 08:51

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); 
                            
    }

ikos23 · Answer 2 · 2020-07-24T08:51:52.723

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.

It returns `List`. But FrontEnd wants not a pure List, but all paging information in json — Sasha77ru, Jul 24 '20 at 08:56

How to get results of JPA custom query as Page

2 Answers2