How to query for potentially null value with Java NamedParameterJdbcTemplate

Question

Here's an example of how I'm using a NamedParameterJdbcTemplate:

SqlParameterSource params = new MapSqlParameterSource("column1", value1)
                                            .addValue("column2", value2)
                                            .addValue("column3", value3);
List<String> values = namedParameterJdbcTemplate.query("SELECT column4 FROM my_table WHERE column1=:column1 and column2=:column2 and column3=:column3", params, (rs, numRow) -> {
    return rs.getString("column4");
});

This usually works just fine, but I've got a case where value2 can be null. Then it doesn't work because null can't be compared normally.

How do I get namedParameterJdbcTemplate to handle the case where I'm looking for a null?

To be clear, for the SQL query to be correct it would need to evaluate to WHERE ... and column2 is null ... instead of WHERE ... and column2=null.

Answer 1

Try using:

SELECT
    column4
FROM
    my_table
WHERE
    (column1=:column1 OR (column1 IS NULL AND :column1 IS NULL)) and
    (column2=:column2 OR (column2 IS NULL AND :column2 IS NULL)) and
    (column3=:column3 OR (column3 IS NULL AND :column3 IS NULL))

Alternatively, you could try using COALESCE:

COALESCE(column1, '') = COALESCE(:column1, '')

That's assuming that you want to equate an empty string with NULL. Also, this will preclude using any index(es) on column1, so be aware of that.

You could also try to change your query string based on the values of :column1, 2, and 3 - adjust it to use = or IS NULL appropriately.