JDBC – PreparedStatement– How to Set Null value?

Introduction

Java Database Connection (JDBC) API allows the Java programmers to access different relational and NoSQL databases like Oracle, MySQL, SQL Server, etc. It helps to store, access and manipulate the data stored in these databases.

In this post, we will explore how to set the NULL values in PreparedStatement.

Null String data type

For String data type, it does not matter if the value to be set as a parameter in the SQL query is NULL.  

Other data types like Integer, Float, Double, Date, etc.

For data types like Integer, Float, Double, Date we have to explicitly call the setNull method.

Example

String name = null;

Long id = null;

PreparedStatement ps = connection.prepareStatement("select * from person where first_name = ? and id = ?"); 

ps.setString(1,name);

The above line will execute without throwing any exception

ps.setLong(2, id);

In the above line of code, since id is null, it will throw java.lang.NullPointerException

Solution

Check for nullability of value and call the setNull method i.e.

if( id == null ) {

ps.setNull(2, Types.INTEGER);

}else {

        ps.setLong(2, id);

}

JDBC requires that the programmer specify the proper data type for Null value using java.sql.Types.

Complete Code

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;

public class JDBCNullExample {

 public static void main(String[] args) throws Exception{
  Connection connection = null;
  try {   
   connection = openConnection();
   String name = null;
   Long id = null;
   
   PreparedStatement ps = connection.prepareStatement("select * from person 
                                                                            where first_name = ? and id = ?");
   ps.setString(1,name);
   
   if(id == null) {
    ps.setNull(2, Types.INTEGER);
   }else {
    ps.setLong(2, id);
   }
   ResultSet rs = ps.executeQuery(); 
   
   System.out.println(rs.getFetchSize());
   rs.close();
  }catch(Exception exception) {
   exception.printStackTrace();
  }finally {
   if(connection != null) {
    connection.close();
   }
  }  
 }
 
 private static Connection openConnection() throws Exception{  
   Class.forName("oracle.jdbc.OracleDriver");
   return DriverManager.getConnection("jdbc:oracle:thin:@IP:orcl", "user","password");   
 }
}

Conclusion

Except for the String data type, we have to explicitly specify the data type if the value is Null for other data types.