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.
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
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.
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
原文：http://www.cnblogs.com/hephec/p/4570549.html