example - spring data pageable custom query

Spring Data y Native Query con paginación

Question

En un proyecto web, usando los últimos datos de primavera (1.10.2) con una base de datos MySQL 5.6, estoy tratando de usar una consulta nativa con paginación pero estoy experimentando un org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException al inicio.

ACTUALIZACIÓN : 20180306 Este problema ahora se corrigió en Spring 2.0.4. Para aquellos que todavía están interesados ​​o atascados con versiones anteriores, verifique las respuestas y comentarios relacionados para obtener soluciones.

De acuerdo con el Ejemplo 50 en Uso de @Query de la documentación de datos de primavera, esto es posible especificando la consulta en sí y un countQuery, como este:

public interface UserRepository extends JpaRepository<User, Long> {
  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
}

Por curiosidad, en la clase NativeJpaQuery puedo ver que contiene el siguiente código para verificar si es una consulta jpa válida:

public NativeJpaQuery(JpaQueryMethod method, EntityManager em, String queryString, EvaluationContextProvider evaluationContextProvider, SpelExpressionParser parser) {
   super(method, em, queryString, evaluationContextProvider, parser);
   JpaParameters parameters = method.getParameters();
   boolean hasPagingOrSortingParameter = parameters.hasPageableParameter() || parameters.hasSortParameter();
   boolean containsPageableOrSortInQueryExpression = queryString.contains("#pageable") || queryString.contains("#sort");
   if(hasPagingOrSortingParameter && !containsPageableOrSortInQueryExpression) {
       throw new InvalidJpaQueryMethodException("Cannot use native queries with dynamic sorting and/or pagination in method " + method);
   }
}

Mi consulta contiene un parámetro Pageable , por lo que hasPagingOrSortingParameter es true , pero también está buscando una secuencia #pageable o #sort dentro de queryString , que no proporciono.

Intenté agregar #pageable (es un comentario) al final de mi consulta, lo que hace que la validación pase, pero luego falla en la ejecución y dice que la consulta espera un parámetro adicional: 3 en lugar de 2.

Lo curioso es que, si cambio manualmente containsPageableOrSortInQueryExpression de false a true mientras se ejecuta, la consulta funciona bien, así que no sé por qué está buscando esa cadena en mi queryString y no sé cómo proporcionarla.

Cualquier ayuda sería muy apreciada.

Actualización 30/01/2018 Parece que los desarrolladores del proyecto spring-data están trabajando en una solución para este problema con un RP de Jens Schauder

Answer 1

Ambos enfoques funcionan bien con MySQL para paginar consultas nativas. Sin embargo, no funcionan con H2. Se quejará del error de sintaxis sql.

• ORDER BY? # {# Pageable}
• ORDER BY a.id / n # pageable / n

Answer 2

Este código funciona con PostgreSQL y MySQL:

public interface UserRepository extends JpaRepository<User, Long> {
  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1 ORDER BY ?#{#pageable}",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
}

ORDER BY ?#{#pageable} es para Pageable . countQuery es para la Page<> .

Answer 3

Mis disculpas de antemano, sin embargo, esto resume la pregunta original y el comentario de Janar ...

Me encuentro con el mismo problema: encontré el Ejemplo 50 de Spring Data como la solución para mi necesidad de tener una consulta nativa con paginación, pero Spring se quejaba en el inicio de que no podía usar la paginación con consultas nativas.

Solo quería informar que logré ejecutar con éxito la consulta nativa que necesitaba, usando paginación, con el siguiente código:

    @Query(value="SELECT a.* "
            + "FROM author a left outer join mappable_natural_person p on a.id = p.provenance_id "
            + "WHERE p.update_time is null OR (p.provenance_name=''biblio_db'' and a.update_time>p.update_time)"
            + "ORDER BY a.id /n#pageable/n", 
        /*countQuery="SELECT count(a.*) "
            + "FROM author a left outer join mappable_natural_person p on a.id = p.provenance_id "
            + "WHERE p.update_time is null OR (p.provenance_name=''biblio_db'' and a.update_time>p.update_time) /n#pageable/n",*/
        nativeQuery=true)
public List<Author> findAuthorsUpdatedAndNew(Pageable pageable);

El countQuery (que se comenta en el bloque de código) es necesario para usar la Page<Author> como el tipo de retorno de la consulta, las líneas nuevas alrededor del comentario "#pageable" son necesarias para evitar el error de tiempo de ejecución en la cantidad de parámetros esperados (solución de la solución). Espero que este error se solucione pronto ...

Answer 4

Prueba esto:

public interface UserRepository extends JpaRepository<User, Long> {
  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1 ORDER BY /*#pageable*/",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
}

( "/* */" para Oracle notation )

Answer 5

Reemplazar / #pageable / con? # {# Pageable} permite hacer paginación. Agregar PageableDefault le permite establecer el tamaño de los elementos de la página.

Answer 6

Solo para el registro, usando H2 como base de datos de prueba y MySQL en tiempo de ejecución, este enfoque funciona (el ejemplo es el objeto más nuevo en el grupo ):

@Query(value = "SELECT t.* FROM t LEFT JOIN t AS t_newer " +
        "ON t.object_id = t_newer.object_id AND t.id < t_newer.id AND o_newer.user_id IN (:user_ids) " +
        "WHERE t_newer.id IS NULL AND t.user_id IN (:user_ids) " +
        "ORDER BY t.id DESC /n-- #pageable/n",
        countQuery = "SELECT COUNT(1) FROM t WHERE t.user_id IN (:user_ids) GROUP BY t.object_id, t.user_id",
        nativeQuery = true)
Page<T> findByUserIdInGroupByObjectId(@Param("user_ids") Set<Integer> userIds, Pageable pageable);

Spring Data JPA 1.10.5, H2 1.4.194, MySQL Community Server 5.7.11-log (innodb_version 5.7.11).

Answer 7

Tengo exactamente el mismo síntoma como @Lasneyx. Mi solución para la consulta nativa de Postgres

@Query(value = "select * from users where user_type in (:userTypes) and user_context=''abc''--#pageable/n", nativeQuery = true)
List<User> getUsersByTypes(@Param("userTypes") List<String> userTypes, Pageable pageable);

Answer 8

Usar "ORDER BY id DESC / n-- #pageable / n" en lugar de "ORDER BY id / n # pageable / n" funcionó para mí con MS SQL SERVER

Answer 9

Utilizo la base de datos Oracle y no obtuve el resultado, sino un error con la coma generada de la que d-man habla anteriormente.

Entonces mi solución fue:

Pageable pageable = new PageRequest(current, rowCount);

Como puede ver sin orden al crear Pagable.

Y el método en el DAO:

public interface UserRepository extends JpaRepository<User, Long> {
  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1 /*#pageable*/ ORDER BY LASTNAME",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
 }

Answer 10

Funciona de la siguiente manera:

public interface UserRepository extends JpaRepository<User, Long> {
    @Query(value = "select * from (select (@rowid//:=@rowid+1) as RN, u.* from USERS u, (SELECT @rowid//:=0) as init where  LASTNAME = ?1) as total"+
        "where RN between ?#{#pageable.offset-1} and ?#{#pageable.offset + #pageable.pageSize}",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
    Page<User> findByLastname(String lastname, Pageable pageable);
}