Target Audience: Java Beginners, JDBC Developers.
What should you know already?: JDBC, PreparedStatement, SQL queries
The Java beginners who works on JDBC should have understanding of what the table and its structure is. It would help to deal with parameters, especially mapping ‘null’ values. Because ‘null’ability of a field should be handled carefully when you pass through Java program.
Passing ‘null’ to String is different from passing ‘null’ to int
Consider the code snippet, in which the SQL query expects 2 parameters: name and address of type VARCHAR.
[java]
String query="INSERT INTO person (name, address) VALUES (?,?)";
PreparedStatement ps=c.prepareStatement(query); // c – java.sql.Connection
ps.setString(1, "Ganesh");
ps.setString(2, null);
ps.executeUpdate();
[/java]
The above code works fine and inserts a row with ‘null’ in address column. Now check the following code, which contains one more column contact_number of type BIGINT. This column allows ‘null’. Here trying to insert a person whose contact number is not known ie null, neither be zero.
[java]
String query="INSERT INTO person (name, address, contact_number) VALUES (?,?,?)";
PreparedStatement ps=c.prepareStatement(query); // c – java.sql.Connection
ps.setString(1, "Raja Raman");
ps.setString(2, null);
ps.setLong(3, null); // error
ps.executeUpdate();
[/java]
The above code will give a error at line 5. Because the method setLong expects a value of primitive date type long, but here it is ‘null’ type.
How to pass ‘null’ to number column
The correct way to pass ‘null’ to int or long or any number type could be as follows:
[java]
String query="INSERT INTO person (name, address, contact_number) VALUES (?,?,?)";
PreparedStatement ps=c.prepareStatement(query); // c – java.sql.Connection
ps.setString(1, "Raja Raman");
ps.setString(2, null);
ps.setNull(3, java.sql.Types.BIGINT); // no error, perfect
ps.executeUpdate();
[/java]
The setNull method gives a flexible way of passing ‘null’ value to almost all kind of data types available in a typical relational database.
Feel free to comment. Happy Programming.