JDBC: CallableStatement
Jakob Jenkov |
A java.sql.CallableStatement is used to call stored procedures in a database.
A stored procedure is like a function or method in a class, except it lives inside the database. Some database heavy operations may benefit performance-wise from being executed inside the same memory space as the database server, as a stored procedure.
Creating a CallableStatement
You create an instance of a CallableStatement
by calling the prepareCall() method on a connection object. Here is an example:
CallableStatement callableStatement =
connection.prepareCall("{call calculateStatistics(?, ?)}");
If the stored procedure returns a ResultSet, and you need a non-default ResultSet
(e.g. with different holdability, concurrency etc. characteristics),
you will need to specify these characteristics already when creating the CallableStatement.
Here is an example:
CallableStatement callableStatement =
connection.prepareCall("{call calculateStatistics(?, ?)}",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY,
ResultSet.CLOSE_CURSORS_OVER_COMMIT
);
Setting Parameter Values
Once created, a CallableStatement is very similar to a PreparedStatement.
For instance, you can set parameters into the SQL, at the places where you put a ? .
Here is an example:
CallableStatement callableStatement =
connection.prepareCall("{call calculateStatistics(?, ?)}");
callableStatement.setString(1, "param1");
callableStatement.setInt (2, 123);
Executing the CallableStatement
Once you have set the parameter values you need to set, you are ready to execute the CallableStatement.
Here is how that is done:
ResultSet result = callableStatement.executeQuery();
The executeQuery() method is used if the stored procedure returns a ResultSet.
If the stored procedure just updates the database, you can call the executeUpdate() method instead,
like this:
callableStatement.executeUpdate();
Batch Updates
You can group multiple calls to a stored procedure into a batch update. Here is how that is done:
CallableStatement callableStatement =
connection.prepareCall("{call calculateStatistics(?, ?)}");
callableStatement.setString(1, "param1");
callableStatement.setInt (2, 123);
callableStatement.addBatch();
callableStatement.setString(1, "param2");
callableStatement.setInt (2, 456);
callableStatement.addBatch();
int[] updateCounts = callableStatement.executeBatch();
OUT Parameters
A stored procedure may return OUT parameters. That is, values that are returned instead of,
or in addition to, a ResultSet. After executing the CallableStatement you can
then access these OUT parameters from the CallableStatement object.
Here is an example:
CallableStatement callableStatement =
connection.prepareCall("{call calculateStatistics(?, ?)}");
callableStatement.setString(1, "param1");
callableStatement.setInt (2, 123);
callableStatement.registerOutParameter(1, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(2, java.sql.Types.INTEGER);
ResultSet result = callableStatement.executeQuery();
while(result.next()) { ... }
String out1 = callableStatement.getString(1);
int out2 = callableStatement.getInt (2);
It is recommended that you first process the ResultSet before trying to access any OUT
parameters. This is recommended for database compatibility reasons.
| Tweet | |
Jakob Jenkov | |
