Handling Stored Procedures with Callable Statements

Callable statements are SQL statements that invoke stored procedures, which are also called database Remote Procedure Calls (RPCs). The RPC is popular in the UNIX system programming world. It is used here in the sense of invoking remote code that is stored in the database as stored procedures. Such procedures, when supported by the DBMS, enable the storing of user statements containing SQL text in the database. These procedures are usually stored for reuse from user session to user session. They are useful for embedding application logic at the database side. Another benefit of using callable statements is that after being deployed within a database, the procedure’s statements are optimized and precompiled in the database. As a result, interpreting the SQL text of stored procedures is no longer required at invocation time, and execution of the precompiled code occurs much faster. Figure 8-1 illustrates the invocation mechanism for stored procedures.

Figure 8-1: Invoking a stored procedure.

Statements that invoke stored procedures should use the JDBC CallableStatement class. A dedicated method must be called to prepare the callable statement, as shown in the following. The usual methods are then used to execute the statement.

Connection’s Method to Create a Statement for Calling Stored Procedures

CallableStatement prepareCall(String sql);

In this, the argument is of the following form:

"{? =call stored_procedure_name ?, ?, ....}"

The prepareCall(String sql) method prepares a callable statement. It returns a CallableStatement object. Why something more elaborate than a simple Statement object is needed is discussed in the following paragraph.

Stored procedures can return multiple result types because they can be composed of SQL statements that return diverse result types: result sets and update counts. The usual methods are used to retrieve these results. However, when a procedure returns both multiple results and OUT parameter values, the OUT parameters should be retrieved last. Stored procedures can be called with parameters; they provide maximum flexibility by enabling values to be passed from and to the user’s application. There are two types of such parameters: IN and OUT. IN parameters pass data to the stored procedure, and OUT parameters are values returned by the procedure code. Special JDBC methods exist to set and access these parameters. As Figure 8-2 shows, setting IN values and registering OUT parameters must be done before the callable statement is executed. Figure 8-2 illustrates how to handle the parameters of callable statements.

Figure 8-2: An overview of dealing with parameters.

After the statement has been executed, all its OUT parameters can be explored, one by one, in left-to-right order. Note that Figure 8-2 illustrates the processing that must be done for a stored procedure that doesn’t return ResultSets. If it returns a ResultSet, you would simply add a loop to fetch the result set before accessing the OUT parameters.

Setting parameters

IN and OUT parameters must be set or registered prior to executing a callable statement.

IN parameters

IN parameters receive a value from the user’s application. They are set via setXXX() methods that take two arguments: the parameter index, beginning at 1, and the value to set. The following methods are used to set values corresponding to their parameters’ specific types.

CallableStatement’s Methods for Setting IN Parameters

void setNull(int parameterIndex, int sqlType); void setBoolean(int parameterIndex, boolean x); void setByte(int parameterIndex, byte x); void setShort(int parameterIndex, short x); void setInt(int parameterIndex, int x); void setLong(int parameterIndex, long x); void setFloat(int parameterIndex, float x); void setDouble(int parameterIndex, double x); void set BigDecimal(int parameterIndex); void setString(int parameterIndex, String x); void setBytes(int parameterIndex, byte x[]); void setDate(int parameterIndex, java.sql.Date x); void setDate(int parameterIndex, java.sql.Date x, java.util.Calendar cal); void setTime(int parameterIndex, java.sql.Time x); void setTime(int parameterIndex, java.sql.Time x, java.util.Calendar cal); void setTimestamp(int parameterIndex, java.sql.Timestamp x); void setTimestamp(int parameterIndex, java.sql.Timestamp x,  java.util.Calendar cal); void setAsciiStream(int parameterIndex, java.io.InputStream x, int length); void setBinaryStream(int parameterIndex, java.io.InputStream x, int length); void setCharacterStream(int parameterIndex, java.io.Reader r, int length); void setObject(int parameterIndex, Object x); void setObject(int parameterIndex, Object x, int targetSqlType); void setObject(int parameterIndex, Object x, int targetSqlType, int scale); void setRef(int parameterIndex, Ref r); void setBlob(int parameterIndex, Blob b); void setClob(int parameterIndex, Clob c); void setArray(int parameterIndex, Array a); void setNull(int parameterIndex, int sqlType, String typeName); void setURL(int parameterIndex, java.net.URL url); void clearParameters(); void setObject(...);

