Skip to content
Java jdbc 6 min read

CallableStatement

CallableStatement is the JDBC interface you use whenever you want to execute a stored procedure (or a stored function) that lives inside the database. Think of it as the bridge between your Java code and pre-compiled logic that runs entirely on the database side.

Why Use Stored Procedures?

Stored procedures are named, pre-compiled routines stored in the database. They can accept input, perform complex logic (loops, conditionals, multi-table updates), and return output — all without sending multiple round-trips from your application.

Benefits at a glance:

  • Performance — the database parses and optimises the procedure once, at creation time.
  • Encapsulation — business logic lives in the DB, shared across multiple applications.
  • Security — applications can call a procedure without having direct SELECT/INSERT rights on the underlying tables.
  • Reduced network traffic — one call replaces many SQL statements.

Note: CallableStatement extends PreparedStatement, which itself extends Statement. Every method available on those interfaces is also available here.

Syntax for Calling a Stored Procedure

JDBC uses a database-agnostic escape syntax with curly braces:

// Procedure with no result (or just IN params)
"{call procedure_name(?, ?, ?)}"

// Procedure that returns a value (function)
"{? = call function_name(?, ?)}"

The ? placeholders represent parameters — exactly like PreparedStatement. Parameters can be:

KindDirectionDescription
INInput onlyYou supply a value before calling
OUTOutput onlyThe procedure writes a result back
INOUTBothYou supply a value; the procedure may change it

Creating a CallableStatement

You get a CallableStatement from an open Connection:

Connection conn = DriverManager.getConnection(url, user, password);
CallableStatement cs = conn.prepareCall("{call get_employee(?, ?)}");

Always close CallableStatement in a try-with-resources block to avoid resource leaks.

Working with IN Parameters

IN parameters are set using the same set* methods as PreparedStatement — indexed from 1.

Example: Stored Procedure with IN Parameters

Assume a MySQL stored procedure:

CREATE PROCEDURE add_employee(IN emp_name VARCHAR(100), IN dept_id INT)
BEGIN
    INSERT INTO employees (name, department_id) VALUES (emp_name, dept_id);
END;

Call it from Java:

import java.sql.*;

public class InParamExample {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://localhost:3306/company";

        try (Connection conn = DriverManager.getConnection(url, "root", "secret");
             CallableStatement cs = conn.prepareCall("{call add_employee(?, ?)}")) {

            cs.setString(1, "Alice");
            cs.setInt(2, 3);

            int rows = cs.executeUpdate();
            System.out.println("Rows inserted: " + rows);
        }
    }
}

Output:

Rows inserted: 1

Tip: You can also set parameters by name using cs.setString("emp_name", "Alice") if your JDBC driver supports named parameters (most modern drivers do).

Working with OUT Parameters

OUT parameters let the stored procedure send a value back to your Java code. You must register each OUT parameter with registerOutParameter before calling the procedure.

Example: Procedure with an OUT Parameter

CREATE PROCEDURE get_salary(IN emp_id INT, OUT emp_salary DECIMAL(10,2))
BEGIN
    SELECT salary INTO emp_salary FROM employees WHERE id = emp_id;
END;
import java.sql.*;

public class OutParamExample {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://localhost:3306/company";

        try (Connection conn = DriverManager.getConnection(url, "root", "secret");
             CallableStatement cs = conn.prepareCall("{call get_salary(?, ?)}")) {

            // Set IN parameter
            cs.setInt(1, 42);

            // Register OUT parameter — must specify its SQL type
            cs.registerOutParameter(2, Types.DECIMAL);

            cs.execute();

            // Retrieve the OUT value after execution
            double salary = cs.getDouble(2);
            System.out.printf("Salary: %.2f%n", salary);
        }
    }
}

Output:

Salary: 75000.00

The java.sql.Types class contains constants for every standard SQL type (Types.INTEGER, Types.VARCHAR, Types.BOOLEAN, etc.). You must register the correct type before calling execute().

Warning: Calling getDouble(2) (or any get* method on an OUT parameter) before calling execute() throws a SQLException. Always execute first, then retrieve.

Working with INOUT Parameters

An INOUT parameter flows in both directions. You set it like an IN parameter and also register it like an OUT parameter.

CREATE PROCEDURE apply_bonus(INOUT salary DECIMAL(10,2), IN bonus_pct DECIMAL(5,2))
BEGIN
    SET salary = salary + (salary * bonus_pct / 100);
END;
import java.sql.*;

public class InOutParamExample {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://localhost:3306/company";

