How to handle null database fields with Java

浏览数：27 / 时间：2015年06月12日

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

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

How to handle null database fields with Java