Java SQL PreparedStatement UPDATE with optional attributes

Question

I'm writing a Java GUI application which uses a MySQL database. In this application the users can see a JTable with the rows of DB's table and modify the attributes of a selected row with a form. I'd like to update only the modified attributes of the row. I know I have to specify every SQL table's column in the String command if I use PreparedStatement and placeholders

String command = "UPDATE table SET attr0 = ?, attr1 = ? WHERE id = ?";
PreparedStatement statement = connection.prepareStatement(command);

but It's not what I'm looking for. Moreover, my DB's table has many columns, so I can't use different String commands for every combination of attributes.

Can someone help me? Thanks.

If you know which attributes of the row are modified, what's the difficulty in creating a query dynamically by looping over the attributes? — JB Nizet
How can I do this? Should I use placeholders for the attributes too? — Manzo Gray
Yes, you should. Construct a list of attribute name/value pairs, iterate on it once to build the query, and iterate on it a second time to bind each parameter value. — JB Nizet
@JBNizet Downside of this approach is that (prepared) statement caching becomes useless because one very likely creates too many different statements (dependent on number of columns and user behavior). — Ralf
@JBNizet Sure. Just wanted to point it out. Often this requirement is there because of a felt need for optimization or because it looks "crude" to always update all columns. But the effect on overall system load by this optimization might well be negative because of the cost of preparing all statements. — Ralf

user2910265 user2910265 · Accepted Answer · 2013-12-14T13:38:39

Unfortunately with straight JDBC, the best you can do is build the SET clause dynamically (see rough code sample below). JDBC can't handle optional parameters and will throw an Exception if not all parameters are bound before executing.

` // 'columns' assumed a Map // 'id' to be a String

List<String> setClauses = new ArrayList<String>();
for (String key : columns.keySet()) {
    setClauses.add(String.format("%s=?", key));
}

// StringUtils is from Apache Commons Lang
// although it's pretty easy to build your own join routine. 
String command = String.format("UPDATE table SET %s WHERE id=?"
    , StringUtils.join(setClauses, ",")
);

PreparedStatement statement = connection.prepareStatement(command);
int p = 1;
for (String key : columns.keySet()) {
    statement.setString(p++, columns.get(key));
}
statement.setString(p++, id);

`

JDBC also doesn't have named parameters either so this is why you have to do the incrementing. If you are able to do it, I would recommend investigating Hibernate (which allows to work with JavaBeans) or Spring JDBCTemplate (which does have named parameters).

Java SQL PreparedStatement UPDATE with optional attributes

1 Answers