How do I use Page in this query instead of List?

Question

@Query(value="SELECT * FROM prot_protocolo where status = 'PENDENTE' AND departamento_id=?", nativeQuery=true)
    public List<Protocolo> findByProtocolosPorDepartamento(String id);

The page does not accept nativeQuery, error when running the query, I tried so:

public Page<Protocolo> findByProtocolosPorDepartamento(Pageable pageable, String id);

Answer 1

The first point is that the pagination information in the method signature should be the last argument. In your case, just have something like this:

Page<Protocolo> findByProtocolosPorDepartamento(String id, Pageable pageable);

However, as described above here, cannot still use native queries or Sorting dynamics and spring data "generate the rest":

Note, that we Currently don’t support Execution of Dynamic Sorting for Activate queries as we’d have to Manipulate the current query declared and we cannot do this reliably for Native SQL

In this case you should implement the Count part, it would be something like this:

@Query(value = "SELECT * FROM prot_protocolo where status = 'PENDENTE' AND departamento_id = :id",
    countQuery = "SELECT count(*) FROM prot_protocolo where status = 'PENDENTE' AND departamento_id = :id",
    nativeQuery = true)
Page<Protocolo> findByProtocolosPorDepartamento(String id, Pageable pageable);

Perhaps the "best" solution would be for you to provide a method, using Property Expressions same, which will cause spring data to generate the JPQL needed for your query, something like this:

Page<Protocolo> findByStatusAndDepartamento(final String status, final Departamento departamento, final Pageable pageable);

One last alternative, if paging is not "solving" with native queries is for you to implement the paging part on your own using Spel support in spring data, something like this example, but can break the independence of databases.