        try (Connection conn = DriverManager.getConnection(url, "root", "secret");
             CallableStatement cs = conn.prepareCall("{call apply_bonus(?, ?)}")) {

            // Set the INOUT parameter (current salary)
            cs.setDouble(1, 60000.00);
            // Register it as an OUT parameter too
            cs.registerOutParameter(1, Types.DECIMAL);

            // Set the IN parameter (bonus percentage)
            cs.setDouble(2, 10.0);

            cs.execute();

            double newSalary = cs.getDouble(1);
            System.out.printf("New salary after bonus: %.2f%n", newSalary);
        }
    }
}

Output:

New salary after bonus: 66000.00

Retrieving a ResultSet from a Procedure

Stored procedures can return one or more result sets. Use executeQuery() when a result set is expected, then iterate it exactly like you would after a SELECT.

CREATE PROCEDURE get_department_staff(IN dept_id INT)
BEGIN
    SELECT id, name, salary FROM employees WHERE department_id = dept_id;
END;
import java.sql.*;

public class ResultSetExample {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://localhost:3306/company";

        try (Connection conn = DriverManager.getConnection(url, "root", "secret");
             CallableStatement cs = conn.prepareCall("{call get_department_staff(?)}")) {

            cs.setInt(1, 3);

            try (ResultSet rs = cs.executeQuery()) {
                while (rs.next()) {
                    System.out.printf("ID: %d  Name: %-20s  Salary: %.2f%n",
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getDouble("salary"));
                }
            }
        }
    }
}

Output:

ID: 42   Name: Alice                Salary: 75000.00
ID: 58   Name: Bob                  Salary: 68000.00

Handling Multiple Result Sets

Some procedures return more than one result set. Use getMoreResults() to walk through them:

cs.execute();
do {
    try (ResultSet rs = cs.getResultSet()) {
        if (rs != null) {
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
        }
    }
} while (cs.getMoreResults());

Calling a Stored Function

A stored function returns a single value directly (as opposed to a procedure’s OUT parameter). The escape syntax puts a ? before the =:

CREATE FUNCTION get_headcount(dept_id INT) RETURNS INT
BEGIN
    DECLARE cnt INT;
    SELECT COUNT(*) INTO cnt FROM employees WHERE department_id = dept_id;
    RETURN cnt;
END;
try (CallableStatement cs = conn.prepareCall("{? = call get_headcount(?)}")) {
    cs.registerOutParameter(1, Types.INTEGER); // the return value
    cs.setInt(2, 3);
    cs.execute();
    int count = cs.getInt(1);
    System.out.println("Staff in dept 3: " + count);
}

Output:

Staff in dept 3: 2

Null Handling

If an OUT parameter can be NULL, check cs.wasNull() immediately after retrieving it:

double val = cs.getDouble(2);
if (cs.wasNull()) {
    System.out.println("No salary on record.");
} else {
    System.out.println("Salary: " + val);
}

Under the Hood

When you call conn.prepareCall(...), the JDBC driver sends a prepare request to the database (similar to PreparedStatement). The database looks up the stored procedure, validates it exists, and returns a handle. Your Java code holds that handle.

On execute(), the driver sends the parameter values and the handle — not the full SQL text — saving parse and plan overhead on every call. The database executes the pre-compiled procedure and streams the output parameters and result sets back.

Because CallableStatement extends PreparedStatement, the driver can also use server-side prepared statements where supported (MySQL Connector/J, PostgreSQL JDBC, etc.), further reducing per-call overhead.

Tip: Wrap multiple related procedure calls in a Transaction (conn.setAutoCommit(false)) when they must all succeed or all fail together.

Statement vs PreparedStatement vs CallableStatement

FeatureStatementPreparedStatementCallableStatement
SQL typeStatic, no paramsParameterised SQLStored procedures / functions
Compiled atEach callprepareStatement()prepareCall()
SQL injection safe?NoYesYes
OUT / INOUT paramsNoNoYes
Multiple result setsPossiblePossibleYes
Best forOne-off DDLApp queries with paramsDB-side business logic

Quick Reference

// Prepare
CallableStatement cs = conn.prepareCall("{call proc_name(?, ?, ?)}");

// Set IN parameters (1-indexed)
cs.setInt(1, 101);
cs.setString(2, "Alice");

// Register OUT parameters
cs.registerOutParameter(3, Types.VARCHAR);

// Execute
cs.execute();               // general purpose
cs.executeUpdate();         // no result set expected
ResultSet rs = cs.executeQuery(); // result set expected

// Retrieve OUT parameters (after execute)
String result = cs.getString(3);

// Check for NULL output
if (cs.wasNull()) { ... }

// Stored function
CallableStatement fn = conn.prepareCall("{? = call func_name(?)}");
fn.registerOutParameter(1, Types.INTEGER);
fn.setInt(2, 42);
fn.execute();
int returnVal = fn.getInt(1);
Last updated June 13, 2026
Was this helpful?