There are three ways to insert nulls using a parameterized query.
First, use the set method for the specific type (setString(), setInt(), setLong(), etc.):
pQuery.setString('charVal', null);
pQuery.setLong('bigintVal', null);
Second, use setNull():
pQuery.setNull('charVal', java.sql.Types.CHAR);
pQuery.setNull('bigintVal', java.sql.Types.BIGINT);
Third, use setObject():
pQuery.setObject('charVal', null, java.sql.Types.CHAR);
pQuery.setObject('bigintVal', null, java.sql.Types.BIGINT);
The java.sql.Types used in the setNull() and setObject() are also used by the other set methods under the covers. Here is a mapping for the setters, the SQL types, and the Java types.
| Set Method |
Java Type |
SQL/JDBC Type |
| setString() |
java.lang.String |
java.sql.Types.VARCHAR |
| setCharacterStream() |
java.io.Reader |
java.sql.Types.LONGVARCHAR |
| setInt() |
java.lang.Integer |
java.sql.Types.INTEGER |
| setLong() |
java.lang.Long |
java.sql.Types.BIGINT |
| setShort() |
java.lang.Short |
java.sql.Types.SMALLINT |
| setDouble() |
java.lang.Double |
java.sql.Types.DOUBLE |
| setFloat() |
java.lang.Float |
java.sql.Types.REAL |
| setBigDecimal() |
java.math.BigDecimal |
java.sql.Types.NUMERIC |
| setDate() |
java.util.Date |
java.sql.Types.DATE |
| setTime() |
java.sql.Time |
java.sql.Types.TIME |
| setTimestamp() |
java.sql.Timestamp |
java.sql.Types.TIMESTAMP |
| setBoolean() |
java.lang.Boolean |
java.sql.Types.BIT |
| setByte() |
java.lang.Byte |
java.sql.Types.TINYINT |
| setBytes() |
java.lang.Byte[] |
java.sql.Types.VARBINARY |
| setBinaryStream() |
java.io.InputStream |
java.sql.Types.LONGVARBINARY |
Below is the complete example. Note that when we do a SELECT statement to find a record with null that we need to use SQL syntax.
//Drop temp table if it exists
var dropStatement = "IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmp_common_types') DROP TABLE tmp_common_types;";
var statementCount = qie.doUpdateQuery('works', dropStatement);
qie.debug("drop table count: " + statementCount);
//Create a temp table
var createStatement = "" +
"CREATE TABLE tmp_common_types (" +
"charCol CHAR(100), " +
"bigintCol BIGINT " +
");" +
"";
statementCount = qie.doUpdateQuery('works', createStatement);
qie.debug("create table count: " + statementCount);
//Use a parameterized insert statement
var insertStatement = "" +
"INSERT INTO tmp_common_types (" +
"charCol, " +
"bigintCol " +
") VALUES (" +
":charVal, " +
":bigintVal " +
")";
var pQuery = qie.getParameterizedQuery(insertStatement);
//Use the setter that is the best match for the
//database columns data type to set it to null
pQuery.setString('charVal', null);
pQuery.setLong('bigintVal', null);
statementCount = pQuery.doUpdateQuery('works');
qie.debug("NULLs - insert count: " + statementCount);
pQuery.clearParameters();
//Use setNull method and pass in the SQL type
//for the database column to set it to null
pQuery.setNull('charVal', java.sql.Types.CHAR);
pQuery.setNull('bigintVal', java.sql.Types.BIGINT);
statementCount = pQuery.doUpdateQuery('works');
qie.debug("NULLs - insert count: " + statementCount);
pQuery.clearParameters();
//Use setObject method and pass in null and the SQL type
//for the database column to set it to null
pQuery.setObject('charVal', null, java.sql.Types.CHAR);
pQuery.setObject('bigintVal', null, java.sql.Types.BIGINT);
statementCount = pQuery.doUpdateQuery('works');
qie.debug("NULLs - insert count: " + statementCount);
//To query for null is requires SQL IS NULL or IS NOT NULL syntax
var selectStatement = "" +
"SELECT " +
" charCol, " +
" bigintCol " +
" FROM tmp_common_types " +
" WHERE " +
" charCol IS NULL " +
" AND bigintCol IS NULL " +
"";
pQuery = qie.getParameterizedQuery(selectStatement);
var results = pQuery.doQuery('works', true);
qie.debug("NULLs - " + results);
See also:
How to convert a 'Standard Query' to a 'Parameterized Query'?
How to insert binary data using a parameterized query
