Spring Data e Native Query con impaginazione

In un progetto web, utilizzando l’ultima spring-dati (1.10.2) con un database MySQL 5.6, sto provando a utilizzare una query nativa con impaginazione, ma sto vivendo un org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException all’avvio.

AGGIORNAMENTO : 20180306 Questo problema è stato risolto nella spring 2.0.4. Per coloro che sono ancora interessati o bloccati con versioni precedenti, controllare le relative risposte e i commenti per soluzioni alternative.

Secondo l’ esempio 50 di Using @Query dalla documentazione di spring-data, è ansible specificare la query stessa e un countQuery, come questo:

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

Per curiosità, nella class NativeJpaQuery posso vedere che contiene il seguente codice per verificare se si tratta di una query jpa valida:

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

La mia query contiene un parametro Pageable , quindi hasPagingOrSortingParameter è true , ma sta anche cercando una sequenza #pageable o #sort all’interno di queryString , che non fornisco.

Ho provato ad aggiungere #pageable (è un commento) alla fine della mia query, che rende valida la convalida, ma poi fallisce durante l’esecuzione dicendo che la query si aspetta un parametro aggiuntivo: 3 invece di 2.

La cosa divertente è che, se cambio manualmente containsPageableOrSortInQueryExpression da false a true mentre è in esecuzione, la query funziona correttamente, quindi non so perché sta verificando che quella stringa sia sul mio queryString e non so come fornirla.

Qualsiasi aiuto sarebbe molto apprezzato.

Aggiornamento 01/30/2018 Sembra che gli sviluppatori del progetto Spring Data stiano lavorando a una soluzione per questo problema con un PR di Jens Schauder

Le mie scuse in anticipo, questo è praticamente riassumendo la domanda originale e il commento di Janar , tuttavia …

Mi imbatto nello stesso problema: ho trovato l’ esempio 50 di Spring Data come la soluzione per il mio bisogno di avere una query nativa con paginazione, ma Spring si lamentava all’avvio che non potevo usare la paginazione con le query native.

Volevo solo segnalare che sono riuscito a eseguire correttamente la query nativa di cui avevo bisogno, usando la paginazione, con il seguente codice:

  @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 findAuthorsUpdatedAndNew(Pageable pageable); 

Il countQuery (che è commentato nel blocco di codice) è necessario per utilizzare Page come tipo di ritorno della query, le nuove righe attorno al commento “#pageable” sono necessarie per evitare l’errore di runtime sul numero di parametri previsti (soluzione alternativa della soluzione alternativa). Spero che questo bug verrà risolto presto …

Solo per la cronaca, utilizzando H2 come database di test e MySQL in fase di esecuzione, questo approccio funziona (l’esempio è il più recente object nel gruppo ):

 @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 findByUserIdInGroupByObjectId(@Param("user_ids") Set userIds, Pageable pageable); 

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

Questo codice ha funzionato con PostgreSQL e MySQL:

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

ORDER BY ?#{#pageable} è per Pageable . countQuery è per Page<> .

Ho lo stesso identico sintomo di @Lasneyx. La mia soluzione alternativa per la query nativa di Postgres

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

Prova questo:

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

( "/* */" per la Oracle notation )

Entrambi i seguenti approcci funzionano bene con MySQL per la paginazione della query nativa. Tuttavia non funzionano con H2. Si lamenterà l’errore di syntax sql.

  • ORDINA DA? # {# Pageable}
  • ORDINA da a.id \ n # pageable \ n

L’utilizzo di “ORDER BY id DESC \ n– #pageable \ n” invece di “ORDER BY id \ n # pageable \ n” ha funzionato con MS SQL SERVER

Io uso il database di Oracle e non ho ottenuto il risultato, ma un errore con la virgola generata che d-man parla di sopra.

Quindi la mia soluzione era:

 Pageable pageable = new PageRequest(current, rowCount); 

Come puoi vedere senza ordine quando crea Pagable.

E il metodo nel DAO:

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

Funziona come di seguito:

 public interface UserRepository extends JpaRepository { @Query(value = "select * from (select (@rowid\\:[email protected]+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 findByLastname(String lastname, Pageable pageable); } 

@EmanueleFusco

TU SEI UN DIO!

Ho battuto la testa per circa 8 ore e non sono riuscito a capire cosa fosse.

Stavo ricevendo questo errore alla riga 4 vicino a WHERE

MySQLSyntaxErrorException: hai un errore nella syntax SQL; controlla il manuale che corrisponde alla tua versione del server MySQL per la syntax corretta da usare vicino a “DOVE”) FROM building_institutions DOVE building_institutions.building_id = 1) A ‘alla riga 2

quando la mia query era simile a questa:

 @Query( value = "SELECT username, password, description, location, title, user_id FROM (institution INNER JOIN user ON institution.user_id = user.id) LEFT JOIN\n" + "(SELECT * FROM building_institutions WHERE building_institutions.building_id = 1) AS reserved_institutions\n" + "ON reserved_institutions.institutions_user_id = kits_nwt.institution.user_id\n" + "WHERE reserved_institutions.institutions_user_id IS null ORDER BY ?#{#pageable}", nativeQuery = true) Page findPotentialInstitutionsByBuildingId(Long userId, Pageable pageable); 

Ma l’errore era presente perché non ho aggiunto il parametro @Query all’annotazione @Query .

Controlla la mia domanda SO che ho chiesto per una query SQL LEFT JOIN. Lì ho risposto alla mia domanda e ho scritto il codice che funziona per la mia query complessa.

Saluti 🙂

La sostituzione di / #pageable / with? # {# Pageable} consente di effettuare l’impaginazione. L’aggiunta di PageableDefault consente di impostare la dimensione degli elementi della pagina.