CODEWITHSUNDEEP
javamysqldata-bindingprepared-statement
Mad-D
Mad-D

Reputation: 4669

Binding NULL in JAVA for MySQL PreparedStatment?

MySQL uses "IS NULL" instead of "NULL" so how do i bind variable to achieve the same ?

Right now i tried with this 

selectStmt.setNull( 2, java.sql.Types.VARCHAR );

when i printed "java.sql.Types.VARCHAR" shows value = 12 ? If so how does this set " IS NULL" in preparedstatment ?

MORE DETAILS:

i.e I am interested in querying (MySQL) for null if i have string = " " in input. So logic goes like this, 

Connection c = SQLCon.connect();
PreparedStatement selectStmt = c.prepareStatement( "select * from "
                                    + TableName
                                    + " where P = ? AND M = ? AND PR = ? AND DR = ? " );


if ( P.trim().equals( "" ) ) 
{
   selectStmt.setNull( 1, java.sql.Types.VARCHAR );
} else 
{
   selectStmt.setString( 1, P );
}

if ( M.trim().equals( "" ) ) 
{
   selectStmt.setNull( 2, java.sql.Types.VARCHAR );
} else 
{
   selectStmt.setString( 2, M );
}

Please advice

Upvotes: 1

Views: 7386

Answers (3)

Mad-D
Mad-D

Reputation: 4669

it's an xml input and on UI user see blank for NULL and hence to send blank as input 

if ( P.trim().equals( "" ) )
  {
    sql += "P IS NULL";
  }
   else
  {
    sql += "P = ?";
  }

PreparedStatement selectStmt = c.prepareStatement( sql );

int i = 1;
// used to prevent index out of range issues

// Binding values for non-null strings ONLY
 if ( !(P.trim().equals( "" )) )
  {
    selectStmt.setString( i, P );
  i++;
  }

Upvotes: 0

Paul Tomblin
Paul Tomblin

Reputation: 182782

If you want to match a column in a where clause that might be null with a parameter that might be null, you actually need to bind it twice, because in SQL a null doesn't equal anything else, even another null.

PreparedStatement stmt = conn.prepareStatement("select * from foo where (? is null and bar is null) or (bar = ?)");
col = 1;
setVarchar(stmt, col++, var);
setVarchar(stmt, col++, var);

void setVarchar(PreparedStatement stmt, int col, String var)
{
  if (var == null)
    stmt.setNull(col, java.sql.Types.VARCHAR);
  else
    stmt.setString(col, var);
}

Upvotes: 6

Edwin Buck
Edwin Buck

Reputation: 70909

It seems odd, but you do:

if (variable == null) {
  selectStmt.setNull(2, java.sql.Types.VARCHAR);
} else {
  ...
}

the reason behind this is because NULL is a value in a database, and not having a value is null in Java. Occasionally, you need to differentiate between getting back a NULL from the database as opposed to getting back nothing from the database.

Upvotes: 2

Related Questions