Statements::JAVA-JDBC2

The PreparedStatement Interface

The PreparedStatement interface extends Statement, adding the ability to set values for parameter markers contained within the statement. PreparedStatement objects represent SQL statements that can be prepared, or precompiled, for execution once and then executed mulitple times. Parameter markers, represented by “?” in the SQL string, are used to specify input vaues to the statement that may vary at runtime.

Creating a PreparedStatement Object

An instance of PreparedStatement is created in the same manner as a Statement
object, except that the SQL command is supplied when the statement is created:

Connection conn = ds.getConnection(user, passwd);
PreparedStatement ps = conn.prepareStatement(“INSERT INTO BOOKLIST" +
"(AUTHOR, TITLE, ISBN) VALUES (?, ?, ?)”);

CODE EXAMPLE Creating a PreparedStatement object with three placeholder markers

Setting ResultSet Characteristics

As with createStatement, the method prepareStatement defines a constructor
that can be used to specify the characteristics of result sets produced by that
prepared statement.

Connection conn = ds.getConnection(user, passwd);
PreparedStatement ps = conn.prepareStatement(
“SELECT AUTHOR, TITLE FROM BOOKLIST WHERE ISBN = ?”,
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);

CODE EXAMPLE Creating a PreparedStatement object that returns forward only, updatable result sets

Setting Parameters

The PreparedStatement interface defines setter methods that are used to substitute values for each of the parameter markers in the precompiled SQL string. The names of the methods follow the pattern "set". For example, the method setString is used to specify a value for a parameter
marker that expects a string. Each of these setter methods takes at least two parameters. The first is always an int equal to the ordinal position of the parameter to be set, starting at 1. The second and any remaining parameters specify the valueto be assigned to the parameter.

PreparedStatement ps = conn.prepareStatement(“INSERT INTO BOOKLIST" +
"(AUTHOR, TITLE, ISBN) VALUES (?, ?, ?)”);
ps.setString(1, “Zamiatin, Evgenii”);
ps.setString(2, “We”);
ps.setLong(3, 0140185852);

CODE EXAMPLE Setting parameters in a PreparedStatement object A value must be provided for each parameter marker in the PreparedStatement object before it can be executed. The methods used to execute a PreparedStatement object (executeQuery, executeUpdate and execute) will throw an SQLException if a value is not supplied for a parameter marker.
The values set for the parameter markers of a PreparedStatement object are not reset when it is executed. The method clearParameters can be called to explictly clear the values that have been set. Setting a parameter with a different value will replace the previous value with the new one.

Type Conversions
The data type specified in a PreparedStatement setter method is a data type in
the Java programming language. The JDBC driver is responsible for mapping this to
the corresponding JDBC type (one of the SQL types defined in java.sql.Types) so
that it is the appropriate type to be sent to the data source. The default mapping is
specified in Appendix B TABLE B-2.

Type Conversions Using the Method setObject

The method setObject can be used to convert an object in the Java programming language to a JDBC type. The conversion is explicit when setObject is passed a Java Object and a JDBC data type. The driver will attempt to convert the Object to the specified JDBC type before passing it to the data source. If the object cannot be converted to the target type, an SQLException object is thrown.

In CODE EXAMPLE a Java Object of type Integer is being converted to the JDBC type SHORT.

Integer value = new Integer(15);
ps.setObject(1, value, java.sql.Types.SHORT);
CODE EXAMPLE 13-10 Converting an Integer object to an SQL SHORT
If setObject is called without a type parameter, the Java Object is implicitly
mapped using the default mapping for that object type.
Integer value = new Integer(15);
// value is mapped to java.sql.Types.INTEGER
ps.setObject(1, value);

CODE EXAMPLE The method setObject using the default mapping The default mapping is described in Appendix B TABLE B-4

Note – The method setObject will do custom mapping for SQL UDTs that have a
custom mapping. See Chapter 17 “Customized Type Mapping” for more
information.

Setting NULL Parameters

The method setNull can be used to set any parameter to JDBC NULL. It takes two
parameters, the ordinal position of the parameter marker, and the JDBC type of the
parameter.

ps.setNull(2, java.sql.Types.VARCHAR);

CODE EXAMPLE Setting a String parameter to JDBC NULL If a Java null is passed to any of the setter methods that take a Java object, the parameter will be set to JDBC NULL.

Describing Outputs and Inputs of a PreparedStatement Object

The method PreparedStatement.getMetaData retrieves a ResultSetMetaData object containing a description of the columns that will be returned by a prepared statement when is it executed. The ResultSetMetaData object contains a record for each column being returned. Methods in the ResultSetMetaData interface provide information about the number of columns
being returned and the characteristics of each column.

PreparedStatement pstmt = conn.prepareStatement(
"SELECT * FROM CATALOG");
ResultSetMetaData rsmd = pstmt.getMetaData();
int colCount = rsmd.getColumnCount();
int colType;
String colLabel;
for (int i = 1; i <= colCount; i++) {
colType = rsmd.getColumnType(i);
colLabel = rsmd.getColumnLabel(i);
...
}

CODE EXAMPLE Creating a ResultSetMetaData object and retrieving column information from it The method PreparedStatement.getParameterMetaData returns a ParameterMetaData object describing the parameter markers that appear in the PreparedStatement object. Methods in the ParameterMetaData interface provide information about the number of parameters and their characteristics.

PreparedStatement pstmt = conn.prepareStatement(
"SELECT * FROM BOOKLIST WHERE ISBN = ?");
...
ParameterMetaData pmd = pstmt.getParameterMetaData();
int colType = pmd.getParameterType(1);
...

CODE EXAMPLE Creating a ParameterMetaData object and retrieving parameter information from it See the API specification for more details.

Executing a PreparedStatement Object

As with Statement objects, the method used to execute a PreparedStatement object depends on the type of SQL statement being executed. If the PreparedStatement object is a query returning a ResultSet object, it should be executed with the method executeQuery. If it is a DML statement returning a row count, it should be executed with the method executeUpdate. The method execute should be used only if the return type of the statement is unknown.
If any of the PreparedStatement execute methods is called with an SQL string as a parameter, an SQLException is thrown.

Returning a ResultSet Object

CODE EXAMPLE shows a query being prepared and then executed multiple times.

PreparedStatement pstmt = conn.prepareStatement(“SELECT AUTHOR, " +
"TITLE FROM BOOKLIST WHERE SECTION = ?”);
for (int i = 1; i <= maxSectionNumber; i++) {
pstmt.setInt(1, i);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
// process the record
}
rs.close();
}
pstmt.close();

CODE EXAMPLE Preparing and executing a statement returning a result set If the statement being executed does not return a ResultSet object an SQLException is thrown by executeQuery.

Returning a Row Count

If the statement being prepared and executed is a DML or DDL operation, it should be executed using the method executeUpdate. This method returns the number of rows that the statement affected.

PreparedStatement pstmt = conn.prepare(
“update stock set reorder = ’Y’ where stock < ?”);
pstmt.setInt(1, 5);
int num = pstmt.executeUpdate();

CODE EXAMPLE Preparing and executing a statement returning an update count If the statement being executed returns a ResultSet object, an SQLException is thrown.

Using the Method execute

If the return type of a PreparedStatement object is not known, it should be
executed with the execute method. As is true with Statement objects, the
methods getResultSet and getUpdateCount can be used to retrieve a result.

PreparedStatement pstmt = conn.prepareStatement(sqlStatement);
// set any parameters the user passes
...
boolean b = pstmt.execute();
if (b == true) {
ResultSet rs = pstmt.getResultSet();
// process a ResultSet
...
}
} else {
int rowCount = pstmt.getUpdateCount();
// process row count
...
}
}