CODEWITHSUNDEEP
javaspringjpaormpagination
Sasha77ru
Sasha77ru

Reputation: 33

How to get results of JPA custom query as 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?

Upvotes: 2

Views: 1360

Answers (2)

ikos23
ikos23

Reputation: 5354

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.

Upvotes: 0

Kavithakaran Kanapathippillai
Kavithakaran Kanapathippillai

Reputation: 8203

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

Upvotes: 2

Related Questions