PreparedStatement and 'null' value in WHERE clause

[Radoslaw Sztando]

Hi,

  
//String name= "Dave";
String name= null;
String sql = "UPDATE PEOPLE SET AGE=15 WHERE NAME=?";
PreparedStatement queryStatement = (PreparedStatement)conn.getConnection().prepareStatement(sql);
    if (name== null) {
ParameterMetaData parm = queryStatement.getParameterMetaData();
int type = parm.getParameterType(1);
queryStatement.setNull(1, type);
    }else {
queryStatement.setObject(1, name);
    }
queryStatement.executeUpdate();  

Why above code does not update column AGE for all records which NAME is null? In normal query I would rather use "...WHERE NAME IS NULL" for this case but I want to have general solution (handling both NULL and not NULL values in WHERE clause). Guys, do you have any idea how to do it?

[Jeanne Boyarsky]

Originally posted by Radoslaw Sztando:
Why above code does not update column AGE for all records which NAME is null?

Because you aren't using "is null". Null will never match on =.

but I want to have general solution (handling both NULL and not NULL values in WHERE clause). Guys, do you have any idea how to do it?

You are on the right track with your if statement. You just need to add a bit more logic:

  
String name= null;
String sql = "UPDATE PEOPLE SET AGE=15 WHERE ";
 
if ( name == null ) {
  sql += " name is null ";
} else {
  sql += " NAME=?" ;
}

PreparedStatement queryStatement = (PreparedStatement)conn.getConnection().prepareStatement(sql);
    if (name!= null) {
queryStatement.setString(1, name);
    }
queryStatement.executeUpdate();  

[Xiangning Liu]

Originally posted by Jeanne Boyarsky:

  
String name= null;
String sql = "UPDATE PEOPLE SET AGE=15 WHERE ";
 
if ( name == null ) {
  sql += " name is null ";
} else {
  sql += " NAME=?" ;
}

PreparedStatement queryStatement = (PreparedStatement)conn.getConnection().prepareStatement(sql);
    if (name!= null) {
queryStatement.setString(1, name);
    }
queryStatement.executeUpdate();  

In this case, it's not necessary to use PreparedStatement, why not use Statement directly?

 
String sql = "UPDATE PEOPLE SET AGE=15 WHERE ";
 
if ( name == null ) {
  sql += " name is null ";
} else {
  sql += " NAME = " + name ;
}
 
Statement stmt = conn.getConnection().createStatement(sql);
stmt.executeUpdate();

[Jeanne Boyarsky]

Originally posted by Xiangning Liu:
In this case, it's not necessary to use PreparedStatement, why not use Statement directly?

Because prepared statements protect you from SQL injection. I'd much rather the JDBC driver handle that than my having to write a (possibly incorrect) validator.

[Srilakshmi Vara]

In your query your are checking

name = null or name = passed value

in oracle name = null is different from name is null.

Change your code accordingly.

HTH