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.

Answer 1

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).