Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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:
Directly inserting the value NULL into the desired column as:
Insert into SampleTable values (NULL);
Using ‘ ’ as 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)
Updated on: 30-Jul-2019
5K+ Views
Advertisements