INOUT Parameters – Database Connectivity

INOUT Parameters

An INOUT parameter acts as both an IN parameter to pass a value to the stored procedure or function and an OUT parameter to return a value from the stored procedure or function. Before the execution of the callable statement, its value can be initialized with a setXXX() method as for an IN parameter and can register the JDBC type of the return value by calling the registerOutputParameter() method as for an OUT parameter. The returned values can be retrieved using the appropriate getXXX() method as we have seen for OUT parameters.

Example 24.4 illustrates the basics of calling stored procedures and functions. The nitty-gritty of creating and deploying stored methods and procedures is database specific, and beyond the scope of this book.

Example 24.4 Stored Procedures and Functions

Click here to view code image
public void storedProcedureCall(Connection connection) {
    final String callProc
        = “{call longCompositionsProc(?, ?)}”;             // (1) 1: IN 2:OUT
    try (CallableStatement cStmt = connection.prepareCall(callProc)) { // (2)
      int duration = 100;                                              // (3)
      cStmt.setInt(1, duration);                                       // (4)
      cStmt.registerOutParameter(2, Types.INTEGER);                    // (5)
      cStmt.execute();                                                 // (6)
      int returnedValue = cStmt.getInt(2);                             // (7)
      System.out.println(“Compositions with duration greater than “
          + duration + “: ” + returnedValue);
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  public void storedFunctionCall(Connection connection) {
    final String callFunc
        = “? = {call longCompositionsFunc(?)}”;              // (8) 1:OUT 2:IN
    try (CallableStatement cStmt = connection.prepareCall(callFunc)) {
      cStmt.registerOutParameter(1, Types.INTEGER);                     // (9)
      int duration = 100;
      cStmt.setInt(2, duration);                                        // (10)
      cStmt.execute();
      int returnedValue = cStmt.getInt(1);                              // (11)
      System.out.println(“Compositions with duration greater than “
          + duration + “: ” + returnedValue);
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

The two methods storedProcedureCall() and storedFunctionCall(), in Example 24.4, call a stored procedure and a stored function, respectively. Both the stored procedure named longCompositionsProc and the stored function named longCompositionsFunc compute the number of rows in the compositions table whose duration is greater than the duration specified in the call. We assume that the stored procedure and function are implemented and deployed on the database side. The result returned will depend on the state of the compositions table. The numbered comments below correspond to the numbered lines in Example 24.4.

1. The stored procedure longCompositionsProc has one IN and one OUT parameter designated by the marker positions 1 and 2, respectively.
2. The prepareCall() method prepares a callable statement for the stored procedure call.
3. The variable duration is declared and initialized with the value 100.
4. The IN parameter at marker 1 is initialized with the value of the variable duration (100). We are interested in finding the number of rows with duration greater than 100.
5. The OUT parameter at marker 2 is registered to return a value of JDBC type INTEGER which will be retrieved as a value of Java type int.
6. The execute() method executes the stored procedure call in the callable statement.
7. After execution, the int value returned in the OUT parameter at marker 2 can be retrieved. Of course, this value depends on the state of the database.
8. The stored function longCompositionsCall has one IN and one OUT parameter designated by the marker positions 2 and 1, respectively.
9. Note that the OUT parameter at marker 1 is reserved for the return value from the stored function call, but its JDBC type must be registered, in this case JDBC type INTEGER.
10. The IN parameter at marker 2 is initialized with the value 100 as the lower limit for duration.
11. After execution, the returned value at marker 1 can be retrieved.