How do I check if the OUT parameter value is null?

By Wayan in JDBC 0 Comment

This example show you how to use the CallableStatement.wasNull() method call to see if the last OUT parameter has a value of SQL NULL.

package org.kodejava.jdbc;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.Types;
import java.sql.SQLException;

public class WasNullExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // Prepares a call to the sored procedure
            String query = "call Get_Product_By_Price2(?, ?)";
            CallableStatement cb = connection.prepareCall(query);

            // Sets the input parameter
            cb.setBigDecimal(1, new BigDecimal("50"));

            // Registers the OUT parameter
            cb.registerOutParameter(2, Types.VARCHAR);

            // Executes the query
            cb.executeQuery();

            // Gets the OUT parameter value
            cb.getString(2);

            // Checks if the last OUT parameter has value of SQL NULL.
            // This method should be called only after calling a
            // getter method; otherwise, there is no value to use in
            // determining whether it is null or not.
            if (cb.wasNull()) {
                System.out.println("Product has an SQL NULL value");
            } else {
                System.out.println("Product: " + cb.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Here is the stored procedure script we called in the example above.

DELIMITER ;;
DROP PROCEDURE IF EXISTS Get_Product_By_Price2;;
CREATE PROCEDURE Get_Product_By_Price2(
    IN product_price DECIMAL(10, 2),
    OUT product_name VARCHAR(50))
BEGIN
    SELECT NAME
    INTO product_name
    FROM products
    WHERE price = product_price;
END;;
DELIMITER ;

Maven dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.0.33</version>
</dependency>

Maven Central

How do I call a stored procedure that return a result set?

By Wayan in JDBC 0 Comment

This example show you how to call a stored procedure that return a result set of the query execution result.

package org.kodejava.jdbc;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class CallableStatementExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // Prepares a call to the sored procedure. This SP takes
            // one IN parameter
            String query = "call Get_Product_By_Price(?)";
            CallableStatement cb = connection.prepareCall(query);

            // Sets the input parameter
            cb.setBigDecimal(1, new BigDecimal("50.99"));

            // Execute the query
            ResultSet rs = cb.executeQuery();

            while (rs.next()) {
                System.out.println("Product: " + rs.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Here is the stored procedure script we use in the example above.

DELIMITER ;;
DROP PROCEDURE IF EXISTS Get_Product_By_Price;;
CREATE PROCEDURE Get_Product_By_Price(IN product_price DECIMAL(10, 2))
BEGIN
    SELECT name FROM products WHERE price = product_price;
END;;
DELIMITER ;

Maven dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.0.33</version>
</dependency>

Maven Central

How do I register out parameter in CallableStatement?

By Wayan in JDBC 0 Comment

This example show you how to register out parameter for executing a stored procedure using the CallableStatement.registerOutParameter() method call. We must register the out parameters before the query execution. The registerOutParameter() method takes two parameters, the index of the parameter and the sql data type of the out parameter.

package org.kodejava.jdbc;

import java.sql.*;

public class RegisterOutParameter {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // Creates a CallableStatement for executing the stored
            // procedure
            String query = "call Get_Product_Detail_By_Name(?, ?, ?)";
            CallableStatement cb = connection.prepareCall(query);

            // Sets the input parameter
            cb.setString(1, "Data Structures, Algorithms");

            // Registers the out parameters
            cb.registerOutParameter(2, Types.VARCHAR);
            cb.registerOutParameter(3, Types.DECIMAL);

            // Executes the query
            cb.executeQuery();

            // Gets the query result output
            System.out.println("Name  : " + cb.getString(1));
            System.out.println("Code  : " + cb.getString(2));
            System.out.println("Price : " + cb.getBigDecimal(3));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Here is the MySQL stored procedure that we call in the code above.

DELIMITER ;;
DROP PROCEDURE IF EXISTS `Get_Product_Detail_By_Name`;;
CREATE PROCEDURE `Get_Product_Detail_By_Name`(
    INOUT product_name VARCHAR(50),
    OUT product_code VARCHAR(10),
    OUT product_price DECIMAL(10, 2))
BEGIN
    SELECT code INTO product_code FROM products WHERE name = product_name;

    SELECT price INTO product_price FROM products WHERE name = product_name;
END;;
DELIMITER ;

Maven dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.0.33</version>
</dependency>

Maven Central

How do I execute stored procedure?

By Wayan in JDBC 0 Comment

Stored procedure are user-generated functions or procedures that, once created in the database, can be called by the client applications, such as Java application. In this example we’ll demonstrate how to use the JDBC java.sql.CallableStatement to call a stored procedure. The store procedure in this example is just for selecting records from the products table.

package org.kodejava.jdbc;

import java.sql.*;

public class CallableStatementDemo {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // Create a CallableStatement to execute the GetAllProducts() 
            // procedure.
            CallableStatement stmt =
                    connection.prepareCall("{CALL Get_All_Products()}");

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getLong("id") + "\t"
                        + rs.getString("code") + "\t"
                        + rs.getString("name") + "\t"
                        + rs.getDouble("price"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Below is the stored procedure definition that was executed in the code above.

DELIMITER ;;
CREATE PROCEDURE `Get_All_Products`()
BEGIN
    SELECT * FROM products;
END;;
DELIMITER ;

When running the code snippet the result will be something like this:

1   P0000001    UML Distilled 3rd Edition   25.0
3   P0000003    PHP Programming 20.0
4   P0000004    Longman Active Study Dictionary 40.0
5   P0000005    Ruby on Rails   24.0
6   P0000006    Championship Manager    0.0
7   P0000007    Transport Tycoon Deluxe 0.0
8   P0000008    Roller Coaster Tycoon 3 0.0
9   P0000009    Pro Evolution Soccer    0.0
10  P0000010    Data Structures, Algorithms 50.99

Maven dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.0.33</version>
</dependency>

Maven Central