Java Notes

CallableStatement in JDBC

Complete guide to CallableStatement — calling stored procedures and functions, IN/OUT/INOUT parameters, and handling multiple result sets from MySQL procedures.

What is CallableStatement?

CallableStatement is used to execute stored procedures and stored functions in the database. It extends PreparedStatement and adds support for OUT and INOUT parameters.

Example

Java Application ↓ CallableStatement ↓ {CALL procedure_name(?, ?, ?)} Database Server ↓ Executes stored procedure ↓ Returns results via OUT params or ResultSet Java Application

Calling Procedures with IN Parameters

java example

import java.sql.*;

public class CallableStatementDemo {
    private static final String URL = "jdbc:mysql://localhost:3306/java_course?useSSL=false&serverTimezone=UTC";
    
    // Simple procedure call with IN parameter
    public static void getEmployee(int empId) throws SQLException {
        String sql = "{CALL get_employee(?)}"; // Standard JDBC escape syntax
        
        try (Connection conn = DriverManager.getConnection(URL, "root", "password");
             CallableStatement cstmt = conn.prepareCall(sql)) {
            
            cstmt.setInt(1, empId); // Set IN parameter
            
            try (ResultSet rs = cstmt.executeQuery()) {
                if (rs.next()) {
                    System.out.printf("Employee: %s %s | Dept: %s | Salary: $%.2f%n",
                        rs.getString("first_name"),
                        rs.getString("last_name"),
                        rs.getString("department"),
                        rs.getDouble("salary"));
                } else {
                    System.out.println("Employee not found: " + empId);
                }
            }
        }
    }
}

OUT Parameters

java example

public static int getEmployeeCount(String department) throws SQLException {
    String sql = "{CALL get_employee_count(?, ?)}";
    
    try (Connection conn = DriverManager.getConnection(URL, "root", "password");
         CallableStatement cstmt = conn.prepareCall(sql)) {
        
        // Set IN parameter
        cstmt.setString(1, department);
        
        // Register OUT parameter (must specify SQL type)
        cstmt.registerOutParameter(2, Types.INTEGER);
        
        // Execute
        cstmt.execute();
        
        // Get OUT parameter value
        int count = cstmt.getInt(2);
        System.out.println(department + " has " + count + " employees");
        return count;
    }
}

INOUT Parameters

java example

public static double adjustSalary(int empId, double raiseAmount) throws SQLException {
    String sql = "{CALL adjust_salary(?, ?)}";
    
    try (Connection conn = DriverManager.getConnection(URL, "root", "password");
         CallableStatement cstmt = conn.prepareCall(sql)) {
        
        // First param: IN (employee ID)
        cstmt.setInt(1, empId);
        
        // Second param: INOUT (pass raise amount, get new salary back)
        cstmt.setDouble(2, raiseAmount);
        cstmt.registerOutParameter(2, Types.DECIMAL);
        
        cstmt.execute();
        
        double newSalary = cstmt.getDouble(2);
        System.out.printf("Employee %d new salary: $%.2f%n", empId, newSalary);
        return newSalary;
    }
}

Calling Stored Functions

java example

public static double calculateBonus(int empId) throws SQLException {
    // Function call syntax: {? = call function_name(?)}
    String sql = "{? = CALL calculate_bonus(?)}";
    
    try (Connection conn = DriverManager.getConnection(URL, "root", "password");
         CallableStatement cstmt = conn.prepareCall(sql)) {
        
        // Register return value (first ?)
        cstmt.registerOutParameter(1, Types.DECIMAL);
        
        // Set input parameter (second ?)
        cstmt.setInt(2, empId);
        
        cstmt.execute();
        
        double bonus = cstmt.getDouble(1);
        System.out.printf("Bonus for employee %d: $%.2f%n", empId, bonus);
        return bonus;
    }
}

Multiple Result Sets

Example

