sope sope - 1 year ago 84
Java Question

Set NULL values in an UPDATE query, JDBC

In the following case, if the entered values are not NULL, then the UPDATE query runs fine. But if NULL values are given for the time variables, the query just won't work. What seems to be the issue? Thank you.

String sql = "UPDATE table1 SET col1=?, col2=? WHERE col1=? and col2=?";
java.sql.PreparedStatement stmt = conn.prepareStatement(sql); //conn is sql connection to DB

if (time1 != null) stmt.setTimestamp(1, time1); else stmt.setNull(1, java.sql.Types.TIMESTAMP);
if (time2 != null) stmt.setTimestamp(2, time2); else stmt.setNull(2, java.sql.Types.TIMESTAMP);
if (time3 != null) stmt.setTimestamp(3, time3); else stmt.setNull(3, java.sql.Types.TIMESTAMP);
if (time4 != null) stmt.setTimestamp(4, time4); else stmt.setNull(4, java.sql.Types.TIMESTAMP);


Answer Source

The problem is that = NULL never returns true (technically, it returns NULL, which is treated as false).

The simplest solution is to use a NULL-safe comparison:

where col1 <=> ? and col2 <=> ?
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download