Java PreparedStatement setString() adding quotes in query (mysql jdbc)

Question

I'm new in Prepared Statements, not sure if what I'm trying to do is legal or not.

 String updatequery = "Update articles SET Title = ? WHERE id IN ?";

    try{
        prestatement = connect.prepareStatement(updatequery);

        prestatement.setString(1, "Test");
        prestatement.setString(2,"(4,5,6)");

I get the following error:

Error: com.mysql.jdbc.exceptions.jdbc4.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 ''(4,5,6)'' at line 1

It seems like the setString() method is adding quotes to the query, is there a way to avoid this or another method to use to accomplish what I'm trying to do ?

You can't use `?` as the target of an `in` clause. You can do `in (?,?,?)`, however. — GriffeyDog, Mar 10 '14 at 14:35
See [here](http://stackoverflow.com/q/178479/823393) for a discussion about using `IN` in prepared statements. — OldCurmudgeon, Mar 10 '14 at 14:39
see this http://stackoverflow.com/questions/3107044/preparedstatement-with-list-of-parameters-in-a-in-clause — Kick, Mar 10 '14 at 14:40

Vasco Mars · Accepted Answer · 2014-03-10T15:18:47.247

0

try this:

final Collection<Integer> ids = Arrays.asList(4, 5, 6);

final StringBuilder updatequery = new StringBuilder("Update articles SET Title = ? WHERE id IN (");

for (int i = 0; i < ids.size(); i++) {
    updatequery.append("?,");
}

updatequery.deleteCharAt(updatequery.length() - 1);
updatequery.append(")");

System.out.println(updatequery);

prestatement.setString(1, "Test");
int i = 2;
for (final Integer id : ids) {
    prestatement.setInt(i ++, id);
}

edited Mar 10 '14 at 15:18

answered Mar 10 '14 at 14:39

Vasco Mars

86
3

I did try this and it does work but it's not as flexible as what I was hoping for since you need to change both the Prepared Statement and add a new setInt if more entries are needed. – neilnm Mar 10 '14 at 15:00
Is it flexible enought now? :-) – Vasco Mars Mar 10 '14 at 15:23

Java PreparedStatement setString() adding quotes in query (mysql jdbc)

1 Answers1