How to set/insert null values to in to a column of a row using JDBC program?
You can insert null values into a table in SQL in two ways:
Insert into SampleTable values (NULL);
Insert into SampleTable values (NULL);
While inserting data into a table using prepared statement object you can set null values to a column using the setNull() method of the PreparedStatement interface.
pstmt.setNull(parameterIndex, sqlType);
Example
Assume we have a table named cricketers_data in the database with the following contents:
+------------+------------+---------------+----------------+-------------+ | First_Name | Last_Name | Date_Of_Birth | Place_Of_Birth | Country | +------------+------------+---------------+----------------+-------------+ | Shikhar | Dhawan | 1981-12-05 | Delhi | India | | Jonathan | Trott | 1981-04-22 | CapeTown | SouthAfrica | | Kumara | Sangakkara | 1977-10-27 | Matale | Srilanka | | Virat | Kohli | 1987-04-30 | Delhi | India | | Rohit | Sharma | 1987-04-30 | Nagpur | India | +------------+------------+---------------+----------------+-------------+
Following JDBC program connects to database and inserts a new row into it with place of birth value as NULL.
import java.sql.Connection; import java.sql.Date; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.Statement; import java.sql.Types; public class InsertingNull < public static void main(String args[])throws Exception < //Registering the Driver DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //Getting the connection String mysqlUrl = "jdbc:mysql://localhost/mydatabase"; Connection con = DriverManager.getConnection(mysqlUrl, "root", "password"); System.out.println("Connection established. "); //Creating a Statement object Statement stmt = con.createStatement(); //Inserting values to a table String query = "INSERT INTO cricketers_data VALUES (?, ?, ?, ?, ?)"; //Creating a Prepared Statement object PreparedStatement pstmt = con.prepareStatement(query); pstmt.setString(1, "Ravindra"); pstmt.setString(2, "Jadeja"); pstmt.setDate(3, new Date(597396086000L)); //Setting null value to a column pstmt.setNull(4, Types.NULL); pstmt.setString(5, "India"); pstmt.execute(); System.out.println("Record inserted"); >> Output
Connection established. Record inserted.
If you retrieve the contents of the cricketers_data table you can observe the newly inserted record with null value.
mysql> select * from cricketers_data; +------------+------------+---------------+----------------+-------------+ | First_Name | Last_Name | Date_Of_Birth | Place_Of_Birth | Country | +------------+------------+---------------+----------------+-------------+ | Shikhar | Dhawan | 1981-12-05 | Delhi | India | | Jonathan | Trott | 1981-04-22 | CapeTown | SouthAfrica | | Lumara | Sangakkara | 1977-10-27 | Matale | Srilanka | | Virat | Kohli | 1987-04-30 | Delhi | India | | Rohit | Sharma | 1987-04-30 | Nagpur | India | | Ravindra | Jadeja | 1988-12-06 | NULL | India | +------------+------------+---------------+----------------+-------------+ 6 rows in set (0.00 sec)
Java Essential Tips: How to set null value in Java JDBC PreparedStatement
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.
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();
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.
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();
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:
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();
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.