Using Stored Programs in JDBC

So far we have mainly reviewed the JDBC calls that can be used with any database and that don't relate in any way to stored program calls. If you have used JDBC with other RDBMS types or with previous versions of MySQL, you probably haven't learned much. Let's move on to processing stored program calls in JDBC (Figure 14-2).

Stored program calls are very similar to standard JDBC calls. A stored program strongly resembles a prepared statement that executes a query, with the following exceptions:

• A stored program can return more than one result set.
• A stored procedure can be associated with outputas well as inputparameters. This means that we need a way to retrieve the altered values from any stored procedure parameters that are defined as OUT or INOUT.

In addition to the general sequence of processing involved in creating and executing a prepared statement, when executing a stored program, we may need to retrieve multiple result sets and alsowhen the stored program execution has completedretrieve the results of any output variables.

14.2.1. Using the CallableStatement Interface

The CallableStatement interface extends the PreparedStatement interface. It includes all of the methods of the PreparedStatement interface, as well as additional methods specific to stored program calls. You create a CallableStatement with the prepareCall( ) method of a Connection object:

CallableStatement statementName = ConnectionName.prepareCall(sql_text);

The single argument to the prepareCall( ) method contains the MySQL statements required to invoke the stored program. Any parameters are indicated by ? characters. The entire call must be enclosed in braces, "{" and "}", which are the standard JDBC escape sequences for indicating database-independent syntax. So to call the stored procedure sp_test_inout_rs2, which has two parameters, we would use the following syntax:

Figure 14-2. JDBC program flow when executing a stored program

CallableStatement callableStmt = myConnection.prepareCall("{CALL sp_test_inout_rs2(?,?)}");

sp_test_inout_rs2 is a stored procedure that has both an IN and an OUT parameter and that returns two result sets. The stored procedure takes the name of a MySQL schema as an IN argument and returns a list of tables and a list of stored routines owned by that schema. It returns the number of tables in the specified database as an OUT parameter. The text for this stored procedure is shown in Example 14-13.

Example 14-13. Example stored procedure used in Java examples

14.2.2. Registering OUT Variables

If the stored procedure includes any OUT variables, you need to identify these to JDBC. The registerOutParameter( ) instance method of CallableStatement allows you to identify these parameters. This method has the following syntax:

callableStatementInstance.registerOutParameter(parameter_number,data_type);

Parameters are identified by number, starting with 1 for the first parameter. The data types are those contained in java.sql.Types and include INTEGER, CHAR, NUMERIC, DATE, etc.

In sp_test_inout_rs2, our second parameter is an OUT integer parameter, so we issue the statement to identify the parameter in Example 14-14.

Example 14-14. Registering a stored procedure OUT or INOUT parameter

14.2.3. Supplying Input Parameters

No matter how many times we execute our stored procedure, we only have to create the CallableStatement and register output parameters once. However, most executions of a stored procedure will have different input parameters , so the first step in a new execution is to identify the values of those parameters. The syntax for setting input parameter values is the same as that for a PreparedStatement; we use the setInt( ), setFloat( ), setString( ), setDate( ), or other appropriate methods of the PreparedStatement interface to set each value. In our example stored procedure, we have only a single VARCHAR input parameter, so we set its value as shown in Example 14-15.

Example 14-15. Setting the value of an input parameter

schemaName is a Java String containing the name of the schema for which we want to retrieve information.

14.2.4. Executing the Procedure

Now we are ready to execute the stored procedure, which we do with the execute( ) instance method shown in Example 14-16.

Example 14-16. Executing a stored procedure

The execute( ) method returns a Boolean value, which resolves to true if the stored procedure returns at least one result set. So we could call execute( ) as shown in Example 14-17.

Example 14-17. Executing a stored procedure that might return a result set

If you know that your stored procedure does not return a result set, you can use the executeUpdate( ) method instead, as shown in Example 14-18.

Example 14-18. Executing a stored procedure that does not return a result set

14.2.5. Retrieving a Result Set

As we noted earlier, the initial execute( ) call will return true only if the stored procedure returns at least one result set. If this is so, or if you know in advance that the stored procedure has a result set, you can retrieve it in the usual fashion. Example 14-19 shows how to retrieve a single result set from a stored procedure call.

Example 14-19. Retrieving a single result set from a stored procedure call

In this case, we knew the names and types of the columns in our result set. If we did not, we could call the getMetaData( ) method to retrieve the result set structure. ResultSetMetaData is described in the section "Getting Result Set Metadata" earlier in this chapter.

14.2.6. Retrieving Multiple Result Sets

If the stored procedure has more than one result set, you can use the getMoreResults( ) method to move to the next set. If there are no more result sets , getMoreResults( ) will return false. So to get a second result set, we can call getMoreResults( ) and then retrieve the result set. Example 14-20 illustrates this technique.

Example 14-20. Obtaining a second result set from the stored procedure call

In this example, we used the column numbers rather than column names to retrieve the results. Using column names (rs2.getString("department_id") for instance) leads to more readable code, but when you are processing dynamic result sets, it may be more convenient to refer to the columns by number.

14.2.7. Dynamically Processing Result Sets

It is possiblebut very unusualthat we might call a stored program without knowing the number and types of input and output parameters. However, because we often use unbounded SELECT statements within stored programs to generate debugging or other messages, and because it is relatively easy to conditionally create result sets in our stored program code, we may find that we need to execute a stored program without knowing exactly how many result sets will be returned or what the structure of each result set will look like.

We therefore need to be familiar with the process of dynamically processing result sets. Example 14-21 implements a method that will execute a stored program passed as a parameter and print out all the result sets generated by that stored program.

Example 14-21. JDBC code to dynamically process multiple result sets

Let's step through Example 14-21:

14.2.8. Retrieving Output Parameter Values

Once all of the result sets have been retrieved, it is time to retrieve the values of any OUT or INOUT parameters that the procedure may have declared. Remember that in order to do this, we must have used the registerOutParameter( ) method to set the types of these parameters before we executed the stored procedure.

To get the values of output parameters, we use "get" methods (getInt( ), getFloat( ), getString( ), etc.) that are similar to those used to retrieve column values, but instead of applying the methods to the ResultSet object, we apply them to the CallableStatement object. In the case of our sp_test_inout_rs2 stored procedure, which has a single integer OUT parameter (the second parameter), we can simply retrieve the value of the OUT parameter with the code shown in Example 14-22.

Example 14-22. Retrieving the value of an output parameter