CallableStatements in JDBC

Posted in: JDBC

CallableStatements

The JDBC CallableStatement interface extends PreparedStatement and provides support for output and input/output parameters. The CallableStatement interface also has support for input parameters that is provided by the PreparedStatement interface.

The CallableStatement interface allows the use of SQL statements to call stored procedures. Stored procedures are programs that have a database interface. These programs possess the following:

• They can have input and output parameters, or parameters that are both input and output.
• They can have a return value.
• They have the ability to return multiple ResultSets.

Conceptually in JDBC, a stored procedure call is a single call to the database, but the program associated with the stored procedure may process hundreds of database requests. The stored procedure program may also perform a number of other programmatic tasks not typically done with SQL statements.

Creating a CallableStatement Object

CallableStatement objects are created with the Connection method prepareCall. The following example, in which con is an active JDBC Connectionobject, creates an instance of CallableStatement.

CallableStatement cstmt = con.prepareCall(
	"{call getTestData(?, ?)}");

Three types of parameters in  CallableStatement:

• IN
  IN parameters are handled in the same manner as PreparedStatements. The various set methods of the inherited PreparedStatement class are used to set the parameters.
• OUT
  OUT parameters are handled with the registerOutParameter method. The most common form of registerOutParameter takes an index parameter as the first parameter and an SQL type as the second parameter. This tells the JDBC driver what to expect for data from the parameter when the statement is processed. There are two other variations on the registerOutParameter method that can be found in the java.sql package Javadoc.
• INOUT
  INOUT parameters require that the work for both IN parameters and OUT parameters be done. For each INOUT parameter, you must call a set method and the registerOutParameter method before the statement can be processed. Failing to set or register any parameter results in an SQLException being thrown when the statement is processed.