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

Question

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

Navigation

#1 by (2 votes)

2

@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, of the error when executing the query, I tried like this:

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

java spring-data spring-mvc

asked by anonymous 24.10.2016 / 15:45

1 answer

java.io.Console when debugging in Eclipse How to execute a code only after finishing another?

score 2 · Accepted Answer

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 here , you can not still use native queries or sorting and the spring data "generate the rest":

Note, that we currently do not support execution of dynamic sorting for native queries as we'd have to manipulate the actual query declared and we can not do this reliably for native SQL

In this case you should implement the part of count, it would look 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);

Maybe the "best" solution would be for you to provide a method, using property expressions itself, 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, you implement the paging part on your own using Spel support on spring data, something like this example , but can break the independence of databases.