JDBC Tutorial - JDBC CallableStatement Stored Procedures

The CallableStatement interface can accept runtime input parameters.

CallableStatement is used to execute database stored procedures.

Connection object can also create the CallableStatement to call a database stored procedure.

Example

Suppose, we have the following MySQL stored procedure.

DELIMITER $$

DROP PROCEDURE IF EXISTS `EMP`.`getEmpName` $$

CREATE PROCEDURE `EMP`.`getEmpName` 
   (IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255))
BEGIN
   SELECT first INTO EMP_FIRST FROM Emp WHERE ID = EMP_ID;
END $$

DELIMITER ;

The stored procedure above defines two parameters, one is EMP_ID and the other is EMP_FIRST. It returns the first name of an employee by employee id.

IN and OUT before the parameter name tells the types of the parameters. IN is for data input and OUT is for data output.

In the code above we pass IN the employee id and get OUT the first name.

We can also have INOUT parameters which can both accept value and pass value out.

There are totally three types of parameters: IN, OUT, and INOUT.

Both PreparedStatement and CallableStatement can accept parameters.

The PreparedStatement object only uses the IN parameter. The CallableStatement object can use all three.

Parameter	Description
IN	A parameter whose value is unknown when the SQL statement is created. We pass values to IN parameters with the `setXXX()` methods.
OUT	A parameter whose value is returned from the SQL statement. We get values from the OUT parameters with the `getXXX()` methods.
INOUT	A parameter can pass value in and out. We bind variables with the setXXX() methods and retrieve values with the getXXX() methods.

The following code shows how to call the stored procedure.

conn is an object of Connection.

CallableStatement cstmt = null;
String SQL = "{call getEmpName (?, ?)}";
cstmt = conn.prepareCall (SQL);

Close CallableStatement Obeject

We need to close the CallableStatement object to free up the resource.

Closing the Connection object first it will close the CallableStatement object as well.

CallableStatement cstmt = null;
String SQL = "{call getEmpName (?, ?)}";
cstmt = conn.prepareCall (SQL);
cstmt.close();