SQLServerException：执行时，索引1超出范围Spring Data findAll（Pageable） [英] SQLServerException: The index 1 is out of range when executiong Spring Data findAll(Pageable)

问题描述

当一个实体的Spring Data findAll（Pageable）方法被调用时，下面的查询由Hibernate执行：

 选择TOP（？）customer0_.id as id1_0_，customer0_.name as name2_0_ from customer customer0_ 
  

之后执行以下查询：

  Hibernate：select customer0_ .id as id1_0_，customer0_.name as customer2_0_ from customer customer0_ order by customer0_.id asc offset 0 rows fetch next 20 rows only 
  

引发了一个execption

  ohengine.jdbc.spi.SqlExceptionHelper：无法执行查询[select customer0_ .id as id1_0_，customer0_.name as customer2_0_ from customer customer0_ order by customer0_.id asc] 
 
 com.microsoft.sqlserver.jdbc.SQLServerException：索引1超出范围。 
 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError（SQLServerException.java:206）
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam（SQLServerPreparedStatement.java:940）
在com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setValue（SQLServerPreparedStatement.java:954）
在com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setInt（SQLServerPreparedStatement.java:1249）
在com.zaxxer .hikari.pool.HikariProxyPreparedStatement.setInt（HikariProxyPreparedStatement.java）
 at org.hibernate.dialect.pagination.SQLServer2005LimitHandler.bindLimitParametersAtStartOfQuery（SQLServer2005LimitHandler.java:132）
 at org.hibernate.loader.Loader.prepareQueryStatement （Loader.java:1950）
在org.hibernate.loader.Loader.executeQueryStatement（Loader.java:1909）
在org.hibernate.loader.Loader.executeQueryStatement（Loader.java:1887）
在org.hibernate.loader.Loader.doQuery（Loader.java:932）
在org.hibernate.loader.Loa der.doQueryAndInitializeNonLazyCollections（Loader.java:349）
在org.hibernate.loader.Loader.doList（Loader.java:2615）
在org.hibernate.loader.Loader.doList（Loader.java： 2598）
 at org.hibernate.loader.Loader.listIgnoreQueryCache（Loader.java:2430）
 at org.hibernate.loader.Loader.list（Loader.java:2425）
 at org.hibernate.loader.Loader.listIgnoreQueryCache .hibernate.loader.hql.QueryLoader.list（QueryLoader.java:502）
在org.hibernate.hql.internal.ast.QueryTranslatorImpl.list（QueryTranslatorImpl.java:370）
在org.hibernate .engine.query.spi.HQLQueryPlan.performList（HQLQueryPlan.java:216）
 at org.hibernate.internal.SessionImpl.list（SessionImpl.java:1481）
 at org.hibernate.query.internal .AbstractProducedQuery.doList（AbstractProducedQuery.java:1441）
 org.hibernate.query.internal.AbstractProducedQuery.list（AbstractProducedQuery.java:1410）
 at org.hibernate.query.Query.getResultList（Query .java：146）
在org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList（Cr iteriaQueryTypeQueryAdapter.java:72）
 at org.springframework.data.jpa.repository.support.SimpleJpaRepository.readPage（SimpleJpaRepository.java:589）
 at org.springframework.data.jpa.repository.support。 SimpleJpaRepository.findAll（SimpleJpaRepository.java:409）
在org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll（SimpleJpaRepository.java:377）
在sun.reflect.NativeMethodAccessorImpl.invoke0（本地方法）
在sun.reflect.NativeMethodAccessorImpl.invoke（NativeMethodAccessorImpl.java:62）
处的java.lang sun.reflect.DelegatingMethodAccessorImpl.invoke（DelegatingMethodAccessorImpl.java:43）
。 reflect.Method.invoke（Method.java:498）维持在org.springframework org.springframework.data.repository.core.support.RepositoryFactorySupport $ QueryExecutorMethodInterceptor.executeMethodOn（RepositoryFactorySupport.java:504）
 
