Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate Version 3.0.5

Hi Everyone,

I am unable to find the correct HQL syntax for selecting all records based on a given collection of composite keys.
My mapping files all seem to be fine, i believe the problem is with my HQL query. Would any of you please give it a more experience view.

Setup:

Class Record {
//fields
RecordKey key;
}

Class RecordKey {
String unitId;
String companyId;
String tranId;
}

Record class is mapped with a composite Key mapping in *.hbm.xml file.

Current HQL.

<![CDATA[
FROM Record rec
WHERE
rec.key.companyId ='1' AND
(rec.key) in elements(keys) ]]>

This currently throws a null pointer when trying to create a hibernate session since it seems to preparse all the named query files.

'keys' is a array/collection of RecordKey class. It wont like me use ':keys'
I set this using query.setParameter("keys",keys); in my code.
I've overridden equals + hashcode incase it needed to compare keys.

I've tried every combination of different field checks and syntax i can think of and searched Google for the last 2 days but cannot find what i'm looking for.



Thank you kindly for your help.

Jake

- don't use "elements" in the query
- use query.setParameterList not query.setParameter
- you'll have to use :keys or ?, keys surely won't work

Thanks for the aid.

If i use setParameterList and use :keys,
so "where record.key in :keys"
i receive a hibernateException Errors in named queries, unexpected token ":"

If i wrap the :keys in brackets i recieve:

Caused by: java.sql.SQLException: [SQL0104] Token , was not valid. Valid tokens: &lt; &gt; = &lt;&gt; &lt;= !&lt; !&gt; != &gt;= ¼&lt; ¼&gt; ¼= IN IS
NOT LIKE BETWEEN. Cause . . . . . : A syntax error was detected at token ,. Token , is not a valid token. A partial list of valid tokens is &lt; &
gt; = &lt;&gt; &lt;= !&lt; !&gt; != &gt;= ¼&lt; ¼&gt; ¼= IN IS NOT LIKE BETWEEN. This list assumes that the statement is correct up to the token. ......


Any further ideas?

Sounds like progress. What was the generated SQL when you had the SQL exception?

Thanks mikewilliams,

Here is the SQL it generated.
(ive obviously been given different names for simplicity reasons in my earlier posts)

select unittransa0_.CHDRCOY as CHDRCOY, unittransa0_.CHDRNUM as CHDRNUM, unittransa0_.TRANNO as TRANNO, unittransa0_.VRTFND as VRTFND9_, unittransa0_.UNITYP as UNITYP9_, unittransa0_.NOFDUNT as NOFDUNT9_, unittransa0_.PRICEUSED as PRICEUSED9_, unittransa0_.PRICEDT as PRICEDT9_, unittransa0_.FUNDAMNT as FUNDAMNT9_ from UTRNFBC unittransa0_ where (unittransa0_.CHDRCOY, unittransa0_.CHDRNUM, unittransa0_.TRANNO)in (?)

This looks like a hibernate bug - invalid SQL is being generated. I haven't been able to find it in JIRA but I didn't search very hard.

Running your example on 3.0.5 and 3.2.6 the generated SQL is very different (and very wrong with 3.0.5).



Basically you need to upgrade!