I'm using Hibernate 5.2.10.Final with MSSQL server.
I have a complicated query which works correct, until I'm adding result items limitation. After that I see in log query, which is produced and exception. When I try to execute produced query directly on MSSQL it works correct.
I see next error:
{quote}
13:37:01,708 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-1) SQL Error: 0, SQLState: S1093
13:37:01,710 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-1) The index 2 is out of range.
{quote}
Exception looks strange bacause in my query I have only one parameter.
Here is stack trace of the exception:
{code:java}
15:02:45,194 ERROR [org.quartz.core.JobRunShell] (org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-1) Job DEFAULT.navigationLogEndDateJod threw an unhandled Exception: : org.springframework.scheduling.quartz.JobMethodInvocationFailedException: Invocation of method 'populateEndTimeNavigationLogs' on target class [class test.NavigationLogEndDateJob] failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query
at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:266)
at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:75)
at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:147)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1423)
at org.hibernate.query.Query.getResultList(Query.java:146)
at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:72)
at test.LogDaoImpl.getSessionLogIdsForNullEndDate(LogDaoImpl.java:41)
at test.LogService.getSessionLogIdsForNullEndDate(LogService.java:21)
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.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at com.sun.proxy.$Proxy70.getSessionLogIdsForNullEndDate(Unknown Source)
at test.NavigationLogEndDateJob.getProcessSessionLogIds(NavigationLogEndDateJob.java:69)
at test.NavigationLogEndDateJob.populateEndTimeNavigationLogs(NavigationLogEndDateJob.java:52)
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.util.MethodInvoker.invoke(MethodInvoker.java:269)
at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:257)
... 3 more
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.loader.Loader.doList(Loader.java:2618)
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:379)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1488)
at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1445)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414)
... 22 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 2 is out of range.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:714)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setValue(SQLServerPreparedStatement.java:723)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setInt(SQLServerPreparedStatement.java:884)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.setInt(WrappedPreparedStatement.java:224)
at org.hibernate.dialect.pagination.AbstractLimitHandler.bindLimitParameters(AbstractLimitHandler.java:156)
at org.hibernate.dialect.pagination.AbstractLimitHandler.bindLimitParametersAtEndOfQuery(AbstractLimitHandler.java:128)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1958)
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)
... 31 more
15:02:45,201 ERROR [org.quartz.core.ErrorLogger] (org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-1) Job (DEFAULT.navigationLogEndDateJod threw an exception.: org.quartz.SchedulerException: Job threw an unhandled exception. [See nested exception: org.springframework.scheduling.quartz.JobMethodInvocationFailedException: Invocation of method 'populateEndTimeNavigationLogs' on target class [class test.NavigationLogEndDateJob] failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query]
at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: org.springframework.scheduling.quartz.JobMethodInvocationFailedException: Invocation of method 'populateEndTimeNavigationLogs' on target class [class test.NavigationLogEndDateJob] failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query
at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:266)
at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:75)
at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
... 1 more
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:147)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1423)
at org.hibernate.query.Query.getResultList(Query.java:146)
at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:72)
at test.LogDaoImpl.getSessionLogIdsForNullEndDate(LogDaoImpl.java:41)
at test.service.impl.LogService.getSessionLogIdsForNullEndDate(LogService.java:21)
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.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at com.sun.proxy.$Proxy70.getSessionLogIdsForNullEndDate(Unknown Source)
at test.NavigationLogEndDateJob.getProcessSessionLogIds(NavigationLogEndDateJob.java:69)
at test.NavigationLogEndDateJob.populateEndTimeNavigationLogs(NavigationLogEndDateJob.java:52)
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.util.MethodInvoker.invoke(MethodInvoker.java:269)
at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:257)
... 3 more
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.loader.Loader.doList(Loader.java:2618)
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:379)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1488)
at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1445)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414)
... 22 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 2 is out of range.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:714)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setValue(SQLServerPreparedStatement.java:723)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setInt(SQLServerPreparedStatement.java:884)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.setInt(WrappedPreparedStatement.java:224)
at org.hibernate.dialect.pagination.AbstractLimitHandler.bindLimitParameters(AbstractLimitHandler.java:156)
at org.hibernate.dialect.pagination.AbstractLimitHandler.bindLimitParametersAtEndOfQuery(AbstractLimitHandler.java:128)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1958)
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)
... 31 more
{code}
Here is produced SQL:
{code:sql}
Hibernate:
select
distinct TOP 2 sessionlog1_.session_log_id as col_0_0_,
sessionlog1_.session_start_date as col_1_0_
from
navigation_log navigation0_
left outer join
session_log sessionlog1_
on navigation0_.session_log_id=sessionlog1_.session_log_id
where
(
navigation0_.navigation_end_date is null
)
and sessionlog1_.session_end_date<=?
and (
sessionlog1_.session_log_id not in (
select
jobnavigat2_.session_log_id
from
job_navigation_log jobnavigat2_
)
)
order by
sessionlog1_.session_start_date asc
{code}
And here is my Java code:
{code:java}
EntityManager entityManager = sessionFactory.createEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery criteria = criteriaBuilder.createQuery(Tuple.class);
Root<NavigationLog> root = criteria.from(NavigationLog.class);
Join<NavigationLog, SessionLog> sessionLogJoin = root.join(SESSION_LOG, JoinType.LEFT);
criteria = criteria.multiselect(sessionLogJoin.get(ID), sessionLogJoin.get(SESSION_START_DATE));
Subquery<JobNavigationLog> subQuery = criteria.subquery(JobNavigationLog.class);
Root<JobNavigationLog> subQueryRoot = subQuery.from(JobNavigationLog.class);
subQuery = subQuery.select(subQueryRoot.get(SESSION_LOG_ID));
criteria.where(criteriaBuilder.and(
criteriaBuilder.isNull(root.get(NAVIGATION_END_DATE)),
criteriaBuilder.lessThanOrEqualTo(sessionLogJoin.get(SESSION_END_DATE), queryLimitDate),
criteriaBuilder.not(criteriaBuilder.in(sessionLogJoin.get(ID)).value(subQuery))));
criteria.distinct(Boolean.TRUE);
criteria.orderBy(criteriaBuilder.asc(sessionLogJoin.get(SESSION_START_DATE)));
List<Tuple> tuples = entityManager.createQuery(criteria).setMaxResults( 5 2 ).getResultList();
{code}
|
|
