Use CallableStatements to call stored procedures

You can call the CallableStatement interface of OceanBase Connector/J 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 call method, because it does not require advance information on whether the stored procedures will return result sets.

In the following example, the stored procedure obSp returns an inOutParam value that increments on the base 1 and a string passed in by using inputParam as ResultSet.

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.

   For 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 was created), OceanBase Connector/J requires that the values of the output parameter be specified by using the registerOutputParameter() method in the CallableStatement operation. For 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. For example,

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

4. Execute CallableStatement and retrieve any result sets or output parameters.

   For example,

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