Spring Boot paging with custom SQL query method

Question

Spring Boot paging with custom SQL query method

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

Next, I have a Spring Boot application where I list some entities. I've searched how to use pagination, but all the examples I've found use the default method findAll() of the Repository, but I do not use this method in my application, I have a method of doing SQL queries on hand according to received parameters (they are filters on the front, name equal to something, age less than something, etc.), someone knows how to use pagination without using findAll( ) OR using findAll() but that can to apply filters in SQL?

spring-boot

asked by anonymous 09.07.2018 / 12:20

2 answers

Why can not I display the value in print? How do I make a face book plugin responsive?

score 0 · Answer 1

See an example using Query and PageRequest , with a dummy entity called Pessoa in which 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 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 notation by method name to create queries (without writing JPQL), you can just write:

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

If none of the solutions above meet, depending on the complexity of your query, you have to break into something more flexible by using the Query class and giving up Spring pagination and manipulating the String of the query according to the filters. You can even continue using PageRequest as a class to pass paging data (so you do not escape the pattern of your code), but its application in your method must 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();

score 0 · Answer 2

First you should check which pagination select 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 the 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 function above is not very clear, you can start the study by testing the pure select in the database.