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 the document "How to" use Java to access relational databases, with the MySQL database as an example. Suppose we have created a database instance with and 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 would 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.geti         NT ("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's 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 is 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 if it hits a null on the Job-id.

How to solve the problem?

There is and ways to detect whether a null value is read.

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

Traverse Sqlresultwhile (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() retrieve the value of the Job-id column. getObject()by using the method, we is able to get null values if there any appears.

Traverse Sqlresultwhile (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