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

CREATE PROCEDURE sp_test_inout_rs2(IN in_user VARCHAR(30),OUT table_count INT)
BEGIN

 SELECT table_name,table_type
 FROM information_schema.tables
 WHERE upper(table_schema)=upper(in_user);

 SELECT routine_name,routine_type
 FROM information_schema.routines
 WHERE upper(routine_schema)=upper(in_user);

 SELECT COUNT(*)
 INTO table_count
 FROM information_schema.tables
 where upper(table_schema)=upper(in_user);

END ;

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

callableStmt.registerOutParameter(2, Types.INTEGER);

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

callableStmt.setString(1, schemaName);

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

callableStmt.execute( );

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

boolean hasResults = callableStmt.execute( );

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

CallableStatement noResultStmt = connection.prepareCall("{call sp_noresult( )}");
noResultStmt.executeUpdate( );

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

ResultSet rs1 = callableStmt.getResultSet( );
while (rs1.next( ))
 System.out.println(rs1.getString("table_name") + " " +
 rs1.getString("table_type"));

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

if (callableStmt.getMoreResults( ))
{
 ResultSet rs2 = callableStmt.getResultSet( );
 while (rs2.next( ))
 System.out.println(rs2.getString(1) + " " + rs2.getString(2));
 rs2.close( );
}

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

1 private void executeProcedure(Connection connection, String sqlText)
2 throws SQLException {
3
4 CallableStatement cs = connection.prepareCall("{CALL " + sqlText + "}");
5 boolean moreResultSets = cs.execute( );
6 while (moreResultSets) {
7
8 ResultSet rs = cs.getResultSet( );
9 ResultSetMetaData rsmd = rs.getMetaData( );
10
11 StringBuffer buffer = new StringBuffer( );
12 for (int i = 1; i <= rsmd.getColumnCount( ); i++)
13 buffer.append(rsmd.getColumnName(i)).append("	");
14 System.out.println(buffer.toString( ));
15
16 while (rs.next( )) {
17 buffer.setLength(0);
18 for (int i = 1; i <= rsmd.getColumnCount( ); i++)
19 buffer.append(rs.getString(i)).append("	");
20 System.out.println(buffer.toString( ));
21 }
22
23 moreResultSets = cs.getMoreResults( );
24 }
25 }

Let's step through Example 14-21:

Line(s)	Explanation
4	Create a CallableStatement object that invokes the stored procedure text provided as an argument to the Java procedure.
5	Execute the stored procedure. The moreResultSets Boolean value will be true if the stored procedure returns any result sets.
6-24	This loop will continue to execute provided that moreResultSets is true. This means that the code within the loop will execute once for each result set returned by the stored procedure.
8-9	On line 8 we get a ResultSet object for the current result set, and on line 9 we retrieve the ResultSetMetaData object for that ResultSet.
11-14	Print out the column names for the current result set, as retrieved from the ResultSetMetaData object.
16-22	Loop through the rows of the current result set. The loop will continue for each row returned by the current result set.
18-21	Loop through each column in the current row. The getColumnCount( ) method of the ResultSetMetaData object tells us how many columns we will need to process, and we use getString( ) to retrieve the value. getString( ) will get a string representation of non-string SQL data types such as dates or numeric data.
23	Use the getMoreResults( ) method of the CallableStatement object to determine if there are more result sets. If this call returns TRue, then the CallableStatement will move to the next result set and the while loop defined on line 6 will continue, allowing us to repeat the above process for the next result set.

 

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

System.out.println("Out parameter = " + callableStmt.getInt(2));