Is it possible to pass a collection into a named query where the parameter bound to that collection is an argument to "elements" and used in an IN clause?

thanks

Hibernate version:
2.1.4

Mapping documents:
<query name="productGraphics">
select foo
from FooImpl as foo
where foo.bar in elements(:bars)
</query>


Code between sessionFactory.openSession() and session.close():
Bar bar1 = getBar1();
Bar bar2 = getBar2();
Collection bars = new ArrayList();
bars.add(bar1);
bars.add(bar2);
Query query = session.getNamedQuery("getFooBars");
query.setParameter("bars", bars);
Collection foos = query.list();

Full stack trace of any exception that occurs:
DEBUG 2004-08-16 15:29:37,131 net.sf.hibernate.engine.QueryParameters - named parameters: {bars=<snipped long string of numbers and letters>}
DEBUG 2004-08-16 15:29:37,162 net.sf.hibernate.hql.QueryTranslator - compiling query
ERROR 2004-08-16 15:29:37,178 net.sf.hibernate.QueryException: unexpected token: as [
select foo
from FooImpl as foo
where foo.bar in elements(:bars)
]
at net.sf.hibernate.hql.FromParser.token(FromParser.java:94)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:123)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:294)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1562)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1533)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at net.ruckusFooTest.getFooBars(FooTest.java:199)

Name and version of the database you are using:
Oracle 10g

just use "in (:bars)"

Thanks for the response. It looks like I'm closer but I still get an error. Now, I get the following stack trace. Is it okay that the collection I pass in contains Objects, or does it need to contain only the ids?

Hibernate: select foo0_.foo_id as bar_1_, foo0_.title as title, foo0_.bar_id as bar_id from foo foo0_, bar bar1_ where (foo0_.bar_id in(?))
DEBUG 2004-08-16 16:33:58,810 net.sf.hibernate.impl.BatcherImpl - preparing statement
DEBUG 2004-08-16 16:33:58,981 net.sf.hibernate.util.JDBCExceptionReporter - SQL Exception
java.sql.SQLException: ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:420)
at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:896)
at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:452)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:986)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2929)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at net.ruckus.FooTest.getFooBars(FooTest.java:199)

Try setParameterList() instead of setParameter()

This time I got an infinite loop where the Foo keeps getting loaded over and over again.

DEBUG 2004-08-16 16:50:04,379 net.sf.hibernate.loader.Loader - processing result set
DEBUG 2004-08-16 16:50:04,379 net.sf.hibernate.loader.Loader - result row: 23
DEBUG 2004-08-16 16:50:04,379 net.sf.hibernate.loader.Loader - Initializing object from ResultSet: 4942274
DEBUG 2004-08-16 16:50:04,379 net.sf.hibernate.loader.Loader - Hydrating entity: net.ruckus.FooImpl#23
DEBUG 2004-08-16 16:50:04,410 net.sf.hibernate.impl.SessionImpl - Version: 0

Then this line appears repeatedly until I kill the server:

DEBUG 2004-08-16 16:50:04,410 net.sf.hibernate.loader.Loader - result row: 23

Use your debugger

Yeah, sorry. Ignore my last post. User error :)

Your last suggestion of use setParameterList, combined with doing "in (:bars)" worked great.

Thanks for your help.