Spring Data y consulta nativa con paginación

En un proyecto web, que utiliza los datos de primavera más recientes (1.10.2) con una base de datos MySQL 5.6, estoy intentando usar una consulta nativa con paginación, pero estoy experimentando un problema org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodExceptional inicio.

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

Según 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, así:

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 NativeJpaQueryclase puedo ver que contiene el siguiente código para comprobar 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 Pageableparámetro, al igual hasPagingOrSortingParameterque true, pero también busca una secuencia #pageableo #sortdentro de queryString, que no proporciono.

Intenté agregar #pageable(es un comentario) al final de mi consulta, lo que hace que se apruebe la validación, 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 containsPageableOrSortInQueryExpressionde falsea truemientras ejecuto, la consulta funciona bien, así que no sé por qué está verificando que esa cadena esté en mi queryStringy 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 PR de Jens Schauder

Jul 13 '16 18:07 Lasneyx

Este es un truco para programas que usan Spring Data JPA antes de la versión 2.0.4 .

El código ha funcionado 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. countQueryes para Page<User>.

Dec 22 '2016 12:12 Dmitry Stolbov

Estoy agregando esta respuesta solo como un marcador de posición para aquellos usuarios que usan versiones más recientes de Spring Boot. En Spring Boot 2.4.3, observé que ninguna de las soluciones alternativas era necesaria y el siguiente código funcionó de inmediato para mí:

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

La countQuerydefinición no era necesaria y, Page#getTotalElements()de hecho, una llamada a ya devolvía el recuento correcto, según lo devuelto por la propia consulta de recuento interno de JPA.

El código anterior es extremadamente poderoso y ofrece paginación realizada a través de una consulta nativa, pero que devuelve resultados en entidades Java reales (en lugar de las feas y voluminosas List<Object[]>, que a veces son necesarias).

Jun 23 '2021 10:06 Tim Biegeleisen

Solo para que conste, utilizando 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 del 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).

Apr 13 '2017 10:04 maricn