After changing the fetching strategy for an entity from SELECT to SUBSELECT, some of my queries result in an
SqlException: Parameter index out of range (4 > number of parameters, which is 3).
After studying the queries generated, I believe that the problem might be related to the use of a bind variable in the ORDER BY clause. The problem does not occur when I remove the ORDER BY clause.
MAIN QUERYCode:
select jpaorder0_.id as id1_0_0_, etc...
from `order` jpaorder0_ inner join checkout jpacheckou1_ on jpaorder0_.checkout_id=jpacheckou1_.id
where jpaorder0_.business_id=2 and (jpaorder0_.verified is not null) and jpaorder0_.state=? and (jpaorder0_.completed between ? and ?)
order by case when jpaorder0_.state=? then jpaorder0_.completed else jpaorder0_.verified end desc limit ?
TRACE org.hibernate.type.EnumType - Binding [COMPLETED] to parameter: [1]
TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [2] as [TIMESTAMP] - [Tue Jul 02 17:52:06 EDT 2013]
TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [3] as [TIMESTAMP] - [Thu Jul 03 17:52:06 EDT 2014]
TRACE org.hibernate.type.EnumType - Binding [COMPLETED] to parameter: [4]
SUBSELECT QUERYCode:
select refunds0_.order_id as order_id9_0_1_, etc...
from order_refund refunds0_
where refunds0_.order_id in (
select jpaorder0_.id
from `order` jpaorder0_ inner join checkout jpacheckou1_ on jpaorder0_.checkout_id=jpacheckou1_.id
where jpaorder0_.business_id=2 and (jpaorder0_.verified is not null) and jpaorder0_.state=? and (jpaorder0_.completed between ? and ?) )
TRACE org.hibernate.type.EnumType - Binding [COMPLETED] to parameter: [1]
TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [2] as [TIMESTAMP] - [Tue Jul 02 17:52:06 EDT 2013]
TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [3] as [TIMESTAMP] - [Thu Jul 03 17:52:06 EDT 2014]
TRACE org.hibernate.type.EnumType - Binding [COMPLETED] to parameter: [4]
Code Generating Order By ClauseCode:
CriteriaBuilder cb, CriteriaQuery cq;
Case<Date> orderByCase = cb.<Date> selectCase();
SingularAttribute<JpaOrder, Date> dateField = getDateField(entry.getValue());
orderByCase.when(cb.equal(orderRoot.get(JpaOrder_.state), state), orderRoot.get(dateField));
cq.orderBy(cb.asc(orderByCase));
Is there a way to work around this issue?On a semi-related note, if you look at the query, it contains "jpaorder0_.business_id=2". Why is it not a bind variable? The value is a BigInteger and is passed in like so:
Code:
criteriaBuilder.equal(root.get(JpaOrder_.businessId), businessId)
Is it possible to somehow hard-code the bind variable in the ORDER BY clause to fix the parameter out of range issue?
I've only posted code that I think is relevant. Please let me know if you would like me to provide the full Java code.
Login
FAQ
Search