CallableStatement

In this chapter you will learn:

How to use CallableStatement
Remember to close CallableStatement

Use CallableStatement

The CallableStatement object created from Connection is used to call a database stored procedure.

There are three types of parameters for a stored procedure.

IN
A parameter whose value is unknown when the SQL statement is created. We bind values to IN parameters with the setXXX() methods.
OUT
A parameter whose value is returned by the SQL statement. We can get the value from the OUT parameters with the getXXX() methods.
INOUT
A parameter acts as both input and output values. We can bind variables with the setXXX() methods and retrieve values with the getXXX() methods.

PreparedStatement can only use the IN parameter while CallableStatement object can use all three of them.

For INOUT parameters we have to use registerOutParameter() method to tell CallableStatement to bind value and return value.

After call stored procedure, we can get the value from the OUT and INOUT parameter with the appropriate getXXX() method.

Suppose, we have the following Oracle stored procedure, which gets the name of an employee by id.

CREATE OR REPLACE PROCEDURE //from java2s  .  co m
getName(EMP_ID IN NUMBER, E_NAME OUT VARCHAR) AS
BEGIN
   SELECT name INTO E_NAME
   FROM Employee
   WHERE ID = EMP_ID;
END;

In order to call the stored procedure above we can issuse the following Java code and pass in the SQL command to CallableStatement.

CallableStatement cstmt = null;/*  j  av  a2 s.co  m*/
try {
   String SQL = "{call getEmpName (?, ?)}";
   cstmt = conn.prepareCall (SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   . . .
}

Closing CallableStatement

We have to close the CallableStatement object to save system resource.

CallableStatement cstmt = null;//from j a  va 2s. co  m
try {
   String SQL = "{call getEmpName (?, ?)}";
   cstmt = conn.prepareCall (SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   cstmt.close();
}