CODEWITHSUNDEEP
javamysqlhibernate
Red Baron
Red Baron

Reputation: 23

Hibernate Why setParameter for a concatenate string in the IN Clause not working

I do some research but have no satisfied answer yet. I have a table, and it supposed to have 3 records if I run it under a database management system like navicat.

____
id  |
____    
1   |
2   |
3   |

If I using set parameter with a concatenate string, it will return just the 1st value

List<Integer> here = Arrays.asList(new Integer[]{1, 2, 3});
SQLQuery query = session.createSQLQuery("SELECT * FROM table WHERE id IN (:here)");
query.setParameter("here", StringUtils.join(here, ","));
query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
query.list();

but if I use setParameterList, it will return all values:

List<Integer> here = Arrays.asList(new Integer[]{1, 2, 3});
SQLQuery query = session.createSQLQuery("SELECT * FROM table WHERE id IN (:here)");
query.setParameterList("here", here );
query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
query.list();

I did debug it to see the real sql, but I see no problem in the 1st case

Hibernate: 
    /* dynamic native SQL query */ 
    SELECT 
         * 
    FROM 
         table 
    WHERE 
         id IN (
               ?
         )
HibernateLog --> TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - [1,2,3]
TRACE BasicBinder:81 - binding parameter [1] as [VARCHAR] - [1,2,3]

Can someone deep explain why the 1st case not working? I am using mysql.

EDIT:

Thanks for all the suggestions. After i used log4jdbc-log4j2-jdbc4.1 and logged the sql out (before hibernate send it to database):

DEBUG jdbc.sqlonly -  com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:116)
500. SELECT * FROM table WHERE id IN ('1,2,3')

16:19:33.518 [http-nio-8280-exec-4] INFO  jdbc.resultsettable - 
|---|
|id |
|---|
|1  |
|---|

Anyone want to log the full sql before it goes to db can see this link: https://stackoverflow.com/a/19299769/4181109

Upvotes: 1

Views: 1611

Answers (2)

SternK
SternK

Reputation: 13111

It looks like the root cause of this behaviour is MySql type conversion in expression evaluation.

MySql try to convert passed to the expr IN (value,...) value '1,2,3' to DOUBLE type (see this) and as result you actually get:

SELECT * FROM table WHERE id IN (1)

when you run this:

SELECT * FROM table WHERE id IN ('1,2,3')

and mysql also generate the following warning:

Warning: Truncated incorrect DOUBLE value: '1,2,3'

Upvotes: 2

flip66
flip66

Reputation: 341

That is expected since hibernate is detecting types to build your query and you send a string instead of a list. The second method is explicitly telling it to expect a list.

https://docs.jboss.org/hibernate/orm/3.2/api/org/hibernate/Query.html#setParameterList(java.lang.String,%20java.util.Collection)

Upvotes: 1

Related Questions