Skip to main content

JDBC CallableStatement

JDBC CallableStatement

In Java Database Connectivity (JDBC), CallableStatement is an interface that's used to execute SQL stored procedures. Stored procedures are groupings of SQL statements that perform a specific task. Using the CallableStatement interface, we can call and execute these stored procedures.

Creating a CallableStatement​

To create a CallableStatement object, we use the prepareCall() method of the Connection object. Here's the syntax:

CallableStatement stmt = con.prepareCall("{call PROCEDURE_NAME}");

Where con is a Connection object and PROCEDURE_NAME is the name of the stored procedure you want to call.

Executing Stored Procedures​

Once we have created a CallableStatement object, we can execute the stored procedure using the execute() method:

stmt.execute();

This statement will call the stored procedure we specified when creating the CallableStatement object.

Using Parameters in Stored Procedures​

Often, stored procedures will require parameters to perform their tasks. We can set these parameters using the various setter methods provided by the CallableStatement interface:

CallableStatement stmt = con.prepareCall("{call PROCEDURE_NAME(?, ?)}");
stmt.setInt(1, 100);
stmt.setString(2, "example");

In this example, the stored procedure expects two parameters: an integer and a string. We use the setInt() and setString() methods to provide these parameters. The first argument of these methods is the parameter's position in the procedure call, and the second argument is the value of the parameter.

Retrieving Results​

Stored procedures can also return results. To retrieve these results, we use the various getter methods provided by the CallableStatement interface:

CallableStatement stmt = con.prepareCall("{call PROCEDURE_NAME(?)}");
stmt.registerOutParameter(1, Types.INTEGER);
stmt.execute();
int result = stmt.getInt(1);

In this example, the stored procedure returns an integer. We first need to register the output parameter using the registerOutParameter() method, specifying the position and data type of the parameter. After executing the procedure, we retrieve the returned result using the getInt() method.

Error Handling​

When working with JDBC and the CallableStatement interface, it's important to handle any SQL exceptions that might occur. This can be done using a try-catch block:

try {
    CallableStatement stmt = con.prepareCall("{call PROCEDURE_NAME}");
    stmt.execute();
} catch (SQLException e) {
    e.printStackTrace();
}

In this example, any SQLException that occurs during the creation or execution of the CallableStatement will be caught and handled by printing the stack trace.

Closing the CallableStatement​

After we are done with our CallableStatement, it's good practice to close it to free up resources. This can be done using the close() method:

stmt.close();

In conclusion, CallableStatement provides a powerful way to use stored procedures in SQL from within Java. It offers various methods to set parameters, execute the procedures, and retrieve results. It's a valuable tool for any Java developer working with databases.