3.5.  Use JDBC PreparedStatement and CallableStatement

[Note]

Overview of Prepared Statements

Sometimes it is more convenient to use a PreparedStatement object for sending SQL statements to the database. This special type of statement is derived from the more general class, Statement.

Although PreparedStatement objects can be used for SQL statements with no parameters, you probably use them most often for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it.

The following creates a PreparedStatement object that takes two input parameters:

PreparedStatement statement = connect.prepareStatement("UPDATE PERSON SET NAME = ? WHERE ID = ?");
					

You must supply values in place of the question mark placeholders (if there are any) before you can execute a PreparedStatement object. Do this by calling one of the setter methods defined in the PreparedStatement class. The following statements supply the two question mark placeholders in the PreparedStatement named statement:

statement.setString(1, "Mikola Zaikin");
statement.setInt(2, 1);
					

The first argument for each of these setter methods specifies the question mark placeholder. In this example, setString(...) specifies the first placeholder and setInt(...) specifies the second placeholder.

After a parameter has been set with a value, it retains that value until it is reset to another value, or the method clearParameters() is called. Using the PreparedStatement object statement, the following code fragment illustrates reusing a prepared statement after resetting the value of one of its parameters and leaving the other one the same:

statement.setString(1, "Mikola Zaikin");
statement.setInt(2, 1);
statement.executeUpdate();

statement.setString(1, "Mikalai Zaikin");
statement.executeUpdate();
					

PreparedStatement method summary:

You can often make coding easier by using a for loop or a while loop to set values for input parameters:

connection.setAutoCommit(false);
PreparedStatement preparedStatement = connection.prepareStatement(sql);
...
for (...) {
    preparedStatement.clearParameters();
    preparedStatement.setObject(1, someValue);
    preparedStatement.execute();
}
connection.commit();
...
preparedStatement.close(); // Do this in the finally block or use try-with-resource!
					

or more efficient:

connection.setAutoCommit(false);
PreparedStatement preparedStatement = connection.prepareStatement(sql);
...
for (...) {
    preparedStatement.setObject(1, someValue);
    preparedStatement.addBatch();
}
preparedStatement.execute();
connection.commit();
...
preparedStatement.close(); // Do this in the finally block or use try-with-resource!
					

CallableStatement Overview

A CallableStatement object provides a way to call stored procedures in a standard way for all RDBMSs. A stored procedure is stored in a database; the call to the stored procedure is what a CallableStatement object contains. This call is written in an escape syntax that may take one of two forms: one form with a result parameter, and the other without one. A result parameter, a kind of OUT parameter, is the return value for the stored procedure. Both forms may have a variable number of parameters used for input (IN parameters), output (OUT parameters), or both (INOUT parameters). A question mark serves as a placeholder for a parameter.

The syntax for invoking a stored procedure using the JDBC API is shown here. Note that the square brackets indicate that what is between them is optional; they are not themselves part of the syntax.

{call procedure_name[(?, ?, ...)]}
					

The syntax for a procedure that returns a result parameter is:

{? = call procedure_name[(?, ?, ...)]}
					

The syntax for a stored procedure with no parameters would look like this:

{call procedure_name}
					

CallableStatement inherits Statement methods, which deal with SQL statements in general, and it also inherits PreparedStatement methods, which deal with IN parameters. All of the methods defined in CallableStatement deal with OUT parameters or the output aspect of INOUT parameters: registering the JDBC types of the OUT parameters, retrieving values from them, or checking whether a returned value was JDBC NULL. Whereas the getXXX methods defined in ResultSet retrieve values from a result set, the getXXX methods in CallableStatement retrieve values from the OUT parameters and/or return value of a stored procedure.

Professional hosting         Free 'Oracle Certified Expert Web Services Developer 6' Guide     Free SCDJWS 5.0 Guide