Spring Data et Native Query avec pagination

Question

Dans un projet web, j'utilise la dernière version de spring-data (1.10.2) avec une base de données MySQL 5.6. J'essaie d'utiliser une requête native avec pagination mais je rencontre un problème d'affichage. org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException au démarrage.

UPDATE 20180306 Ce problème est maintenant corrigé en Printemps 2.0.4 Pour ceux qui sont toujours intéressés ou qui sont coincés avec des versions plus anciennes, consultez les réponses et les commentaires relatifs aux solutions de contournement.

Selon Exemple 50 à l'utilisation de @Query de la documentation de spring-data cela est possible en spécifiant la requête elle-même et un countQuery, comme ceci :

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);
}

Par curiosité, en NativeJpaQuery je peux voir qu'il contient le code suivant pour vérifier si c'est une requête jpa valide :

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);
   }
}

Ma requête contient un Pageable donc hasPagingOrSortingParameter es true mais il cherche aussi un #pageable o #sort dans la séquence queryString que je ne fournis pas.

J'ai essayé d'ajouter #pageable (c'est un commentaire) à la fin de ma requête, ce qui fait que la validation passe mais ensuite, elle échoue à l'exécution en disant que la requête attend un paramètre supplémentaire : 3 au lieu de 2.

Le truc marrant c'est que, si je change manuellement containsPageableOrSortInQueryExpression de false a true en cours d'exécution, la requête fonctionne bien, donc je ne sais pas pourquoi il vérifie que cette chaîne se trouve à ma position. queryString et je ne sais pas comment la fournir.

Toute aide serait très appréciée.

Mise à jour du 30/01/2018 Il semble que les développeurs du projet spring-data travaillent sur un correctif pour ce problème à l'aide d'un fichier PR par Jens Schauder

Answer 1

Je m'excuse d'avance, mais je résume à peu près la question initiale et le commentaire de l'auteur. Janar mais...

Je me heurte au même problème : j'ai trouvé les Exemple 50 de Spring Data comme la solution à mon besoin d'avoir une requête native avec pagination mais Spring se plaignait au démarrage que je ne pouvais pas utiliser la pagination avec les requêtes natives.

Je voulais juste signaler que j'ai réussi à exécuter avec succès la requête native dont j'avais besoin, en utilisant la pagination, avec le code suivant :

    @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);

La requête countQuery (qui est commentée dans le bloc de code) est nécessaire pour utiliser la fonction Page<Author> comme type de retour de la requête, les retours à la ligne autour du commentaire "#pageable" sont nécessaires pour éviter l'erreur d'exécution sur le nombre de paramètres attendus (solution de contournement de la solution de contournement). J'espère que ce bug sera bientôt corrigé...

Answer 2

Il s'agit d'un hack pour les programmes utilisant Spring Data JPA avant la version 2.0.4 .

Le code a fonctionné avec PostgreSQL et 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} est pour Pageable . countQuery est pour Page<User> .

Answer 3

Pour mémoire, en utilisant H2 comme base de données de test et MySQL au moment de l'exécution, cette approche fonctionne (l'exemple est le suivant l'objet le plus récent du groupe ):

@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 4

Essayez ça :

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

J'ai exactement le même symptôme que @Lasneyx. Ma solution de contournement pour la requête native 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);