103

Java PreparedStatement provides a possibility to explicitely set a Null value. This possibility is:

prepStmt.setNull(parameterIndex, Types.VARCHAR);

Are the semantics of this call the same as when using a specific setType with a null parameter?

prepStmt.setString(null);

?

rogerdpack
  • 62,887
  • 36
  • 269
  • 388
paweloque
  • 18,466
  • 26
  • 80
  • 136

5 Answers5

83

This guide says:

6.1.5 Sending JDBC NULL as an IN parameter

The setNull method allows a programmer to send a JDBC NULL (a generic SQL NULL) value to the database as an IN parameter. Note, however, that one must still specify the JDBC type of the parameter.

A JDBC NULL will also be sent to the database when a Java null value is passed to a setXXX method (if it takes Java objects as arguments). The method setObject, however, can take a null value only if the JDBC type is specified.

So yes they're equivalent.

Sergey Vyacheslavovich Brunov
  • 17,291
  • 7
  • 48
  • 81
djna
  • 54,992
  • 14
  • 74
  • 117
  • 2
    +1: Interesting. I assumed that's how setXXX worked with nulls, but I'd never actually tested it or read the docs for it. – Powerlord Aug 31 '09 at 13:45
  • 2
    I don't suppose there is something like myPreparedStatement.setInteger(myIntegerObject) (although I see that exact method name does not exist) in the case I'm wanting use a potentially null integer? Otherwise I'm going to have to use an if/else statement, calling .setInt() one way and .setNull() the other way, which seems a bit tedious. –  May 22 '12 at 02:10
  • @ardave, yes that's what I mean by my final paragraph – djna May 22 '12 at 03:58
  • @user74754 if you aren't sure whether your Integer is going to be null, and you don't want to use an if/else, then you could do this: stmt.setObject(1, myPossiblyNullInteger, java.sql.Types.INTEGER) – ldkronos Jun 25 '18 at 14:57
83

but watch out for this....

Long nullLong = null;

preparedStatement.setLong( nullLong );

-thows null pointer exception-

because the protype is

setLong( long )

NOT

setLong( Long )

nice one to catch you out eh.

user207421
  • 305,947
  • 44
  • 307
  • 483
Owen
  • 839
  • 6
  • 2
  • That is actually the example that brought me here. – sf_jeff Aug 17 '20 at 02:01
  • 2
    In this case (using a Postgres DB in my case), you can use the setObject method. `Long nullLong = null; preparedStatement.setObject(nullLong);` – casperw Apr 06 '21 at 18:50
  • @Owen : if(obj== null || obj == 0) {ps.setLong(INDEX_TWO, Types.BIGINT);} else {ps.setLong(INDEX_TWO, obj);} worked for me – Onic Team Jan 20 '22 at 10:18
13

Finally I did a small test and while I was programming it it came to my mind, that without the setNull(..) method there would be no way to set null values for the Java primitives. For Objects both ways 

setNull(..)

and 

set<ClassName>(.., null))

behave the same way.

paweloque
  • 18,466
  • 26
  • 80
  • 136
11

You could also consider using preparedStatement.setObject(index,value,type);

Suresh Karia
  • 17,550
  • 18
  • 67
  • 85
bithom
  • 186
  • 2
  • 14
11
preparedStatement.setNull(index, java.sql.Types.NULL);

that should work for any type. Though in some cases failure happens on the server-side, like: for SQL:

COALESCE(?, CURRENT_TIMESTAMP)

Oracle 18XE fails with the wrong type: expected DATE, got STRING -- that is a perfectly valid failure;

Bottom line: it is good to know the type if you call .setNull().

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Dmitriy Pichugin
  • 370
  • 2
  • 5