MySQL Query fails in my App, but succeeds in MySQL Command Line Tool

I am running this MySQL query against my db that contains a lot of customer data. the query is giant so I'll cut out parts. We're using 5.1.45-51-log Percona SQL Server which is a pretty common version of MySQL.

select distinct *  " +
               "from customer_contact_preferences prefs " +
...
               "join re_account_attribute uaa on uaa.account_id = ua.id " +
              "where ... " +
...
               "and uaa.attribute_key = 'R_GROUP' " +
               //"and uaa.attribute_value in ( ? ) " + //PROBLEM HERE
              "order by customer_contact_id)"

the argument to uaa.attribute value is '1','2',3'.

In the code, we use org.springframework.jdbc.core.simple.SimpleJDBCDaoSupport.update() to call this query. When it is called in the code through Spring, it incorrectly returns 0 rows. When I substitute the args (4 people have checked that I did the substitution right), and run it on the mysql command line, the query correctly returns >5 rows. If I comme开发者_如何学Cnt out the problem line, like i'm doing above, I get >5 and actually too many rows (that's why I need that constraint).

The table is described as follows:

| attribute_key      | varchar(255)     | NO   |     | NULL    |                |
| attribute_value    | varchar(255)     | NO   |     | NULL    |                |

It is so we can store any kind of key/value pair, not restricted to varchar or ints.

Anyway, what is wrong with doing "IN " when you have a string or list of strings? Thank you.

Parameterized IN clauses are supported by Spring JDBC templates only if you use named parameters instead of ?s, see 12.7.3 Passing in lists of values for IN clause. So, you need

getSimpleJdbcTemplate().update("... IN (:params) ...", 
    Collections.singletonMap("params", ...));

You can't bind a List or array to '?'.

You have to create a '?' for each item in the List or array in the SQL and then bind each one individually.

List<String> values = Arrays.asList(new String [] { "foo", "bar", "baz" });
StringBuilder sql = new StringBuilder();
sql.append("SELECT * FROM X WHERE ID IN (");
for (int i = 0; i < values.length; ++i)
{
    sql.append('?');
    if (i != values.length-1))
        sql.append(',');
}
sql.append(")");
PreparedStatement ps = connection.prepareStatement(sql.toString());
for (int i = 0; i < values.length; ++i)
{
    ps.setString(i+1, values[i]);
}
ResultSet rs = ps.executeQuery();