public static void getDepartmentReport(String department) throws SQLException { String sql = "{CALL get_department_report(?)}"; try (Connection conn = DriverManager.getConnection(URL, "root", "password"); CallableStatement cstmt = conn.prepareCall(sql)) { cstmt.setString(1, department); boolean hasResults = cstmt.execute(); int resultSetCount = 0; while (hasResults || cstmt.getUpdateCount() != -1) { if (hasResults) { resultSetCount++; try (ResultSet rs = cstmt.getResultSet()) { if (resultSetCount == 1) { // First result set: employees System.out.println("\n=== " + department + " Employees ==="); while (rs.next()) { System.out.printf(" %s %s - $%.2f%n", rs.getString("first_name"), rs.getString("last_name"), rs.getDouble("salary")); } } else if (resultSetCount == 2) { // Second result set: statistics if (rs.next()) { System.out.println("\n=== Department Statistics ==="); System.out.printf(" Total: %d | Avg: $%.2f | Max: $%.2f | Payroll: $%.2f%n", rs.getInt("total_employees"), rs.getDouble("avg_salary"), rs.getDouble("max_salary"), rs.getDouble("total_payroll")); } } } } hasResults = cstmt.getMoreResults(); } } }

Output

\n=== " + department + " Employees ===
\n=== Department Statistics ===

Named Parameters (using index alternative)

java example

// MySQL doesn't support named parameters natively
// But you can use named indices for clarity:
public static void namedParameterPattern(String dept, double minSalary) throws SQLException {
    String sql = "{CALL search_employees(?, ?, ?)}";
    
    try (Connection conn = DriverManager.getConnection(URL, "root", "password");
         CallableStatement cstmt = conn.prepareCall(sql)) {
        
        final int PARAM_DEPT = 1;
        final int PARAM_MIN_SALARY = 2;
        final int PARAM_RESULT_COUNT = 3;
        
        cstmt.setString(PARAM_DEPT, dept);
        cstmt.setDouble(PARAM_MIN_SALARY, minSalary);
        cstmt.registerOutParameter(PARAM_RESULT_COUNT, Types.INTEGER);
        
        ResultSet rs = cstmt.executeQuery();
        while (rs.next()) {
            // Process results
        }
        
        int totalFound = cstmt.getInt(PARAM_RESULT_COUNT);
        System.out.println("Found: " + totalFound);
    }
}

Error Handling with Stored Procedures

Example

public static void robustProcedureCall() { String sql = "{CALL risky_operation(?)}"; try (Connection conn = DriverManager.getConnection(URL, "root", "password"); CallableStatement cstmt = conn.prepareCall(sql)) { cstmt.setInt(1, 100); cstmt.execute(); } catch (SQLException e) { // MySQL error codes from stored procedures switch (e.getErrorCode()) { case 1062: // Duplicate entry System.err.println("Record already exists"); break; case 1452: // Foreign key constraint System.err.println("Referenced record not found"); break; case 1644: // Custom SIGNAL from procedure System.err.println("Business rule violation: " + e.getMessage()); break; default: System.err.println("Database error [" + e.getErrorCode() + "]: " + e.getMessage()); } } }

Interview Questions

Q1: What is CallableStatement and when do you use it?

Answer: CallableStatement is used to call stored procedures and functions in the database. Use it when business logic is implemented in database procedures, for complex operations that benefit from server-side execution, or when calling legacy database procedures.

Q2: What is the syntax difference between calling a procedure and a function?

Answer: Procedure: {CALL procedure_name(?, ?)}. Function: {? = CALL function_name(?)} — the first ? is the return value which must be registered as an OUT parameter.

Q3: Explain IN, OUT, and INOUT parameters.

Answer: IN parameters pass values to the procedure (like method arguments). OUT parameters receive values from the procedure (like return values). INOUT parameters both send a value in and receive a modified value back. OUT/INOUT must be registered with registerOutParameter().

Q4: How do you handle multiple result sets from a stored procedure?

Answer: Call execute(), then loop using getMoreResults(). Check if current result is a ResultSet (getResultSet()) or an update count (getUpdateCount()). Loop until both return null/-1.

Q5: Why use stored procedures vs inline SQL?

Answer: Stored procedures offer: reduced network traffic (one call vs many), precompiled execution plans, centralized business logic, security (GRANT EXECUTE without table access), and atomic complex operations. Downsides: harder to version control, database-specific syntax.

Summary

In this chapter, we learned about CallableStatement in JDBC in detail. Here are the key points:

Basic introduction to the concept and why it is needed
Syntax and structure with complete examples
Internal working and best practices
Real-world applications and use cases
Common mistakes and how to avoid them
How this topic is asked in interviews

To practice these concepts, write and run the code in your IDE and verify the output. Modify each example and experiment so that you deeply understand the concept.

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for CallableStatement in JDBC.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this Java Master Course topic.

Search Terms

java-master-course, java master course, java, master, course, jdbc, callable, statement

Continue learning this concept

JDBCStatement Interface in JDBCComplete guide to JDBC Statement interface — executing static SQL, DDL operations, multiple results, batch execution, and understanding when to use Statement vs PreparedStatement.JDBCBatch Processing in JDBCMaster JDBC batch processing — Statement and PreparedStatement batching, performance optimization, error handling, and bulk data import patterns.JDBCJDBC ArchitectureDeep dive into JDBC architecture — two-tier and three-tier models, driver manager, connection pooling, DataSource, and internal execution flow.JDBCJDBC IntroductionComplete introduction to JDBC — Java Database Connectivity concepts, API overview, driver types, and how Java applications communicate with databases.JDBCJDBC ProjectsHands-on JDBC projects — Student Management System, Employee Payroll, Library Management with full working code including DAO pattern, connection pooling, and menu-driven interface.JDBCMySQL Connection with JDBCStep-by-step guide to connecting Java with MySQL — driver setup, connection URLs, properties, SSL configuration, and troubleshooting common errors.

PreviousBatch Processing in JDBC NextJDBC Architecture

Java Notes

CallableStatement in JDBC

Complete guide to CallableStatement — calling stored procedures and functions, IN/OUT/INOUT parameters, and handling multiple result sets from MySQL procedures.

What is CallableStatement?

CallableStatement is used to execute stored procedures and stored functions in the database. It extends PreparedStatement and adds support for OUT and INOUT parameters.

Example

Java Application ↓ CallableStatement ↓ {CALL procedure_name(?, ?, ?)} Database Server ↓ Executes stored procedure ↓ Returns results via OUT params or ResultSet Java Application

Calling Procedures with IN Parameters

java example

import java.sql.*;

public class CallableStatementDemo {
    private static final String URL = "jdbc:mysql://localhost:3306/java_course?useSSL=false&serverTimezone=UTC";
    
    // Simple procedure call with IN parameter
    public static void getEmployee(int empId) throws SQLException {
        String sql = "{CALL get_employee(?)}"; // Standard JDBC escape syntax
        
        try (Connection conn = DriverManager.getConnection(URL, "root", "password");
             CallableStatement cstmt = conn.prepareCall(sql)) {
            
            cstmt.setInt(1, empId); // Set IN parameter
            
            try (ResultSet rs = cstmt.executeQuery()) {
                if (rs.next()) {
                    System.out.printf("Employee: %s %s | Dept: %s | Salary: $%.2f%n",
                        rs.getString("first_name"),
                        rs.getString("last_name"),
                        rs.getString("department"),
                        rs.getDouble("salary"));
                } else {
                    System.out.println("Employee not found: " + empId);
                }
            }
        }
    }
}

OUT Parameters

java example

public static int getEmployeeCount(String department) throws SQLException {
    String sql = "{CALL get_employee_count(?, ?)}";
    
    try (Connection conn = DriverManager.getConnection(URL, "root", "password");
         CallableStatement cstmt = conn.prepareCall(sql)) {
        
        // Set IN parameter
        cstmt.setString(1, department);
        
        // Register OUT parameter (must specify SQL type)
        cstmt.registerOutParameter(2, Types.INTEGER);
        
        // Execute
        cstmt.execute();
        
        // Get OUT parameter value
        int count = cstmt.getInt(2);
        System.out.println(department + " has " + count + " employees");
        return count;
    }
}

INOUT Parameters

java example

public static double adjustSalary(int empId, double raiseAmount) throws SQLException {
    String sql = "{CALL adjust_salary(?, ?)}";
    
    try (Connection conn = DriverManager.getConnection(URL, "root", "password");
         CallableStatement cstmt = conn.prepareCall(sql)) {
        
        // First param: IN (employee ID)
        cstmt.setInt(1, empId);
        
        // Second param: INOUT (pass raise amount, get new salary back)
        cstmt.setDouble(2, raiseAmount);
        cstmt.registerOutParameter(2, Types.DECIMAL);
        
        cstmt.execute();
        
        double newSalary = cstmt.getDouble(2);
        System.out.printf("Employee %d new salary: $%.2f%n", empId, newSalary);
        return newSalary;
    }
}

Calling Stored Functions

java example

public static double calculateBonus(int empId) throws SQLException {
    // Function call syntax: {? = call function_name(?)}
    String sql = "{? = CALL calculate_bonus(?)}";
    
    try (Connection conn = DriverManager.getConnection(URL, "root", "password");
         CallableStatement cstmt = conn.prepareCall(sql)) {
        
        // Register return value (first ?)
        cstmt.registerOutParameter(1, Types.DECIMAL);
        
        // Set input parameter (second ?)
        cstmt.setInt(2, empId);
        
        cstmt.execute();
        
        double bonus = cstmt.getDouble(1);
        System.out.printf("Bonus for employee %d: $%.2f%n", empId, bonus);
        return bonus;
    }
}

Multiple Result Sets

Example

Output

\n=== " + department + " Employees ===
\n=== Department Statistics ===

Named Parameters (using index alternative)

java example

// MySQL doesn't support named parameters natively
// But you can use named indices for clarity:
public static void namedParameterPattern(String dept, double minSalary) throws SQLException {
    String sql = "{CALL search_employees(?, ?, ?)}";
    
    try (Connection conn = DriverManager.getConnection(URL, "root", "password");
         CallableStatement cstmt = conn.prepareCall(sql)) {
        
        final int PARAM_DEPT = 1;
        final int PARAM_MIN_SALARY = 2;
        final int PARAM_RESULT_COUNT = 3;
        
        cstmt.setString(PARAM_DEPT, dept);
        cstmt.setDouble(PARAM_MIN_SALARY, minSalary);
        cstmt.registerOutParameter(PARAM_RESULT_COUNT, Types.INTEGER);
        
        ResultSet rs = cstmt.executeQuery();
        while (rs.next()) {
            // Process results
        }
        
        int totalFound = cstmt.getInt(PARAM_RESULT_COUNT);
        System.out.println("Found: " + totalFound);
    }
}

Error Handling with Stored Procedures

Example

Interview Questions

Q1: What is CallableStatement and when do you use it?

Q2: What is the syntax difference between calling a procedure and a function?

Answer: Procedure: {CALL procedure_name(?, ?)}. Function: {? = CALL function_name(?)} — the first ? is the return value which must be registered as an OUT parameter.

Q3: Explain IN, OUT, and INOUT parameters.

Q4: How do you handle multiple result sets from a stored procedure?

Answer: Call execute(), then loop using getMoreResults(). Check if current result is a ResultSet (getResultSet()) or an update count (getUpdateCount()). Loop until both return null/-1.

Q5: Why use stored procedures vs inline SQL?

Summary

In this chapter, we learned about CallableStatement in JDBC in detail. Here are the key points:

Basic introduction to the concept and why it is needed
Syntax and structure with complete examples
Internal working and best practices
Real-world applications and use cases
Common mistakes and how to avoid them
How this topic is asked in interviews

To practice these concepts, write and run the code in your IDE and verify the output. Modify each example and experiment so that you deeply understand the concept.

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for CallableStatement in JDBC.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this Java Master Course topic.

Search Terms

java-master-course, java master course, java, master, course, jdbc, callable, statement

Continue learning this concept

PreviousBatch Processing in JDBC NextJDBC Architecture