CallableStatement Example in Java

Posted in: Callable Statement,JDBC

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.

CREATE PROCEDURE GetImmediateManager
   @employeeID INT,
   @managerID INT OUTPUT
AS
BEGIN
   SELECT @managerID = ManagerID 
   FROM HumanResources.Employee 
   WHERE EmployeeID = @employeeID
END

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.

public static void executeStoredProcedure(
Connection con) {
   try {
      CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
      cstmt.setInt(1, 5);
      cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
      cstmt.execute();
      System.out.println("MANAGER ID: " + cstmt.getInt(2));
   }
   catch (Exception e) {
      e.printStackTrace();
   }
}