How to Handle NULL Values in JDBC? - Coding

Java programming can be able to handle databases by using JDBC API. In this article, we will discuss how to handle NULL values in JDBC.

For this problem, we have inserted some data into an already created table named book in my Database named books. To handle null values in JDBC involves checking for null values in ResultSet.
The ResultSet is one of the interfaces available in java.sql package for handling PreparedStatement results.
The PreparedStatement is used for executing SQL queries in the form of parameters and the parameters are represented by ‘?’ symbol.

Table Structure:

Below is the book table, before inserting any null values into it.

book Table

Approach to handle NULL values in JDBC

For Handling null values in JDBC API,

We can use the ResultSet interface because it has a method that is wasNull().
This method can be able to check whether the given ResultSet object is null or not.
If the ResultSet is not null, we perform insert operation otherwise we will replace those null values with N/A.

This is one approach we have another approach also for handling NULL values in JDBC is exception handling if any null value is traced then raise an exception after that no data is inserted into the table.

Program to handle NULL values in JDBC

In this below example, we have inserted some data into table then one row of data has null values. Before insertion we have replaced those null value to N/A.

Java

// Java Program to Handle NULL values in JDBC 
package horje; 
  
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
  
// Driver Class 
public class RetrieveDataExample { 
    // Main Function 
    public static void main(String[] args) 
    { 
        try { 
            // Load the MySQL JDBC driver 
            Class.forName("com.mysql.cj.jdbc.Driver"); 
  
            // Establish a connection to the database 
            Connection con = DriverManager.getConnection( 
                "jdbc:mysql://localhost:3306/books", "root", 
                "password"); 
  
            if (con != null) { 
                // Insert a new book into the 'book' table 
                String insertQuery 
                    = "INSERT INTO book (id, author, name, price) VALUES (?, ?, ?, ?)"; 
                
                PreparedStatement insertStatement 
                    = con.prepareStatement(insertQuery); 
  
                insertStatement.setInt(1, 500); 
  
                // Set author to NULL 
                insertStatement.setNull( 
                    2, java.sql.Types.VARCHAR); 
  
                // Set name to NULL 
                insertStatement.setNull( 
                    3, java.sql.Types.VARCHAR); 
  
                // Set price to NULL 
                insertStatement.setNull( 
                    4, java.sql.Types.VARCHAR); 
                int rowsInserted 
                    = insertStatement.executeUpdate(); 
  
                // Check if the insertion was successful 
                if (rowsInserted > 0) { 
                    System.out.println(rowsInserted + " row(s) inserted successfully."); 
                } 
                else { 
                    System.out.println("Insert failed."); 
                } 
  
                // Retrieve all data from the 'book' table 
                String selectQuery = "SELECT * FROM book"; 
                PreparedStatement selectStatement = con.prepareStatement(selectQuery); 
                ResultSet resultSet = selectStatement.executeQuery(); 
  
                System.out.println("The Available Data\n"); 
  
                // Iterate over the result set and print the 
                // retrieved data 
                while (resultSet.next()) { 
                    int id = resultSet.getInt("id"); 
  
                    // Check for NULL values and replace 
                    // them with a default value 
                    String author_name = resultSet.getString("author"); 
                    if (resultSet.wasNull()) { 
                        // Or any default value you prefer 
                        author_name = "N/A"; 
                    } 
  
                    String book_name = resultSet.getString("name"); 
                    if (resultSet.wasNull()) { 
                        // Or any default value you prefer 
                        book_name = "N/A"; 
                    } 
  
                    String book_price = resultSet.getString("price"); 
                    if (resultSet.wasNull()) { 
                        // Or any default value you prefer 
                        book_price = "N/A"; 
                    } 
  
                    System.out.println( 
                        "ID: " + id 
                        + ", Author_Name: " + author_name 
                        + ", Book_Name: " + book_name 
                        + ", Book_Price " + book_price); 
                } 
            } 
            else { 
                System.out.println("Not Connected..."); 
            } 
        } 
        catch (Exception e) { 
            // Print any exceptions that occur during 
            // database access 
            System.out.println("Exception is " + e.getMessage()); 
        } 
    } 
}

Output:

Below is the Table after inserting the null values.

Data Output

Explanation of the above code:

We have inserted data with null values.
Then logic automatically identify the null values then replace with N/A.
We can observe in the above output image marked that row with straight line.
This means if any null value detected, then it will replace them with NA.
The resultSet.wasNull() is used for checking null values in ResultSet.
If ResultSet don’t have any null values data inserted successfully with actual values inserted with NA only.

Reffered: https://www.geeksforgeeks.org

Java

Related
How to Customize the Ordering of Elements in a PriorityQueue Using a Comparator in Java?
How to Create a File with a Specific File Attribute in Java?
How to Serialize and Deserialize a HashSet in Java?
How to Create a File with a Specific charset in Java?
How to Calculate Age From a Birthdate Using Java Date/Time?

Type:	Geek
Category:	Coding
Sub Category:	Tutorial
Uploaded by:	Admin
Views:	13