Почему spring nativeQuery с нумерацией страниц выдает исключение SQLGrammarException?

Я пытаюсь запустить следующий запрос в Spring 4.2.4:

@Query(value="SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY i.TYPE_NUMBER ASC) AS RN, i.* FROM Item i) AS g WHERE RN between ?#{ #pageable.offset} and ?#{#pageable.offset + #pageable.pageSize}",
        countQuery="SELECT count(i.ID) FROM Item i",
    nativeQuery = true)
Page<Item> getItems(Pageable pageable);

Этот код следует приведенному здесь примеру: https://github.com/spring-projects/spring-data-jpa/blob/master/src/test/java/org/springframework/data/jpa/repository/sample/UserRepository.java#L539

Это приводит к исключению SQLGrammarException:

Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2066) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:910) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2554) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2540) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2365) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1909) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573) ~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449) ~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:191) ~[spring-data-jpa-1.9.4.RELEASE.jar:?]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:78) ~[spring-data-jpa-1.9.4.RELEASE.jar:?]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:100) ~[spring-data-jpa-1.9.4.RELEASE.jar:?]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:91) ~[spring-data-jpa-1.9.4.RELEASE.jar:?]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:462) ~[spring-data-commons-1.11.4.RELEASE.jar:?]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:440) ~[spring-data-commons-1.11.4.RELEASE.jar:?]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61) ~[spring-data-commons-1.11.4.RELEASE.jar:?]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) ~[spring-tx-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281) ~[spring-tx-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ~[spring-tx-4.2.6.RELEASE.jar:4.2.6.RELEASE]

Это caused автор:

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ','.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) ~[sqljdbc4.jar:?]
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) ~[sqljdbc4.jar:?]...

Я попытался запустить тот же собственный запрос без разбивки на страницы, и он будет работать без ошибок. Запрос выглядит так:

  @Query(value="SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY i.TYPE_NUMBER ASC) AS RN, i.* FROM Item i) AS g WHERE RN between 0 and 50",
        nativeQuery = true)
    List<Item> getItems();

Это ограничение весенних данных или я что-то здесь упускаю?


java spring hibernate spring-data nativequery
person Community    schedule 25.11.2016    source источник
comment
Есть ли cause в этом SQLGrammarException?   -  person Joe C    schedule 26.11.2016
comment
Да, вот причина: Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ','. Это происходит только при запуске с использованием объекта Pageable.   -  person    schedule 26.11.2016
comment
Включите журнал sql, чтобы увидеть выполненный sql /   -  person Daniel Käfer    schedule 26.11.2016
comment
@DanielKäfer Спасибо. Я включил отладку SQL в режиме гибернации и обнаружил, что проблема связана с динамической сортировкой объекта Pageable: PageRequest pageRequest = new PageRequest(0, 25, Sort.Direction.ASC, "itemCategory"); С параметрами динамической сортировки объекта, доступного для страниц, Hibernate генерирует SELECT top 25 * FROM (SELECT ROW_NUMBER() OVER(ORDER BY i.TYPE_NUMBER ASC) AS RN, i.* FROM Item i) AS g WHERE RN between ? and ?, i.itemCategory asc Ошибка здесь довольно очевидна. Знаете ли вы, как использовать параметры сортировки pageable с собственным запросом?   -  person    schedule 01.12.2016

Ответы (1)


arrow_upward
1
arrow_downward

Я думаю, что это ошибка в весенних данных. Данные Spring добавляют , i.itemCategory asc, если запрос содержит ORDER BY. В другом случае весенние данные добавляют ORDER BY i.itemCategory asc. В качестве обходного пути вы можете добавить ORDER BY 1=1 к запросу.

@Query(value="SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY i.TYPE_NUMBER ASC) AS RN, i.* FROM Item i) AS g WHERE RN between ?#{ #pageable.offset} and ?#{#pageable.offset + #pageable.pageSize}, ORDER BY 1=1",
        countQuery="SELECT count(i.ID) FROM Item i",
    nativeQuery = true)
Page<Item> getItems(Pageable pageable);
person Daniel Käfer    schedule 03.12.2016