How to Handle NULL Values in JDBC?

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.

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 geeksforgeeks;

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.

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.