Use CallableStatements to call stored procedures

You can call the CallableStatement operation of the JDBC API to call stored procedures.

CallableStatement enables you to call statements to execute stored procedures by using the executeUpdate(), executeQuery(), or execute() method. execute() is the most flexible method, because you do not need to know in advance whether the stored procedures will return result sets.

In the following example, the stored procedure returns an inOutParam value that is incremented by 1, and uses inputParam as ResultSet to enter a string.

CREATE PROCEDURE obSp(IN inputParam VARCHAR(100),
                        INOUT inOutParam INT)
BEGIN
    DECLARE n INT;
    SET n = inOutParam + 1;
    SET inOutParam = n;

    SELECT inputParam;

    SELECT CONCAT('zhang', inputParam);
END

To call the obSp stored procedure in the example, perform the following steps:

1. Prepare a callable statement by using the Connection.prepareCall() method.

   Example:

   import java.sql.CallableStatement;
   
   ...
   
   
   
       CallableStatement cSt = conn.prepareCall("{call obSp(?, ?)}");
   
   
       cSt.setString(1, "asdfg");
   

2. Register output parameters, if any.

   To retrieve the values of the output parameters (the OUT or INOUT parameters specified when the stored procedure is created), JDBC requires that the values of the output parameter be specified by using the registerOutputParameter() method in the CallableStatement operation. Example:

   import java.sql.Types;
   ...
       cSt.registerOutParameter(2, Types.INTEGER);
   
       cSt.registerOutParameter("inOutParam", Types.INTEGER);
   ...
   

3. Set input parameters, if any.

   Input and input/output parameters are set in the same way as the PreparedStatement object. However, CallableStatement also allows you to set parameters by name. Example:

        cSt.setString(1, "asdfg");
   
        cSt.setString("inputParam", "asdfg");
   
   
        cSt.setInt(2, 1);
   
        cSt.setInt("inOutParam", 1);
   
   ...
   

4. Execute CallableStatement and search for any result sets or output parameters.

   Example:

   ...
   
       boolean obResults = cSt.execute();
   
          while (obResults) {
           ResultSet rs = cSt.getResultSet();
   
   
           ...
   
           obResults = cSt.getMoreResults();
       }
   
       int outputValue = cSt.getInt(2); 
   
       outputValue = cSt.getInt("inOutParam"); 
   
   ...