Using a CallableStatement in JDBC is the standard way to execute stored procedures. It allows you to handle input parameters (IN), output parameters (OUT), and even result sets returned by the database.

Here is a guide on how to use it for different scenarios.

1. Basic Syntax

The syntax for calling a stored procedure uses the escape sequence {call procedure_name(?, ?)}.

// Standard format for calling a procedure with parameters
String query = "{call get_user_details(?, ?)}";
CallableStatement stmt = connection.prepareCall(query);

2. Working with Input Parameters (IN)

If your procedure requires data, you set them just like a PreparedStatement using index-based setters (starting at 1).

try (CallableStatement stmt = conn.prepareCall("{call get_product_by_id(?)}")) {
    stmt.setInt(1, 101); // Set the first parameter

    try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
            System.out.println("Product: " + rs.getString("name"));
        }
    }
}

3. Working with Output Parameters (OUT)

If the procedure returns a value via an OUT parameter, you must register its SQL type before execution and retrieve it afterward.

String sql = "{call get_employee_count_by_dept(?, ?)}";
try (CallableStatement stmt = conn.prepareCall(sql)) {
    // 1. Set the IN parameter
    stmt.setString(1, "Engineering");

    // 2. Register the OUT parameter type
    stmt.registerOutParameter(2, java.sql.Types.INTEGER);

    // 3. Execute
    stmt.execute();

    // 4. Retrieve the value from the OUT parameter
    int count = stmt.getInt(2);
    System.out.println("Total employees: " + count);
}

4. Handling Stored Functions

If you are calling a database function that returns a value directly, the syntax changes slightly to include a return placeholder at the beginning.

// The first '?' is the return value
String sql = "{? = call calculate_tax(?)}";
try (CallableStatement stmt = conn.prepareCall(sql)) {
    stmt.registerOutParameter(1, java.sql.Types.DOUBLE);
    stmt.setDouble(2, 5000.00);

    stmt.execute();
    double tax = stmt.getDouble(1);
    System.out.println("Tax: " + tax);
}

Key Best Practices:

• Try-with-resources: Always wrap your Connection and CallableStatement in a try-with-resources block to ensure they are closed automatically.
• Parameter Indexing: Remember that JDBC parameters are 1-indexed.
• SQL Types: Use java.sql.Types when registering OUT parameters to match your database schema.
• Performance: CallableStatement objects are often pre-compiled by the database driver, making them more efficient than raw SQL strings for repeated calls.