。 data.repository.core.support.RepositoryFactorySupport $ QueryExecutorMethodInterceptor.doInvoke（RepositoryFactorySupport.java ：489）
在org.springframework.data.repository.core.support.RepositoryFactorySupport $ QueryExecutorMethodInterceptor.invoke（RepositoryFactorySupport.java:461）
在org.springframework.aop.framework.ReflectiveMethodInvocation.proceed（ReflectiveMethodInvocation的.java：179）
处org.springframework.aop.framework.ReflectiveMethodInvocation.proceed org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke（DefaultMethodInvokingMethodInterceptor.java:61）
（ReflectiveMethodInvocation.java:179 ）
在org.springframework.transaction.interceptor.TransactionInterceptor $ 1.proceedWithInvocation（TransactionInterceptor.java:99）
在org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction（TransactionAspectSupport.java:282）
 at org.springframework.transaction.interceptor.TransactionInterceptor.invoke（TransactionInterceptor.java:96）
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed（ReflectiveMetho dInvocation.java:179）在org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke（PersistenceExceptionTranslationInterceptor.java:136 
）
在org.springframework.aop.framework.ReflectiveMethodInvocation.proceed（ReflectiveMethodInvocation.java： 
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed（ReflectiveMethodInvocation。）
 at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor $ CrudMethodMetadataPopulatingMethodInterceptor.invoke（CrudMethodMetadataPostProcessor.java:133）
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed（ReflectiveMethodInvocation。 Java的：179），美元，org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke（ExposeInvocationInterceptor.java:92 b $ b）
在org.springframework.aop.framework.ReflectiveMethodInvocation.proceed（ReflectiveMethodInvocation.java:179） 
 at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke（SurroundingTransactionDetectorMethodInterceptor.java:57）
 at org.springframewo rk.aop.framework.ReflectiveMethodInvocation.proceed（ReflectiveMethodInvocation.java:179）
处com.sun.proxy org.springframework.aop.framework.JdkDynamicAopProxy.invoke（JdkDynamicAopProxy.java:213）
。 $ Proxy170.findAll（未知来源）
 at sa.tvtc.farm.web.rest.CustomerResource.getAllCustomers（CustomerResource.java:107）
 at sa.tvtc.farm.web.rest.CustomerResource $ $ FastClassBySpringCGLIB $$ 48eccfb1.invoke（< generated>）
 at org.springframework.cglib.proxy.MethodProxy.invoke（MethodProxy.java:204）
 at org.springframework.aop.framework.CglibAopProxy $ CglibMethodInvocation.invokeJoinpoint（CglibAopProxy.java:721）
在org.springframework.aop.framework.ReflectiveMethodInvocation.proceed（ReflectiveMethodInvocation.java:157）
在com.ryantenney.metrics.spring.TimedMethodInterceptor.invoke （TimedMethodInterceptor.java:48）
 at com.ryantenney.metrics.spring.TimedMethodInterceptor.invoke（TimedMethodInterceptor.java:34）
 at com.ryantenney.metrics.spring.AbstractMe tricMethodInterceptor.invoke（AbstractMetricMethodInterceptor.java:59）
处org.springframework.aop.framework.CglibAopProxy $ DynamicAdvisedInterceptor org.springframework.aop.framework.ReflectiveMethodInvocation.proceed（ReflectiveMethodInvocation.java:179）
。截取（CglibAopProxy.java:656）
 at sa.tvtc.farm.web.rest.CustomerResource $$ EnhancerBySpringCGLIB $$ 9b6861c0.getAllCustomers（< generated>）
  

配置：

application-dev.yml

  spring：
数据源：
类型：com.zaxxer.hikari.HikariDataSource 
 ... 
 jpa：
 database-platform：org.hibernate.dialect.SQLServer2012Dialect 
 database：SQL_SERVER 
  

pom。 xml

 < hibernate.version> 5.2.8.Final< /hibernate.version> 
< hikaricp.version> 2.6.0< /hikaricp.version> 
< mssql-jdbc.version> 6.1.0.jre8< /mssql-jdbc.version> 
< liquibase-hibernate5.version> 3.6< /liquibase-hibernate5.version> 
 
