17.5 Creating Callable Statements | Core Servlets and Javaserver Pages: Core Technologies, Vol. 1 (2nd Edition)

With a CallableStatement , you can execute a stored procedure or function in a database. For example, in an Oracle database, you can write a procedure or function in PL/SQL and store it in the database along with the tables. Then, you can create a connection to the database and execute the stored procedure or function through a CallableStatement .

A stored procedure has many advantages. For instance, syntax errors are caught at compile time instead of at runtime; the database procedure may run much faster than a regular SQL query; and the programmer only needs to know about the input and output parameters, not the table structure. In addition, coding of the stored procedure may be simpler in the database language than in the Java programming language because access to native database capabilities (sequences, triggers, multiple cursors ) is possible.

One disadvantage of a stored procedure is that you may need to learn a new database-specific language (note, however, that Oracle8i Database and later support stored procedures written in the Java programming language). A second disadvantage is that the business logic of the stored procedure executes on the database server instead of on the client machine or Web server. The industry trend has been to move as much business logic as possible from the database and to place the business logic in JavaBeans components (or, on large systems, Enterprise JavaBeans components ) executing on the Web server. The main motivation for this approach in a Web architecture is that the database access and network I/O are often the performance bottlenecks.

Calling a stored procedure in a database involves the six basic steps outlined below and then described in detail in the following subsections.

Define the call to the database procedure. As with a prepared statement, you use special syntax to define a call to a stored procedure. The procedure definition uses escape syntax, where the appropriate ? defines input and output parameters.
Prepare a CallableStatement for the procedure. You obtain a CallableStatement from a Connection by calling prepareCall .
Register the output parameter types. Before executing the procedure, you must declare the type of each output parameter.
Provide values for the input parameters. Before executing the procedure, you must supply the input parameter values.
Execute the stored procedure. To execute the database stored procedure, call execute on the CallableStatement .
Access the returned output parameters. Call the corresponding get Xxx method, according to the output type.

Define the Call to the Database Procedure

Creating a CallableStatement is somewhat similar to creating a PreparedStatement (see Section 17.4, "Using Prepared Statements") in that special SQL escape syntax is used in which the appropriate ? is replaced with a value before the statement is executed. The definition for a procedure takes four general forms.

Procedure with no parameters.
```
 
```
```
 { call  procedure_name  } 
```

Procedure with input parameters.

 { call  procedure_name  (?, ?, ...) }

Procedure with an output parameter.
```
 
```
```
 { ? call  procedure_name  } 
```

Procedure with input and output parameters.

 { ? = call  procedure_name  (?, ?, ...) }

In each of the four procedure forms, the procedure_name is the name of the stored procedure in the database. Also, be aware that a procedure can return more than one output parameter and that the indexed parameter values begin with the output parameters. Thus, in the last procedure example above, the first input parameter is indexed by a value of 2 (not 1).

Core Note

If the procedure returns output parameters, then the index of the input parameters must account for the number of output parameters.

Prepare a CallableStatement for the Procedure

You obtain a CallableStatement from a Connection with the prepareCall method, as below.

 String procedure = "{ ? = call procedure_name( ?, ? ) }"; CallableStatement statement =   connection.prepareCall(procedure);

Register the Output Parameter Types

You must register the JDBC type of each output parameter, using registerOutParameter , as follows ,

 statement.registerOutParameter(  n, type  );

where n corresponds to the ordered output parameter (using 1-based indexing), and type corresponds to a constant defined in the java.sql.Types class ( Types.FLOAT , Types.DATE , etc.).

Provide Values for the Input Parameters

Before executing the stored procedure, you replace the marked input parameters by using a set Xxx call corresponding to the entry you want to set and the type of parameter (e.g., setInt , setString ). For example,

 statement.setString(2, "name"); statement.setFloat(3, 26.0F);

sets the first input parameter (presuming one output parameter) to a String , and the second input parameter to a float . Remember that if the procedure has output parameters, the index of the input parameters starts from the first output parameter.

Execute the Stored Procedure

To execute the stored procedure, simply call execute on the CallableStatement object. For example:

 statement.execute();

Access the Output Parameters

If the procedure returns output parameters, then after you call execute , you can access each corresponding output parameter by calling get Xxx , where Xxx corresponds to the type of return parameter ( getDouble , getDate , etc.). For example,

 int value = statement.getInt(1);

returns the first output parameter as a primitive int .

Example

In Listing 17.11, the CallableStatements class demonstrates the execution of an Oracle stored procedure (technically, a function, since it returns a value) written for the music table (see Section 18.5 for setting up the music table). You can create the discount stored procedure in the database by invoking our CallableStatements class and specifying create on the command line. Doing so calls the createStoredFunction method, which submits the procedure (a long string) to the database as an SQL update. Alternatively, if you have Oracle SQL*Plus, you can load the procedure directly from discount.sql , Listing 17.12. See Section 18.5 for information on running the SQL script in SQL*Plus.

