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
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'
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.