< dependency> 
< groupId> com.microsoft.sqlserver< / groupId> 
< artifactId> mssql-jdbc< / artifactId> 
< version> $ {mssql-jdbc.version}< / version> 
< /依赖关系> 
< dependency> 
< groupId> com.github.sabomichal< / groupId> 
< artifactId> liquibase-mssql< / artifactId> 
< version> $ {liquibase-mssql.version}< / version> 
< /依赖关系> 
  

解决方案

使用以前版本的Hibernate修复了这个问题

 < hibernate.version> 5.2.7.Final< /hibernate.version> 
  

When the Spring Data findAll(Pageable) method of an entity is called, the following query is executed by Hibernate:

select TOP(?) customer0_.id as id1_0_, customer0_.name as name2_0_ from customer customer0_

After that the following query is executed:

Hibernate: select customer0_.id as id1_0_, customer0_.name as name2_0_ from customer customer0_ order by customer0_.id asc offset 0 rows fetch next 20 rows only

An execption is raised

o.h.engine.jdbc.spi.SqlExceptionHelper   : could not execute query [select customer0_.id as id1_0_, customer0_.name as name2_0_ from customer customer0_ order by customer0_.id asc]

com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:206)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:940)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setValue(SQLServerPreparedStatement.java:954)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setInt(SQLServerPreparedStatement.java:1249)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setInt(HikariProxyPreparedStatement.java)
at org.hibernate.dialect.pagination.SQLServer2005LimitHandler.bindLimitParametersAtStartOfQuery(SQLServer2005LimitHandler.java:132)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1950)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1909)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1887)
at org.hibernate.loader.Loader.doQuery(Loader.java:932)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
at org.hibernate.loader.Loader.doList(Loader.java:2615)
at org.hibernate.loader.Loader.doList(Loader.java:2598)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2430)
at org.hibernate.loader.Loader.list(Loader.java:2425)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:370)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1481)
at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1441)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1410)
at org.hibernate.query.Query.getResultList(Query.java:146)
at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:72)
at org.springframework.data.jpa.repository.support.SimpleJpaRepository.readPage(SimpleJpaRepository.java:589)
at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:409)
at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:377)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:504)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:489)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:461)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy170.findAll(Unknown Source)
at sa.tvtc.farm.web.rest.CustomerResource.getAllCustomers(CustomerResource.java:107)
at sa.tvtc.farm.web.rest.CustomerResource$$FastClassBySpringCGLIB$$48eccfb1.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:721)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at com.ryantenney.metrics.spring.TimedMethodInterceptor.invoke(TimedMethodInterceptor.java:48)
at com.ryantenney.metrics.spring.TimedMethodInterceptor.invoke(TimedMethodInterceptor.java:34)
at com.ryantenney.metrics.spring.AbstractMetricMethodInterceptor.invoke(AbstractMetricMethodInterceptor.java:59)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:656)
at sa.tvtc.farm.web.rest.CustomerResource$$EnhancerBySpringCGLIB$$9b6861c0.getAllCustomers(<generated>)

Configuration:

application-dev.yml

spring:
datasource:
    type: com.zaxxer.hikari.HikariDataSource
 ...
jpa:
    database-platform: org.hibernate.dialect.SQLServer2012Dialect
    database: SQL_SERVER

pom.xml

<hibernate.version>5.2.8.Final</hibernate.version>
<hikaricp.version>2.6.0</hikaricp.version>
<mssql-jdbc.version>6.1.0.jre8</mssql-jdbc.version>
<liquibase-hibernate5.version>3.6</liquibase-hibernate5.version>

    <dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>mssql-jdbc</artifactId>
        <version>${mssql-jdbc.version}</version>
    </dependency>
    <dependency>
        <groupId>com.github.sabomichal</groupId>
        <artifactId>liquibase-mssql</artifactId>
        <version>${liquibase-mssql.version}</version>
    </dependency>

解决方案

The use of previous version of Hibernate fixed the problem

<hibernate.version>5.2.7.Final</hibernate.version>

这篇关于SQLServerException：执行时，索引1超出范围Spring Data findAll（Pageable）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！