Spring Boot paging with custom SQL query method

Question

Next, I have a Spring Boot application where I do a listing of some entities. I researched how to use paging, but all the examples I found use the default method findAll() Repository, only that I don’t use this method in my application, I have a method of my own where I do SQL queries in hand according to received parameters (these are filters in the front, name equals something, age less than something, etc. ), someone knows how to use paging without using findAll() OR using findAll() but can apply filters to SQL?

Answer 1

See an example using Query and PageRequest, with a fictional entity called Pessoa where I use JPQL:

public interface PessoaCrudRepository extends CrudRepository<Pessoa, Long> {
    @Query(value = "SELECT p FROM Pessoa p WHERE p.nome = ?1")
    Page<Pessoa> buscarPessoa(String nomePessoa, Pageable pageable);
}

And to call and to use:

int numeroPagina = 0;
int numeroResultados = 10;
PageRequest pageRequest = new PageRequest(numeroPagina, numeroResultados,
            Sort.Direction.ASC, "nome");

Page<Pessoa> pagePessoa = repository.buscarPessoa("Jonas", pageRequest);
List<Pessoa> pessoas = pagePessoa.getContent();

If you want to use method name notation to create queries (without writing JPQL), you can write only:

public interface PessoaCrudRepository extends CrudRepository<Pessoa, Long> {
    Page<Pessoa> findByNome(String nomePessoa, Pageable pageable);
}

If none of the above solutions meet, depending on the complexity of your query, you have to go for something more flexible using the class Query and giving up Spring paging and manipulating the String of the query according to the filters. You can even continue using the PageRequest as a class to pass paging data (not to deviate from the default of your code), but its application in your method should be explicit in Query. Example:

String jpql = "SELECT p FROM Pessoa p WHERE p.nome = :nome";
Query query = entityManager.createQuery(jpql); 
query.setParameter("nome", "Jonas");

query.setFirstResult(pageRequest.getPageNumber() * pageRequest.getPageSize());
query.setMaxResults(pageRequest.getPageSize());

List<Pessoa> pessoas = query.getResultList();

Answer 2

First you should check which select for paging your bank uses.

Oracle:

 select * from (
        select  p.id,p.name, rownum as rn
        from Pessoa p
            where rownum <= #{pageNumber} + #{pageSize}
            and p.name like ('%Test')
    ) where rn > #{pageNumber}

Postgres:

SELECT p.id, p.name,
FROM p.name like ('%Test')
LIMIT #{pageSize}
OFFSET(#{pageNumber} - 1) * #{pageSize}

In spring boot we can use as follows :

   import org.springframework.data.domain.Page;
   import org.springframework.data.domain.PageImpl;
   import org.springframework.data.domain.Pageable;

   public Page<Pessoa> listPessoaByName(String name, Pageable pageable) {
    Map<String, Object> map = new HashMap<>();
    map.put("name", name);
    map.put("pageSize", pageable.getPageSize());
map.put("pageNumber",pageable.getPageSize()*pageable.getPageNumber());

    //lista as Pessoas de acordo com o select acima.
    // O map representa os parametros que o select ira receber.
    List<Pessoa> listPessoa =  selectList("searchPessoas", map);
    //count com o total de Pessoa que possuem o name
    int total = totalOfPessoaByName(pessoaFilter);
    return new PageImpl<>(listPessoa, pageable,total);
}

If the above function is not very clear, you can start the study by testing the pure select in the database.