PreparedStatement setNull(..)

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);

?

175441 次浏览
小开
最佳答案

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.

小开

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.

小开

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.

小开

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

小开 
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()

UPDATE: stackoverflow need to hire more editors to add\remove punctuation to my posts here


提交答案