Handling Database Null Values

A column in a database table may be defined as being NULL or NOT NULL . NULL indicates that the column may store a NULL value; NOT NULL indicates that the column may not contain a NULL value. A NULL value means that the value is unknown. When a table is created in the database and you don t specify that a column is NULL or NOT NULL , the database assumes you mean NULL .

The Java object types, such as String , may be used to store database NULL values. When a SELECT statement is used to retrieve a column that contains a NULL value into a Java String , that String will contain a Java null value. For example, the phone column (defined as a VARCHAR2 ) for customer #5 is NULL , and the following statement uses the getString() method to read that value into a String named phone :

 phone = customerResultSet.getString("phone"); 

Once the statement is run, the phone Java String will contain the Java null value.

That s fine for NULL values being stored in Java objects, but what about the Java numeric, logical, and bit type types? If you retrieve a NULL value into a Java numeric, logical, or bit variable ” int , float , boolean , and byte , for example ”that variable will contain the value zero. To the database, zero and NULL are different values: zero is a definite value, NULL means the value is unknown. This causes a problem if you want to differentiate between zero and NULL in your Java program.

There are two ways to get around this problem:

• You can use the wasNull() method in the ResultSet . The wasNull() method returns true if the value retrieved from the database was NULL; otherwise , the method returns false .

• You can use a Java wrapper class . A wrapper class is a Java class that allows you to define a wrapper object , which can then be used to store the column value returned from the database. A wrapper object stores database NULL values as Java null values, and non- NULL values are stored as regular values.

Let s take a look at an example that illustrates the use of first technique, using product #12 from the products table. This row has a NULL value in the product_type_id column, and this column is defined as a database INTEGER . Also, assume that a ResultSet object named productResultSet has been populated with the product_id and product_type_id columns for product #12 from the products table. The following example uses the wasNull() method to check if the value read for the product_type_id column was NULL :

 System.out.println("product_type_id = " +  productResultSet.getInt("product_type_id")); if (productResultSet.wasNull()) {  System.out.println("Last value read was NULL"); } 

Because the product_type_id column contains a NULL value, wasNull() will return true , so the string Last value read was NULL would be displayed.

Before you see an example of the second method that uses the Java wrapper classes, I need to explain what these wrapper classes actually are. The wrapper classes are defined in the java.lang package, with the following seven wrapper classes being defined in that package:

• java.lang.Short

• java.lang.Integer

• java.lang.Long

• java.lang.Float

• java.lang.Double

• java.lang.Boolean

• java.lang.Byte

Objects declared using these wrapper classes can be used to represent database NULL values for the various types of numbers , as well as for the Boolean type. When a database NULL is retrieved into such an object, it will contain the Java null value. The following example declares a java.lang.Integer named productTypeId :

 java.lang.Integer productTypeId; 

A database NULL may then be stored in productTypeId using a call to the getObject() method, as shown in the following example:

 productTypeId =  (java.lang.Integer) productResultSet.getObject("product_type_id"); 

The getObject() method returns an instance of the java.lang.Object class and must be cast into an appropriate type, in this case, to a java.lang.Integer . Assuming this example reads the same row from productResultSet as the previous example, getObject() will return a Java null value, and this value will be copied into productTypeId . Of course, if the value retrieved from the database had a value other than NULL , productTypeId would contain that value. For example, if the value retrieved from the database was 1, productTypeId would contain the value 1.

You can also use a wrapper object in a JDBC statement that performs an INSERT or UPDATE to set a column to a regular value or a NULL value. If you want to set a column value to NULL using a wrapper object, you would set that wrapper object to null and use it in an INSERT or UPDAT E statement to set the database column to NULL . The following example sets the price column for product #12 to NULL using a java.lang.Double object that is set to null :

 java.lang.Double price = null; myStatement.executeUpdate("UPDATE products " +  "SET price = " + price + " " +  "WHERE product_id = 12");