getting an error while executing an sqlQuery containing in clause

Question

I have an sqlQuery:

select * from products where product_type in :prodTypes
query.setParameter("prodTypes",getInClauseFromList(productTypes));

productTypes is a List<String>

public String getInClauseFromList(List list) {
    if(list != null && !list.isEmpty()) {
      String inClause = Arrays.toString(list.toArray());
      inClause = inClause.replace("[", "('").replace("]", "')");
      inClause = inClause.replace(", ", "','");
      return inClause;
    }
    return "('')";
  }

I get an error:

Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your
SQL syntax; check the manual that corresponds to your MySQL server version for the right
syntax to use near ''(\'clothes\')')

It would be helpful for you and us if you could also show us what the actual text of the query is. Right now, I think we could only speculate about the exact syntax error. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 29, 2015 at 9:46
I tried the show sql: I get select * from products where product_type in ? It's not helpful :( — senior
– senior, Commented Jul 29, 2015 at 9:48
I need to see the IN clause as plain text. There is some small problem, but we need to see this. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 29, 2015 at 9:48
OK...but this is a bit like throwing out the baby with the bathwater. My advice to you is to print to console or log the result from getInClauseFromList(). It will be obvious why MySQL is barfing on it. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 29, 2015 at 9:54

nutfox · Accepted Answer · 2015-07-29 09:50:15Z

1

I would recommend using something like Spring's JDBCTemplate. Then you don't have to convert the list manually to a String.

In your example above the String you are passing in is quoted (as it should be) and hence the resulting SQL is not correct.

Given that I don't know which type "query" is: Try setting the list directly as the parameter. Most of the frameworks should support converting it properly for the IN clause.

answered Jul 29, 2015 at 9:50

nutfox

4843 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

getting an error while executing an sqlQuery containing in clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related