How to handle null database fields with Java

 

Content is king and large software cannot get away from serving content. In this post, I shall document how to use Java to access relational databases, with the MySQL database as an example. Suppose we have created a database instance with two tables with the following SQL commands:

CREATE DATABASE `db-instance`;
USE `db-instance`;

CREATE TABLE Job (
        `id` INT NOT NULL AUTO_INCREMENT,
        `name` VARCHAR(255),
        `description` TEXT,
        PRIMARY KEY(id)
) ENGINE=INNODB;

CREATE TABLE Person (
	`id` INT NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255),
	`address` VARCHAR(255),
	`job-id` INT,
	PRIMARY KEY (ID),
        FOREIGN KEY (`job-id`) REFERENCES Job(`id`)
) ENGINE=INNODB;

　　

The above SQL commands will create a Job table and a Person table in the db-instance database.

Steps to interact with the database through JDBC

• Load the database driver.
• Provide a username and password pair and name of database instance to get a Connection object.
• Create a Statement object from the Connection object.
• Use the Statement object to execute SQL commands to interact with the database.

Sample code to interact with the database through the JDBC api.

Connection dbConnection = null;
try {
     // Load the MySQL driver
     Class.forName("com.mysql.jdbc.Driver");
     // Connect to the database instance (db-instance)
     // @ localhost with a user account (identified by user and password).
     dbConnection = DriverManager.getConnection("jdbc:mysql://localhost/"
             + "db-instance", "user", "password");
     // Execute a SQL select statement on the database.
     Statement sqlStat = dbConnection.createStatement();
     ResultSet sqlResult = sqlStat.executeQuery("SELECT * FROM Person");
     // Traverse sqlResult
     while(sqlResult .next()) {
         // Get the value of job-id
         int jobId = sqlResult.getInt("job-id");
         System.out.println("Job ID: " + jobId);
     } // end while

} catch (ClassNotFoundException cnfe) {
     System.out.println(cnfe.getMessage());
} catch (SQLException sqle) {
     System.out.println(sqle);
} finally {
     // Free up resources used
     if (dbConnection != null) {
         try {
             dbConnection.close();
         } catch (SQLException sqle) {
             // Swallow any exceptions when closing the connection.
         } // end try-catch block
     } // end if
} // end try-catch block

　　

What is wrong with the above code?

Based on the database schema shown earlier, we can see that the job-id column of the Person table can contain null values. However, in the above coding, we are using the getInt() method of the ResultSet class to retrieve a the job-id value. The int data type being one of Java’s primitive types is not able to store the null. On my machine, the getInt() method returns 0 when it hits a null on the job-id.

How to solve the problem?

There are two ways to detect whether a null value is read.

1) Use the wasNull() method provided by the ResultSet class.

// Traverse sqlResult
while(sqlResult.next()) {
    // Get the value of job-id
    int jobId = sqlResult.getInt("job-id");
    // if jobId is supposed to be null
    if (sqlResult.wasNull()) {
            System.out.println("Job ID: null");
    } else {
            System.out.println("Job ID: " + jobId);
    }
} // end while

　　

2) Use the getObject() method instead of the getInt() method to retrieve the value of the job-id column. By using the getObject() method, we are able to get null values if there any appears.

// Traverse sqlResult
while(sqlResult.next()) {
    Object jobId = sqlResult.getObject("job-id");
    if (jobId == null) {
        System.out.println("Job ID: null");
    } else {
        System.out.println("Job ID: " + jobId);
    } // end if (jobId == null)
} // end while