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

Question

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

SternK · Answer 1 · 2020-07-22T11:47:50.343

2

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'

edited Jul 22 '20 at 11:47

answered Jul 21 '20 at 05:58

SternK

11,649
22
32
46

thanks for the answer, but i did found out that hibernate did convert the sql into SELECT * FROM table WHERE id IN ('1,2,3'); before sending it into mysql – Red Baron Jul 21 '20 at 09:22
1

In fact, this is not transformation, hibernate generate exactly the same query as you ask, but then mysql transform the `'1,2,3'` value to `1` and this behaviour can be unexpected as mentioned in the mysql documentation. – SternK Jul 21 '20 at 09:58

flip66 · Accepted Answer · 2020-07-21T05:58:48.403

1

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)

edited Jul 21 '20 at 05:58

answered Jul 21 '20 at 05:47

flip66

341
2
5
17

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

2 Answers2