JDBC CallableStatement Stored Procedures Example

In this article, we will learn how to get single ResultSet and multiple ResultSet from a CallableStatement object.
The CallableStatement interface used to execute SQL stored procedures. The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs.
The Connection interface provides prepareCall(String SQL) method to creates a CallableStatement object for calling database stored procedures. The CallableStatement object provides methods for setting up its IN and OUT parameters, and methods for executing the call to a stored procedure.

Technologies used

  1. JDK - 1.8 or later
  2. MySQL - 5.7.12
  3. IDE - Eclipse Neon
  4. JDBC API - 4.2

Steps to process stored procedure SQL statement with JDBC

  1. Establishing a connection.
  2. Create a CallableStatement from a connection object.
  3. Execute the stored procedure query.
  4. Using try-with-resources statements to automatically close JDBC resources
From JDBC 4.0, we don't need to include 'Class.forName()' in our code, to load JDBC driver. JDBC 4.0 drivers that are found in your classpath are automatically loaded.

CallableStatement with Single ResultSet Example

Consider the following MySQL stored procedure.
DELIMITER $$
USE `mysql_database`$$
CREATE PROCEDURE `retreive_users` ()
BEGIN
 select * from users;
END$$
DELIMITER ;
The following program demonstrates how to call the retreive_users() stored procedure and generate a single result set.
package com.javaguides.jdbc.storedprocedure;

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

/**
 * Simple CallableStatement Example
 * @author Ramesh Fadatare
 *
 */
public class SimpleCallableStatementExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/mysql_database?useSSL=false";
        String username = "root";
        String password = "root";
        String sql = "call retreive_users()";

        try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password); CallableStatement stmt = conn.prepareCall(sql); ResultSet rs = stmt.executeQuery();) {

            while (rs.next()) {
                System.out.println("ID = " + rs.getInt(1) + ", NAME = " + rs.getString(2) + ", Email = " +
                    rs.getString(3) + ", Country = " + rs.getString(4) + ", Password = " + rs.getString(5));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
Output:
ID = 1, NAME = A, Email = tony@gmail.com, Country = US, Password = secret
ID = 2, NAME = Pramod, Email = pramod@gmail.com, Country = India, Password = 123
ID = 3, NAME = A, Email = a@gmail.com, Country = India, Password = 123
ID = 4, NAME = B, Email = b@gmail.com, Country = India, Password = 123
ID = 5, NAME = C, Email = c@gmail.com, Country = India, Password = 123
ID = 6, NAME = D, Email = d@gmail.com, Country = India, Password = 123

CallableStatement with Multiple ResultSet Example

Consider the following MySQL stored procedure having multiple select statements.
DELIMITER $$
USE `mysql_database`$$
BEGIN
 select distinct name from users where id = 1;
    
    select distinct email from users;
    
    select count(id) as users_count from users;
END
DELIMITER ;
The following program demonstrates how to call the retreive_different_results() stored procedure and get multiple result set.
package com.javaguides.jdbc.storedprocedure;

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

/**
 * CallableStatement Example
 * @author Ramesh Fadatare
 *
 */
public class CallableMultipleResultSetExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/mysql_database?useSSL=false";
        String username = "root";
        String password = "root";
        String sql = "call retreive_different_results()";

        try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password); CallableStatement stmt = conn.prepareCall(sql);) {

            boolean hasRs = stmt.execute();

            System.out.println();
            // Get Product Names
            if (hasRs) {
                try (ResultSet rs = stmt.getResultSet()) {
                    while (rs.next()) {
                        System.out.println("NAME = " + rs.getString(1));
                    }
                }
            }

            // Get Total Price
            if (stmt.getMoreResults()) {
                try (ResultSet rs = stmt.getResultSet()) {
                    if (rs.next()) {
                        System.out.println("Email = " + rs.getString(1));
                    }
                }
            }

            // Get Max/Min Price
            if (stmt.getMoreResults()) {
                try (ResultSet rs = stmt.getResultSet()) {
                    if (rs.next()) {
                        System.out.println("Users count = " + rs.getInt(1));
                    }
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
Output:
NAME = A
Email = tony@gmail.com
Users count = 6

Comments