10.2.3 CallableStatement Interface Explained

The CallableStatement interface in JDBC is used to execute stored procedures in a database. Stored procedures are precompiled SQL code that can be reused and called with parameters. The CallableStatement interface extends the PreparedStatement interface and provides methods to handle input, output, and input-output parameters.

Key Concepts

1. Executing Stored Procedures

The primary purpose of the CallableStatement interface is to execute stored procedures. A stored procedure is a set of SQL statements that are stored in the database and can be called by name. This allows for modular and reusable SQL code.

Example

        CallableStatement callableStatement = connection.prepareCall("{call getEmployeeName(?, ?)}");
        callableStatement.setInt(1, 123);
        callableStatement.registerOutParameter(2, Types.VARCHAR);
        callableStatement.execute();
        String employeeName = callableStatement.getString(2);
    

2. Input Parameters

Input parameters are values that are passed to the stored procedure. These parameters are set using methods like setInt(), setString(), etc., which are inherited from the PreparedStatement interface.

Example

        callableStatement.setInt(1, 123);
        callableStatement.setString(2, "John Doe");
    

3. Output Parameters

Output parameters are values that are returned from the stored procedure. These parameters are registered using the registerOutParameter() method, and their values are retrieved after the procedure is executed.

Example

        callableStatement.registerOutParameter(2, Types.VARCHAR);
        callableStatement.execute();
        String result = callableStatement.getString(2);
    

4. Input-Output Parameters

Input-output parameters are parameters that are both passed to and returned from the stored procedure. They are set as input parameters and then retrieved as output parameters after the procedure is executed.

Example

        callableStatement.setInt(1, 123);
        callableStatement.registerOutParameter(1, Types.INTEGER);
        callableStatement.execute();
        int result = callableStatement.getInt(1);
    

5. Handling Multiple Result Sets

Some stored procedures may return multiple result sets. The CallableStatement interface provides methods to handle multiple result sets, such as getMoreResults() and getResultSet().

Example

        callableStatement.execute();
        ResultSet resultSet1 = callableStatement.getResultSet();
        callableStatement.getMoreResults();
        ResultSet resultSet2 = callableStatement.getResultSet();
    

Examples and Analogies

Think of a stored procedure as a recipe in a cookbook. The CallableStatement interface is like a chef who follows the recipe step-by-step. Input parameters are like the ingredients the chef uses, output parameters are like the finished dish, and input-output parameters are like ingredients that change form during the cooking process. Handling multiple result sets is like preparing multiple dishes from the same recipe.

By mastering the CallableStatement interface, you can efficiently execute stored procedures in your Java SE 11 applications, leveraging the power of precompiled SQL code and modular database operations.