CODEWITHSUNDEEP
javasqlhibernatehqlwhere-in
Chantz
Chantz

Reputation: 5963

When using Hibernate for Java getting abnormal behavior binding parameterList in HQL

I am using Hibernate v3.6.4 in my Java project. I am getting problems when trying to use a parameter list for a named parameter in my HQL query.

Basically, I want to get all the records that match one of the IDs mentioned in the IN clause.

I tried using both HQL & Criteria, but I get the same result.

My HQL query:

Set<String> reportIds = new HashSet<String>();
reportIds.add("1");
reportIds.add("2");

String whereClause = "from Report where id IN (:reportIds) ";
Query query = session.createQuery(whereClause);
query.setParameterList("reportIds", reportIds);

Output = empty list. Although I checked by firing manual sql queries in the terminal that there are indeed such records.

I turned on the logging and here is what I see:

Hibernate:

    /* 
from
    Report 
where
    id IN (
        :ids
    ) */ select
        mediavalue0_.id as id31_,
        ...
        
    from
        report mediavalue0_ 
    where
        mediavalue0_.id in (
            ?
        )

HibernateLog --> 13:22:36 TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - 1,2

This is very abnormal, because if you notice the final bind statement, it considers the toString() of the Set, i.e. "1,2" instead of "1" and then "2"!

I was messing around with it, so on a whim, I decided to directly create the instance of the Set inside the setParameterList() method call itself. Like so,

query.setParameterList("reportIds", Sets.newHashSet("1","2"));

And it worked! BTW, Sets.newHashSet() is a construct provided by Google's Guava library. I use the same library to generate the original reportIds set. So, there is no inconsistency there.

This query translates to following TRACE:

from
    Report 
where
    id IN (
        :ids
    ) */ select
        mediavalue0_.id as id31_,
        ...
    from
        report mediavalue0_ 
    where
        mediavalue0_.id in (
            ? , ?
        )

HibernateLog --> 13:28:57 TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - 1
HibernateLog --> 13:28:57 TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [2] as [VARCHAR] - 2

Notice the separate binds for VARCHAR.

I am totally flummoxed by this odd behavior. Maybe some of you guys can point out, what I am doing wrong.

FYI the criteria construct, I used (& which resulted in the same output), is like:

Criteria criteria = session.createCriteria(Report.class);
criteria.add(Restrictions.in("id", reportIds));

P.S. I also used a named SQL query with same result:

<sql-query name="reportByIds">
    <return class="report.Report"/>
    SELECT mvr.* from report mvr
    WHERE mvr.id IN :ids
</sql-query>

UPDATE:

I found the issue. Kind of embarrassing. The client side code was sending a Set of 1 element, which was the concatenated String of the report IDs. AArgh!

Upvotes: 1

Views: 1033

Answers (1)

Roman C
Roman C

Reputation: 1

Assume id attribute is of type Long

Set<Long> reportIds = new HashSet<Long>();
reportIds.add(1l);
reportIds.add(2l);

then the query should work, the criteria also works

Upvotes: 0

Related Questions