java - Spring-Data-JPA throws "Caused by: java.sql.SQLException: Incorrect syntax near ' limit'."by using findAll

Question

我在谷歌上搜索了 2 天，但找不到适合我的问题的答案:-)。

我正在尝试使用 Spring Data JPA Repositories findAll() 函数来使用分页。

我在版本“1.7.1.RELEASE”中使用 Spring-Data-JPA。后端 SQL 是一个 MS-SQL 2008 服务器，我使用的是版本“1.3.1”的 JTDS。

我已经创建了一个 JPA 实体并使用例如 findOne 方法查询数据库工作正常并给了我预期的答案但是当我使用 findAll(Pageable arg0)抛出以下堆栈跟踪的方法:

        2015-01-26 12:23:49,527 DEBUG | could not extract ResultSet [n/a] 
java.sql.SQLException: Incorrect syntax near 'limit'.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372) ~[jtds-1.3.1.jar:1.3.1]
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988) ~[jtds-1.3.1.jar:1.3.1]
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421) ~[jtds-1.3.1.jar:1.3.1]
    at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671) ~[jtds-1.3.1.jar:1.3.1]
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:505) ~[jtds-1.3.1.jar:1.3.1]
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:1029) ~[jtds-1.3.1.jar:1.3.1]
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) ~[c3p0-0.9.1.2.jar:0.9.1.2]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2066) [hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863) [hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839) [hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:910) [hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355) [hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2554) [hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2540) [hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370) [hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2365) [hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:497) [hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387) [hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:236) [hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1264) [hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103) [hibernate-core-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573) [hibernate-entitymanager-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449) [hibernate-entitymanager-4.3.8.Final.jar:4.3.8.Final]
    at org.hibernate.jpa.criteria.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:67) [hibernate-entitymanager-4.3.8.Final.jar:4.3.8.Final]
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.readPage(SimpleJpaRepository.java:459) [spring-data-jpa-1.7.1.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:357) [spring-data-jpa-1.7.1.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:326) [spring-data-jpa-1.7.1.RELEASE.jar:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_75]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_75]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_75]
    at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_75]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:442) [spring-data-commons-1.9.1.RELEASE.jar:na]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:427) [spring-data-commons-1.9.1.RELEASE.jar:na]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:381) [spring-data-commons-1.9.1.RELEASE.jar:na]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.1.4.RELEASE.jar:4.1.4.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98) [spring-tx-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262) [spring-tx-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) [spring-tx-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.1.4.RELEASE.jar:4.1.4.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) [spring-tx-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.1.4.RELEASE.jar:4.1.4.RELEASE]
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:122) [spring-data-jpa-1.7.1.RELEASE.jar:na]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.1.4.RELEASE.jar:4.1.4.RELEASE]
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) [spring-aop-4.1.4.RELEASE.jar:4.1.4.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.1.4.RELEASE.jar:4.1.4.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) [spring-aop-4.1.4.RELEASE.jar:4.1.4.RELEASE]
    at com.sun.proxy.$Proxy57.findAll(Unknown Source) [na:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_75]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_75]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_75]
    at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_75]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) [spring-aop-4.1.4.RELEASE.jar:4.1.4.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) [spring-aop-4.1.4.RELEASE.jar:4.1.4.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) [spring-aop-4.1.4.RELEASE.jar:4.1.4.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98) [spring-tx-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262) [spring-tx-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) [spring-tx-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.1.4.RELEASE.jar:4.1.4.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) [spring-aop-4.1.4.RELEASE.jar:4.1.4.RELEASE]
    at com.sun.proxy.$Proxy58.findAll(Unknown Source) [na:na]
    at com.X.Y.Z.main(GenericAccountCompare_Analyse.java:51) [classes/:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_75]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_75]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_75]
    at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_75]
    at org.codehaus.mojo.exec.ExecJavaMojo$1.run(ExecJavaMojo.java:293) [exec-maven-plugin-1.3.2.jar:na]
    at java.lang.Thread.run(Thread.java:745) [na:1.7.0_75]
2015-01-26 12:23:49,528  WARN | SQL Error: 102, SQLState: 42000 
2015-01-26 12:23:49,528 ERROR | Incorrect syntax near 'limit'. 
2015-01-26 12:23:49,529 DEBUG | Mark transaction for rollback 
2015-01-26 12:23:49,531 DEBUG | rolling back 
2015-01-26 12:23:49,559 DEBUG | rolled JDBC Connection 
2015-01-26 12:23:49,559 DEBUG | re-enabling autocommit 
2015-01-26 12:23:49,588 DEBUG | Releasing JDBC connection 
2015-01-26 12:23:49,588 DEBUG | Released JDBC connection

我的 VendorAdapterConfig 看起来像

<bean id="jpaVendor"
    class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
    <property name="showSql" value="true" />
    <property name="generateDdl" value="false" />
    <property name="databasePlatform" value="org.hibernate.dialect.SQLServer2008Dialect" />
    <property name="database" value="SQL_SERVER" />
</bean>

这是使用 findAll 方法的代码块(堆栈跟踪中所述的第 51 行):

