Java JDBC CallableStatement Interface Example with MySQL Database

In this article, we will discuss how to use the CallableStatement interface to call a stored procedure from the MySQL database. We also discuss a few important CallableStatement interface methods.

CallableStatement interface Overview

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. This escape syntax has one form that includes a result parameter and one that does not. If used, the result parameter must be registered as an OUT parameter. The other parameters can be used for input, output or both.

A CallableStatement can return one ResultSet object or multiple ResultSet objects. Multiple ResultSet objects are handled using operations inherited from Statement.

CallableStatement interface Class Diagram

The below class diagram shows a list of CallableStatement interface methods:

CallableStatement Interface Examples

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 = [email protected], Country = US, Password = secret
ID = 2, NAME = Pramod, Email = [email protected], Country = India, Password = 123
ID = 3, NAME = A, Email = [email protected], Country = India, Password = 123
ID = 4, NAME = B, Email = [email protected], Country = India, Password = 123
ID = 5, NAME = C, Email = [email protected], Country = India, Password = 123
ID = 6, NAME = D, Email = [email protected], 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 = [email protected]
Users count = 6