How to update multiple columns using PreparedStatement in Java - JDBC

java, jdbc, mysql

Solution

No, no, no and no!

Do NOT build SQL statements by concatenating strings. I thought we had been over this a long time ago. It is a very bad practice and a very good way to create errors and let huge security holes into your applications. Creating SQL statements by concatenating strings is a recipe for disaster and a bad habit that needs to be killed off before it takes hold.

Use prepared statements - it will save you a lot of pain in the long run.

For example, compare what you have written with this:

PreparedStatement update = connection.prepareStatement
    ("UPDATE items SET name = ?, category = ?, price = ?, quantity = ? WHERE id = ?");

update.setString(1, u.getName());
update.setString(2, u.getCategory());
...
update.setInt(5, u.getId());

update.executeUpdate();

Sure, it is slightly more verbose, but far more readable and far less error-prone - not to mention that JDBC takes care of those pesky quoting issues that make SQL injection possible.

Problem

I want to update multiple columns data in my MySQL database with Java application through using PreparedStatement but I could not figure out how to do it. Here is the code query for updating: ``` String s = "UPDATE items SET name =" + u.getName() + ",category=" + u.getCategory() + ", price=" + u.getPrice() + " ,qunatity=" + u.getQuantity() + " WHERE id = '" + u.getId() + "'; "; ```

Original source