Page<Workrelationship> wrPage = wrRepo.findAll(new PageRequest(0, 20));

知道我做错了什么吗？

谢谢和亲切的问候

壳牌

PS:我也用MS-JDBC驱动测试过，同样报错

[更新]

这是生成的查询:

select workrelati0_.a as aR1_9_, workrelati0_.b as b2_9_, workrelati0_.c as c6_9_, workrelati0_.d as d7_9_, workrelati0_.e as e3_9_, workrelati0_.f as f4_9_, workrelati0_.g as gO5_9_ from tblWorkRelationship workrelati0_ limit ?

如果我直接使用 MS-SQL Studio 执行该查询，我会收到同样的错误 “limit”附近的语法不正确。

我没有 persistence.xml 或任何其他 hibernate 配置。所有这些都是使用 Spring-Data Configbeans 完成的:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:jpa="http://www.springframework.org/schema/data/jpa"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
        http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd">

    <context:property-placeholder location="classpath:application.properties" />

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
        destroy-method="close">
        <property name="driverClass" value="${jdbc.driver}" />
        <property name="jdbcUrl" value="${jdbc.url}" />
        <property name="user" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
        <property name="acquireIncrement" value="${jdbc.c3p0.pool.acquireIncrement}" />
        <property name="minPoolSize" value="${jdbc.c3p0.pool.minPoolSize}" />
        <property name="maxPoolSize" value="${jdbc.c3p0.pool.maxPoolSize}" />
    </bean>

    <jpa:repositories base-package="com.X.Y.Z.repository"
        entity-manager-factory-ref="myEmf" 
        transaction-manager-ref="transactionManager" />

    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="myEmf" />
        <property name="persistenceUnitName" value="base" />
        <qualifier value="my-db" />
    </bean>

    <bean id="myEmf"
        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="jpaVendorAdapter" ref="jpaVendor" />
        <property name="packagesToScan" value="com.X.Y.Z.domain" />
        <property name="persistenceUnitName" value="base" />
    </bean>

    <bean id="jpaVendor"
        class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
        <property name="showSql" value="true" />
        <property name="generateDdl" value="false" />
        <property name="databasePlatform" value="org.hibernate.dialect.SQLServer2008Dialect" />
    </bean>

    <tx:annotation-driven transaction-manager="transactionManager"/>
</beans>

[/更新]

[更新2]

我使用 supportsLimit-Function 测试了方言，结果出乎意料。如上所述，我使用的是 MS-SQL 2008 服务器，从 MS-SQL 2012 开始支持限制函数。

SQLServerDialect、SQLServer2005Dialect 和 SQLServer2008Dialect 通过调用 supportLimit 返回 true。

SQLServerDialect dialect = new SQLServerDialect();
log.info("Support limit: " + dialect.supportsLimit());

SQLServer2005Dialect dialect2005 = new SQLServer2005Dialect();
log.info("Support limit: " + dialect2005.supportsLimit());

SQLServer2008Dialect dialect2008 = new SQLServer2008Dialect();
log.info("Support limit: " + dialect2008.supportsLimit());

2015-01-26 13:52:38,594  INFO | HHH000400: Using dialect: org.hibernate.dialect.SQLServerDialect 
2015-01-26 13:52:38,596  INFO | Support limit: true 
2015-01-26 13:52:38,597  INFO | HHH000400: Using dialect: org.hibernate.dialect.SQLServer2005Dialect 
2015-01-26 13:52:38,597  INFO | Support limit: true 
2015-01-26 13:52:38,598  INFO | HHH000400: Using dialect: org.hibernate.dialect.SQLServer2008Dialect 
2015-01-26 13:52:38,599  INFO | Support limit: true

[/Update2]

[更新3]

这是包含 getLimitString 的输出:

2015-01-26 14:25:14,815  INFO | HHH000400: Using dialect: org.hibernate.dialect.SQLServerDialect 
2015-01-26 14:25:14,817  INFO | Support limit: true 
2015-01-26 14:25:14,817  INFO | Limit-String: select top 20 * from A 
2015-01-26 14:25:14,817  INFO | HHH000400: Using dialect: org.hibernate.dialect.SQLServer2005Dialect 
2015-01-26 14:25:14,818  INFO | Support limit: true 
2015-01-26 14:25:14,818  INFO | Limit-String: select top 20 * from A 
2015-01-26 14:25:14,818  INFO | HHH000400: Using dialect: org.hibernate.dialect.SQLServer2008Dialect 
2015-01-26 14:25:14,819  INFO | Support limit: true 
2015-01-26 14:25:14,820  INFO | Limit-String: select top 20 * from A

对我来说是正确的:-)。

[/Update3]

Answer 1

我也使用了 2 个实体管理器，但我已将一个 (MySQL) 的数据库方言复制并粘贴到另一个 (SQLServer)，但遇到了同样的问题。这篇文章很棒，因为我检查了我的数据库方言，果然它是不正确的。纠正后，它起作用了!

properties.setProperty("hibernate.dialect", "org.hibernate.dialect.SQLServer2012Dialect");