jdbcTemplate pass in null value

Question

I'm working with mysql and spring 4.1.6, but am having trouble sending in a null variable to the data base. I know to send a null value the syntax IS NULL is used but the field being sent into the data base is optional(the only optional value in the entire object), so it may be null or may have a value.

My request to database that simple checks if the address exists called by CRUD methods:

Note: the variable in question is street2

public boolean doesExist(Address address){
    String sql = "SELECT EXISTS(SELECT * FROM Address WHERE contactType=? AND  street1=? AND street2 ? AND city=? AND state=? AND zip=? AND country=?);";

    String s = isNullDBQuery(address.getStreet2());

    jdbcTemplate = new JdbcTemplate(dataSource);
    int exist = jdbcTemplate.queryForObject(sql, new Object[] {address.getContactType().name(), address.getStreet1(), isNullDBQuery(address.getStreet2()), address.getCity(), address.getState().name(), address.getZip(), address.getCountry().name()}, Integer.class);

    if(exist == 1){
        return true;
    } else {
        return false;
    }
}

My isNullDBQuery:

private String isNullDBQuery(String s){
    if(s == null){
        return "IS NULL";
    } else {
        return "=" + s;
    }
}

The error that's being returned is a syntax error with 'IS NULL'. The query sent to the database is street2 'IS NULL' rather then street2 IS NULL. Is it possible to get rid of the single quotation marks in my request? or is there a better way to do this?

any help is appreciated

Answer 1

I managed to figure out how to achieve what I wanted:

public boolean doesExist(Address address){
    String street2 = stringIsNull(address.getStreet2());
    String sql = "SELECT EXISTS(SELECT * FROM Address WHERE contactType=? AND  street1=? AND street2 " + street2 + " AND city=? AND state=? AND zip=? AND country=?);";
    jdbcTemplate = new JdbcTemplate(dataSource);
    int exist;

    if(street2.equals("IS NULL")){
        exist = jdbcTemplate.queryForObject(sql, new Object[] {address.getContactType().name(), address.getStreet1(), address.getCity(), address.getState().name(), address.getZip(), address.getCountry().name()}, Integer.class);
    } else {
        exist = jdbcTemplate.queryForObject(sql, new Object[] {address.getContactType().name(), address.getStreet1(), address.getStreet2(), address.getCity(), address.getState().name(), address.getZip(), address.getCountry().name()}, Integer.class);
    }

    if(exist == 1){
        return true;
    } else {
        return false;
    }

}

private String stringIsNull(String string){
    if(string == null) {
        return "IS NULL";
    } else {
        return "=?";
    }
}

Answer 2

No. Only values may be passed as parameters of a prepared statement. Not arbitrary portions of the query.

You need to use two different SQL queries (or generate it dynamically based on the nullness of the street2).