Spring Data i Native Query z paginacją

W projekcie internetowym, używając najnowszych danych wiosennych (1.10.2) z bazą danych MySQL 5.6, próbuję użyć natywnego zapytania z paginacją, ale doświadczam org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodExceptionproblemu podczas uruchamiania.

AKTUALIZACJA : 20180306 Ten problem został już rozwiązany w wersji Spring 2.0.4. Osoby, które nadal są zainteresowane lub mają problemy ze starszymi wersjami, powinny sprawdzić powiązane odpowiedzi i komentarze dotyczące obejścia.

Zgodnie z przykładem 50 przy użyciu @Query z dokumentacji danych wiosennych możliwe jest określenie samego zapytania i countQuery, na przykład:

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

Z ciekawości, w NativeJpaQueryklasie widzę, że zawiera następujący kod do sprawdzenia, czy jest to prawidłowe zapytanie jpa:

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

Moje zapytanie zawiera Pageableparametr, więc hasPagingOrSortingParameterjest true, ale szuka również sekwencji #pageablelub #sortwewnątrz elementu queryString, czego nie dostarczam.

Próbowałem dodać #pageable(to komentarz) na końcu mojego zapytania, co powoduje, że walidacja jest zakończona, ale kończy się niepowodzeniem podczas wykonywania, mówiąc, że zapytanie oczekuje jednego dodatkowego parametru: 3 zamiast 2.

Zabawne jest to, że jeśli ręcznie zmienię containsPageableOrSortInQueryExpressionz falsena truepodczas pracy, zapytanie działa dobrze, więc nie wiem, dlaczego sprawdza, czy ten ciąg znajduje się na moim, queryStringi nie wiem, jak go podać.

Każda pomoc byłaby bardzo mile widziana.

Aktualizacja 30.01.2018 Wygląda na to, że programiści w projekcie Spring-Data pracują nad rozwiązaniem tego problemu za pomocą PR Jensa Schaudera

Z góry przepraszam, to właściwie podsumowuje oryginalne pytanie i komentarz Janara , jednak ...

Napotykam ten sam problem: znalazłem przykład 50 Spring Data jako rozwiązanie dla mojej potrzeby posiadania natywnego zapytania z paginacją, ale Spring narzekał podczas uruchamiania, że ​​nie mogę używać paginacji z natywnymi zapytaniami.

Chciałem tylko zgłosić, że udało mi się pomyślnie uruchomić natywne zapytanie, którego potrzebowałem, używając paginacji, z następującym kodem:

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

CountQuery (które jest zakomentowane w bloku kodu) jest potrzebne do użycia Page<Author> jako zwracanego typu zapytania, nowe linie wokół komentarza „#pageable” są potrzebne, aby uniknąć błędu czasu wykonania dotyczącego liczby oczekiwanych parametrów (obejście obejście). Mam nadzieję, że ten błąd zostanie wkrótce naprawiony ...

To jest hack do programu używającego Spring Data JPA przed wersją 2.0.4 .

Kod współpracował z PostgreSQL i 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}jest dla Pageable. countQueryjest dla Page<User>.

Dla przypomnienia, używając H2 jako testowej bazy danych i MySQL w czasie wykonywania, takie podejście działa (przykład to najnowszy obiekt w grupie ):

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

Mam dokładnie taki sam symptom jak @Lasneyx. Moje obejście dla zapytania natywnego 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);

Spróbuj tego:

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

( "/* */"dla Oracle notation)

Używam bazy danych Oracle i nie otrzymałem wyniku, ale błąd z wygenerowanym przecinkiem, o którym d-man mówi powyżej.

Wtedy moje rozwiązanie brzmiało:

Pageable pageable = new PageRequest(current, rowCount);

Jak widać bez zamawiania po utworzeniu Pagable.

I metoda w 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);
 }

Oba poniższe podejścia działają dobrze z MySQL w przypadku podziału na strony zapytań natywnych. Jednak nie działają z H2. Będzie narzekać na błąd składni sql.

• ZAMÓWIENIE PRZEZ? # {# Pageable}
• ZAMÓWIENIE PRZEZ a.id \ n # pageable \ n

Użycie „ORDER BY id DESC \ n-- #pageable \ n” zamiast „ORDER BY id \ n # pageable \ n” działało dla mnie z MS SQL SERVER

Mogłem z powodzeniem zintegrować Pagination w

wiosna-data-jpa-2.1.6

następująco.

