After upgrade hibernate-core to version 3.3.2, some namedQuerys fails due to "Missing IN or OUT parameter at index:: 2".
Aparently, this error is related to setting filter parameter in the subselect generated by "inElements" in the HQL query.
Below follows an example:
1) Mapping for entity "Customer", with filter definition used to restrict customers by id:
Code:
<class name="com.test.Customer">
<id column="ID_CSTMR" name="identifier" type="long">
<generator class="native">
<param name="sequence">SEQ_BAS_CSTMR</param>
</generator>
</id>
<property length="60" name="name"/>
<set name="banks" table="BAS_BANK_CSTMR_ASSN">
<key column="ID_CSTMR"/>
<many-to-many class="com.test.Bank" column="ID_BANK"/>
</set>
</class>
<filter-def name="customerFilter">
<filter-param name="customerId" type="long"/>
</filter-def>
3) Mapping for entity "Bank", with the "custumerFilter" attached to the collection "customers":
Code:
<class name="com.test.Bank">
<id column="ID_BANK" name="identifier" type="long">
<generator class="native">
<param name="sequence">SEQ_BAS_BANK</param>
</generator>
</id>
<property length="60" name="name"/>
<bag name="customers" table="BAS_BANK_CSTMR_ASSN">
<key column="ID_BANK"/>
<many-to-many class="com.test.Customer" column="ID_CSTMR"/>
<filter name="customerFilter" condition=":customerId = ID_CSTMR"/>
</bag>
</class>
3) Named query which returns a bank list for a specific customer:
Code:
<query name="findBankByCustomer">
from Bank entity
where :customer in elements (entity.customers)
</query>
4) Enabling filter in hibernate current session and executing the namedQuery:
Code:
protected void process(Session session, Long customerId){
// Enable the customer filter in the hibernate current session excluding the customer with id 1000
session.enableFilter("customerFilter").setParameter("customerId",
Long.valueOf(1000));
Customer customer = (Customer) session.load(Customer.class, customerId);
List banks = session.getNamedQuery("findBankByCustomer").setParameter(
"customer", customer).list(); // Fails
...
5) Exception after query.list():
Code:
java.sql.SQLException: Missing IN or OUT parameter at index:: 2
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:120)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:137)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:250)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:330)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:355)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1852)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3874)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3959)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:4515)
at net.sf.log4jdbc.PreparedStatementSpy.executeQuery(PreparedStatementSpy.java:719)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1812)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2232)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
at org.hibernate.loader.Loader.list(Loader.java:2124)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1149)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
6) Generated SQL Query:
Code:
select bank0_.ID_BANK as ID1_0_, bank0_.NAME as NAME0_
from BAS_BANK bank0_
where 90 in (select customers1_.ID_CSTMR
from BAS_BANK_CSTMR_ASSN customers1_
where bank0_.ID_BANK=customers1_.ID_BANK
and ? = customers1_.ID_CSTMR)
Note that filter parameter in the subquery has not been set.
Somebody can help?
Login
FAQ
Search