HQL IN expression problems.

nyte3k · **Joined:** Mon Dec 18, 2006 2:39 am **Posts:** 16

I"m trying to do a simple SQL where clause with the IN expression like so:

String accountNumberList = "1,2,3");

session.createQuery("select * from table where account.accountId in (:accoountNumberList)").setString("accountNumberList", accountNumberList);

The thing is that only the results that match the first character in that string are returned. It will not return the data that matched the next characters.

I've tried using setParameterList() , with a List instead of a String (accountNumberList) but gives me an exception saying an Integer was expected but a String was received.

i92absaj · **Posted:** Tue Feb 06, 2007 10:32 am

I think that:

Code:

[...] account.accountId in (:accoountNumberList)").setString( [...]

means that accountId (integer type) is equals to a string value. It can't cast the value from integer to string.

Another way to code the in option is using criteria API.

Code:

Criteria criterio = session.createCriteria(XXX.class);
Vector<Integer> accoountNumberList =new Vector<Integer>();
accoountNumberList.add(1);
accoountNumberList.add(2);
accoountNumberList.add(3);

criterio.add(
   Restrictions.not(
      Restrictions.in("accountId", accoountNumberList)
   )
); 

nyte3k · **Joined:** Mon Dec 18, 2006 2:39 am **Posts:** 16

i'll give that a shot... I'm kind of new to using criteria queries...

Can you also set parameters using a criteria?

like...
.setString(s,s).setParameter(p,p);

i92absaj · **Posted:** Tue Feb 06, 2007 12:23 pm

Yes, you can set parameters, have a look at:
http://www.hibernate.org/hib_docs/v3/re ... teria.html

You may code the restriction you need. You only have to add Restrictions

Criteria API is recommend when you need to built a query dynamically from user input.

nyte3k · **Joined:** Mon Dec 18, 2006 2:39 am **Posts:** 16

After messing around with it a bit, it does feel quite powerful...now I don't have have to keep a separate class of sql/hql string constants :)

I seem to be getting a ClassCastException still... java.lang.String cannot be cast to java.lang.Integer.

org.hibernate.type.IntegerType.set(IntegerType.java:41)...

points to line 147 of my code which is the list() method applied at the end of my query...

Code:

List someList = session.createCriteria(LocationStats.class)
  .add(Restrictions.eq("salaryId", salary))
  .add(Restrictions.in("metroArea.id", metroAreas))
  .list() //LINE 147

salaryId is an int; metroArea.id is an int; metroAreas is a list of integers. So i'm not seeing where the String comes in.

I've also tried changing the list to a String, and my metroArea.id parameter to a String, but the same problem occurs.

nyte3k · **Joined:** Mon Dec 18, 2006 2:39 am **Posts:** 16

btw, it does appear (according to my logs) that the sql query is being ran before that exception, if that means anything

i92absaj · **Posted:** Wed Feb 07, 2007 4:35 am

Sorry, I can't find any error in the code you've send...
I guess there should be mismatched types...

May you try to split the query into two to focus on the restriction error?
First:

Code:

List someList = session.createCriteria(LocationStats.class)
  .add(Restrictions.eq("salaryId", salary))
  .list();

and later:

Code:

List someList = session.createCriteria(LocationStats.class)
  .add(Restrictions.in("metroArea.id", metroAreas))
  .list();

Although, I suppose the error would appear in the second option :-S