Java – why does spring nativequery with paging throw an sqlgrammarexception?
•
Java
I tried to use spring 4.2 Run the following query in 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);
This code follows the example shown here: https://github.com/spring-projects/spring-data-jpa/blob/master/src/test/java/org/springframework/data/jpa/repository/sample/UserRepository.java#L539
This causes 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]
This is by:
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:?]...
I have tried to run the same native query without paging, and it will run without any errors The query is as follows:
@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();
Is this the limitation of spring data or what I missed here?
Solution
I think this is a mistake in the spring data If the query contains order by, i.itemcategory ASC. Is appended to the spring data In another case, the spring data adds order by i.itemcategory ASC As a workaround, you can add order by 1 = 1 to the query
@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",nativeQuery = true) Page<Item> getItems(Pageable pageable);
The content of this article comes from the network collection of netizens. It is used as a learning reference. The copyright belongs to the original author.
THE END
二维码