The stored procedure discount modifies the price entry in the music table. Specifically, the procedure accepts two input parameters, composer_in (the composer to select in the music table) and discount_in (the percent by which to discount the price). If the discount_in is outside the range 0.05 to 0.50, then a value of -1 is returned; otherwise , the number of rows modified in the table is returned from the stored procedure.

Listing 17.11 CallableStatements.java

 package coreservlets; import java.sql.*; import coreservlets.beans.*; /** An example that executes the Oracle stored procedure  *  "discount". Specifically, the price of all compositions  *  by Mozart in the "music" table are discounted by  *  10 percent.  *  <P>  *  To create the stored procedure, specify a command-line  *  argument of "create".  */ public class CallableStatements {   public static void main(String[] args) {     if (args.length < 5) {       printUsage();       return;     }     String vendor = args[4];     // Change to DriverUtilities2.loadDrivers() to force     // loading of vendor drivers from default XML file.     DriverUtilities.loadDrivers();     if (!DriverUtilities.isValidVendor(vendor)) {       printUsage();       return;     }     String driver = DriverUtilities.getDriver(vendor);     String host = args[0];     String dbName = args[1];     String url =       DriverUtilities.makeURL(host, dbName, vendor);     String username = args[2];     String password = args[3];     Connection connection =       ConnectionInfoBean.getConnection(driver, url,                                        username, password);     if (connection == null) {       return;     }     try {       if ((args.length > 5) && (args[5].equals("create"))) {         createStoredFunction(connection);       }       doCallableStatement(connection, "Mozart", 0.10F);     } catch(SQLException sqle) {       System.err.println("Problem with callable: " + sqle);     } finally {       try {         connection.close();       } catch(SQLException sqle) {         System.err.println("Error closing connection: " + sqle);       }     }   }   private static void doCallableStatement(Connection connection,                                           String composer,                                           float discount)       throws SQLException {     CallableStatement statement = null;     try {  connection.prepareCall("{ ? = call discount( ?, ? ) }");   statement.setString(2, composer);   statement.setFloat(3, discount);   statement.registerOutParameter(1, Types.INTEGER);   statement.execute();   int rows = statement.getInt(1);  System.out.println("Rows updated: " + rows);     } catch(SQLException sqle) {       System.err.println("Problem with callable: " + sqle);     } finally {       if (statement != null) {         statement.close();       }     }   }   /** Create the Oracle PL/SQL stored procedure "discount".    *  The procedure (technically, a PL/SQL function, since a    *  value is returned), discounts the price for the specified    *  composer in the "music" table.    */   private static void createStoredFunction(                         Connection connection)       throws SQLException {     String sql = "CREATE OR REPLACE FUNCTION discount " +                  "  (composer_in IN VARCHAR2, " +                  "   discount_in IN NUMBER) " +                  "RETURN NUMBER " +                  "IS " +                  "  min_discount CONSTANT NUMBER:= 0.05; " +                  "  max_discount CONSTANT NUMBER:= 0.50; " +                  "BEGIN " +                  "  IF discount_in BETWEEN min_discount " +                  "                 AND max_discount THEN " +                  "    UPDATE music " +                  "    SET price = price * (1.0 - discount_in) "+                  "    WHERE composer = composer_in; " +                  "    RETURN(SQL%ROWCOUNT); " +                  "  ELSE " +                  "    RETURN(-1); " +                  "  END IF; " +                  "END discount;";     Statement statement = null;     try {       statement = connection.createStatement();       statement.executeUpdate(sql);     } catch(SQLException sqle) {       System.err.println("Problem creating function: " + sqle);     } finally {       if (statement != null) {         statement.close();       }     }   }   private static void printUsage() {     System.out.println("Usage: CallableStatement host " +                        "dbName username password " +                        "vendor [create].");   } }

Listing 17.12 discount.sql (PL/SQL function for Oracle)

 /* Discounts the price of all music by the specified  * composer, composer_in. The music is discounted by the  * percentage specified by discount_in.  *  * Returns the number of rows modified, or -1 if the discount  * value is invalid.  */ CREATE OR REPLACE FUNCTION discount   (composer_in IN VARCHAR2, discount_in IN NUMBER) RETURN NUMBER IS   min_discount CONSTANT NUMBER:= 0.05;   max_discount CONSTANT NUMBER:= 0.50; BEGIN   IF discount_in BETWEEN min_discount AND max_discount THEN     UPDATE music     SET price = price * (1.0 - discount_in)     WHERE composer = composer_in;     RETURN(SQL%ROWCOUNT);   ELSE     RETURN(-1);   END IF; END discount;