JDBC CallableStatement Interface

In JDBC API there is a Statement interface that provides functionality to execute static SQL statements. Then there is PreparedStatement interface that extends Statement and adds functionality to create parameterized SQL statements. PreparedStatement is extended by CallableStatement in JDBC API and it provides functionality to execute SQL stored procedures.

How to get CallableStatement Object

You can get a CallableStatement instance by calling the prepareCall() method of the Connection interface.

CallableStatement callableStatement = connection.prepareCall(“{call PROCEDURE_NAME(?, ?, ?)}”);

Here ‘?’ is a place holder used to register IN, OUT and INOUT parameters.

If you want to call a function using Callable statement-

CallableStatement callableStatement = connection.prepareCall(“? = {call PROCEDURE_NAME(?, ?, ?)}”);

Methods in CallableStatement

CallableStatement inherits execute methods from its parent class-

execute()- Executes any SQL statement. This method returns a boolean; true if the first result is a ResultSet object; false if it is an update count or there are no results.
executeQuery()- Executes SQL statement that returns a ResultSet.
executeUpdate()- Executes the SQL statement which may be a DML statement like INSERT, UPDATE or DELETE or an SQL statement that returns nothing, such as an SQL DDL statement (Create, Drop).

There are several setter methods for passing the value of IN and INOUT parameters of the appropriate type.

setDate(String parameterName, Date x)- Sets the designated parameter to the given java.sql.Date value using the default time zone of the virtual machine that is running the application.
setDouble(String parameterName, double x)- Sets the designated parameter to the given Java double value.
setFloat(String parameterName, float x)- Sets the designated parameter to the given Java float value.
setInt(String parameterName, int x)- Sets the designated parameter to the given Java int value.
setLong(String parameterName, long x)- Sets the designated parameter to the given Java long value.
setShort(String parameterName, short x)- Sets the designated parameter to the given Java short value.
setString(String parameterName, String x)- Sets the designated parameter to the given Java String value.
setTime(String parameterName, Time x)- Sets the designated parameter to the given java.sql.Time value.

There are also the getter counterparts to retrieve the values from the OUT parameters.

You need to register the out parameters using one of the overloaded variant of the registerOutParameter() method where you can pass either the index of the parameter or the parameter name.

registerOutParameter(int parameterIndex, int sqlType)- Registers the OUT parameter in ordinal position parameterIndex to the JDBC type sqlType.
registerOutParameter(String parameterName, int sqlType)- Registers the OUT parameter named parameterName to the JDBC type sqlType.

CallableStatement Java example

1. In this CallableStatement example we'll execute a stored procedure having both IN and OUT parameters. In the stored procedure there is a SELECT statement to get data about an employee by passing its id. There is one IN parameter for ID and three OUT parameters for the data that is retrieved.

SQL Procedure

CREATE PROCEDURE `select_employee_by_id`(IN param_id int, 
    OUT param_fname varchar(45), 
    OUT param_lname varchar(45), 
    OUT param_dept varchar(45))
BEGIN
 SELECT first_name, last_name, department 
 INTO param_fname, param_lname, param_dept
 from EMPLOYEE 
 where id = param_id;
END

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

public class CallableStatementDemo {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode", 
        "root", "admin");
      // CallableStatement object
      CallableStatement callableStatement = connection.prepareCall(
        "{call select_employee_by_id(?, ?, ?, ?)}");
      // Setting IN parameter - employee ID
      callableStatement.setInt(1, 5);
      // Registering OUT parameters
      callableStatement.registerOutParameter(2, JDBCType.VARCHAR);
      callableStatement.registerOutParameter(3, JDBCType.VARCHAR);
      callableStatement.registerOutParameter(4, JDBCType.VARCHAR);
      
      callableStatement.executeQuery();	      	    
      System.out.println("Employee Record " + "First Name: " + callableStatement.getString(2) + 
          " Last Name: " + callableStatement.getString(3) +
          " Department: " + callableStatement.getString(4));
	     
    } catch (ClassNotFoundException | SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null){         
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } 
    }
  }
}

2. Here is another CallableStatement Java example where stored procedure returns a ResultSet (multiple records).

SQL Procedure

CREATE PROCEDURE `select_all_employee`()
BEGIN
  SELECT * FROM employee;
END

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

public class CallableStatementDemo {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Load driver
      Class.forName("com.mysql.cj.jdbc.Driver");
      // connection object
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode", 
        "root", "admin");
      // Getting CallableStatement object
      CallableStatement callableStatement = connection.prepareCall(
        "{call select_all_employee()}");
    
      ResultSet rs = callableStatement.executeQuery();
      while(rs.next()){
        System.out.println("Employee Id: " + rs.getInt("id") + 
            " First Name: " + rs.getString("FIRST_NAME") + 
            " Last Name: " + rs.getString("LAST_NAME") + 
            " Department: " + rs.getString("DEPARTMENT")); 
      }	   	    
    } catch (ClassNotFoundException | SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally{
      if(connection != null){         
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } 
    }
  }
}

That's all for the topic JDBC CallableStatement Interface. If something is missing or you have something to share about the topic please write a comment.

You may also like

KnpCode

August 15, 2022