java – 为什么带有分页的spring nativeQuery抛出一个SQLGrammarException？
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了java – 为什么带有分页的spring nativeQuery抛出一个SQLGrammarException？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6510字，纯文字阅读大概需要10分钟。
内容图文
我试图在Spring 4.2.4中运行以下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}",
countQuery="SELECT count(i.ID) FROM Item i",
nativeQuery = true)
Page<Item> getItems(Pageable pageable);
这会导致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]
这是由：
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();
这是春季数据的限制还是我在这里遗漏了什么？
解决方法:
我认为这是春季数据中的一个错误.如果查询包含ORDER BY,则Spring数据追加i.itemCategory asc.在另一种情况下,spring数据添加了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);
内容总结
以上是互联网集市为您收集整理的java – 为什么带有分页的spring nativeQuery抛出一个SQLGrammarException？全部内容，希望文章能够帮你解决java – 为什么带有分页的spring nativeQuery抛出一个SQLGrammarException？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】