Doing a SQL batch update in Java that won't update a field if passed-in parm is null

Question

How do I create a batch update that won't update a column if the passed-in parameter value is null? For example:

String UPDATE_STMT = "UPDATE MY_TABLE SET col1 = ?, col2 = ?, col3 = ?";
Connection conn = getConnection();
PreparedStatement pstmt = conn.preparedStatement(UPDATE_STMT);

List items = getItems();
for (ItemType item : items) {

    stmt.setString(1, item.x);
    stmt.setString(2, item.y);
    stmt.setString(3, item.z);
}

pstmt.executeBatch();

How do I code it so that col1 will only be updated with the value of item.x if item.x is not null or empty? if item.x is null/empty, I don't want the current value in the col1 field to be overridden. This is for an Oracle 10g database.

score 2 · Answer 1 · answered Sep 17 '12 at 16:36

2

I guess it should work for you:

String UPDATE_STMT = "UPDATE MY_TABLE SET col1 =   
CASE WHEN ? IS NULL THEN col1 ELSE ? END, col2 = ?, col3 = ?";

Note, now you need to set item.x twice, stmt.setString(1, item.x); stmt.setString(2, item.x);

answered Sep 17 '12 at 16:36

a1ex07

36,826
12
90
103

score 0 · Accepted Answer · answered Sep 17 '12 at 16:37

0

Use COALESCE to return col1 if item.x is null:

String UPDATE_STMT = "UPDATE MY_TABLE SET col1 = COALESCE(?,col1), col2 = ?, col3 = ?"

Then all you need to do is make sure that item.x is always going to be null, not a blank string.

answered Sep 17 '12 at 16:37

Jim

3,482
22
18

COALESCE returns null if the parameters are all null. – Jim Sep 17 '12 at 19:01

score 0 · Answer 3 · edited May 23 '17 at 12:21

If you don't want alex07's Oracle specific solution, just regenerate the prepared statement for each set of items.

Connection conn = getConnection();

List items = getItems();
for (ItemType item : items) {

    String UPDATE_STMT = "UPDATE MY_TABLE SET ";
    if (item.x != null) {UPDATE_STMT += "col1 = ?, ";}
    UPDATE_STMT += "col2 = ?, col3 = ?";

    PreparedStatement pstmt = conn.preparedStatement(UPDATE_STMT);

    int count = 0;
    if (item.x != null) {pstmt.setString(++count, item.x);}
    pstmt.setString(++count, item.y);
    pstmt.setString(++count, item.z);

    pstmt.executeBatch();
}

Doing a SQL batch update in Java that won't update a field if passed-in parm is null

3 Answers3