@Query(
 value = “SELECT * FROM Users”, 
 countQuery = “SELECT count(*) FROM Users”, 
 nativeQuery = true)
Page<User> findAllUsersWithPagination(Pageable pageable);

Działa jak poniżej:

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

U mnie poniżej pracowałem w MS SQL

 @Query(value="SELECT * FROM ABC r where r.type in :type  ORDER BY RAND() \n-- #pageable\n ",nativeQuery = true)
List<ABC> findByBinUseFAndRgtnType(@Param("type") List<Byte>type,Pageable pageable);

Używam poniższego kodu. pracujący

@Query(value = "select * from user usr" +
  "left join apl apl on usr.user_id = apl.id" +
  "left join lang on lang.role_id = usr.role_id" +
  "where apl.scr_name like %:scrname% and apl.uname like %:uname and usr.role_id in :roleIds ORDER BY ?#{#pageable}",
  countQuery = "select count(*) from user usr" +
      "left join apl apl on usr.user_id = apl.id" +
      "left join lang on lang.role_id = usr.role_id" +
      "where apl.scr_name like %:scrname% and apl.uname like %:uname and usr.role_id in :roleIds",
  nativeQuery = true)
Page<AplUserEntity> searchUser(@Param("scrname") String scrname,@Param("uname") String  uname,@Param("roleIds") List<Long> roleIds,Pageable pageable);

Usunięcie \ n # pageable \ nz zapytania i liczby zapytań zadziałało. Wersja Springboot: 2.1.5.RELEASE DB: MySQL

To zadziałało dla mnie (używam Postgres) w Groovy:

@RestResource(path="namespaceAndNameAndRawStateContainsMostRecentVersion", rel="namespaceAndNameAndRawStateContainsMostRecentVersion")
    @Query(nativeQuery=true,
            countQuery="""
            SELECT COUNT(1) 
            FROM 
            (
                SELECT
                ROW_NUMBER() OVER (
                    PARTITION BY name, provider_id, state
                    ORDER BY version DESC) version_partition,
                *
                FROM mydb.mytable
                WHERE
                (name ILIKE ('%' || :name || '%') OR (:name = '')) AND
                (namespace ILIKE ('%' || :namespace || '%') OR (:namespace = '')) AND
                (state = :state OR (:state = ''))
            ) t
            WHERE version_partition = 1
            """,
            value="""
            SELECT id, version, state, name, internal_name, namespace, provider_id, config, create_date, update_date 
            FROM 
            (
                SELECT 
                ROW_NUMBER() OVER (
                    PARTITION BY name, provider_id, state
                    ORDER BY version DESC) version_partition,
                *
                FROM mydb.mytable
                WHERE 
                (name ILIKE ('%' || :name || '%') OR (:name = '')) AND
                (namespace ILIKE ('%' || :namespace || '%') OR (:namespace = '')) AND
                (state = :state OR (:state = ''))       
            ) t            
            WHERE version_partition = 1             
            /*#{#pageable}*/
            """)
    public Page<Entity> findByNamespaceContainsAndNameContainsAndRawStateContainsMostRecentVersion(@Param("namespace")String namespace, @Param("name")String name, @Param("state")String state, Pageable pageable)

Kluczem było tutaj użycie: /*#{#pageable}*/

Pozwala mi na sortowanie i paginację. Możesz to przetestować, używając czegoś takiego: http: // localhost: 8080 / api / v1 / entity / search / namespaceAndNameAndRawStateContainsMostRecentVersion? Namespace = & name = & state = Published & page = 0 & size = 3 & sort = name, desc

Uważaj na ten problem: Spring Pageable nie tłumaczy nazwy @Column

Możesz użyć poniższego kodu dla h2 i MySQl

    @Query(value = "SELECT req.CREATED_AT createdAt, req.CREATED_BY createdBy,req.APP_ID appId,req.NOTE_ID noteId,req.MODEL model FROM SUMBITED_REQUESTS  req inner join NOTE note where req.NOTE_ID=note.ID and note.CREATED_BY= :userId "
        ,
         countQuery = "SELECT count(*) FROM SUMBITED_REQUESTS req inner join NOTE note WHERE req.NOTE_ID=note.ID and note.CREATED_BY=:userId",
        nativeQuery = true)
Page<UserRequestsDataMapper> getAllRequestForCreator(@Param("userId") String userId,Pageable pageable);

Zastąpienie / #pageable / z? # {# Pageable} pozwala na paginację. Dodanie PageableDefault pozwala ustawić rozmiar elementów strony.