The setObject() methods belong to advanced JDBC features. They allow given Java objects to be stored in the database. However, they are converted to the database target SQL data type before they are actually sent to the database.

Note 

Note that it is possible to pass database-specific abstract data types by using a driver-specific Java type and using a targetSqlType of java.sql.types.OTHER with the setObject(int parameterIndex, Object x, int targetSqlType) and the setObject(int parameter Index, Object x, int targetSqlType, int scale) methods.

Normally, parameter values remain unaffected for repeated use of a statement. When invoked, the method in void clearParameters(); immediately releases the resources used by the current parameters, and their values are cleared.

OUT parameters

OUT parameters must be registered prior to executing the callable statement. This registration is the way to specify their type. The following methods are available to register OUT parameters.

CallableStatement’s Methods for Registering OUT Parameters

void registerOutParameter(int parameterIndex, int sqlType); void registerOutParameter(int parameterIndex, int sqlType, int scale); void registerOutParameter(int parameterIndex, int sqlType, String typeName); 

In the first method of the preceding, the first argument is the parameter index, beginning at 1. The type argument must be defined in java.sql.Types.

The method in the second line of the preceding is used to register OUT parameters of type SQL numeric or decimal. The scale argument represents the desired number of digits to the right of the decimal point.

The method in the third line of the preceding enables you to specify user-named types or REF types, such as STRUCT, DISTINCT, or JAVA_OBJECT.

Accessing parameters

It is necessary to access parameters in left-to-right order and with the method that matches their type. The following methods are provided for this purpose.

CallableStatement’s Methods for Accessing Parameters

boolean wasNull(); String getString(int parameterIndex); boolean getBoolean(int parameterIndex); byte getByte(int parameterIndex); short getShort(int parameterIndex); int getInt(int parameterIndex); long getLong(int parameterIndex); float getFloat(int parameterIndex); double getDouble(int parameterIndex); java.math.BigDecimal getBigDecimal(int parameterIndex); byte[] getBytes(int parameterIndex); java.sql.Date getDate(int parameterIndex); java.sql.Time getTime(int parameterIndex); java.sql.Timestamp getTimestamp(int parameterIndex); Object getObject(int parameterIndex); Object getObject(int parameterIndex, java.util.Map); Ref getRef(int parameterIndex); Blob getBlob(int parameterIndex); Clob getClob(int parameterIndex); Array getArray(int parameterIndex); java.net.URL getURL(int parameterIndex);

When an OUT parameter has a null value, the method in the line boolean wasNull(); returns true. Note that you must call the corresponding getXXX() method before calling wasNull().

An example of handling stored procedures

Listing 8-1 illustrates how to prepare a callable statement, how to set IN parameters, how to register OUT parameters, how to execute the statement, and how to access the OUT parameters. In this example, the first parameter is an IN parameter used to pass a value to the stored procedure. The second one is an OUT parameter used to retrieve a value after the execution of the stored procedure.

Note 

I recommend that you check your database’s documentation for help with creating stored procedures. Stored procedures are the ideal place for database-specific, rich SQL code, such as Oracle’s PL-SQL or Sybase’s Transact-SQL.

Listing 8-1: Dealing with Stored Procedure Parameters

... ... Connection myConnection = DriverManager.getConnection(url,         "javauser", "hotjava"); CallableStatement myStmt = myConnection.prepareCall(         "{call my_stored_procedure ?, ?}"); myStmt.setString(1, "Hotjava"); myStmt.registerOutParameter(2, java.sql.Types.VARCHAR); int res = myStmt.executeUpdate(); String outParam = myStmt.getString(2); myStmt.close(); myConnection.close(); ... ...