| Documentation Contents |
NOTE: The material in this chapter is based on JDBCtm API Tutorial and Reference, Second Edition: Universal Data Access for the Javatm 2 Platform, published by Addison Wesley as part of the Java series, ISBN 0-201-43328-1.
The PreparedStatement
interface inherits from Statement and differs from it
in two ways:
PreparedStatement contain an SQL
statement that has already been compiled. This is what makes a
statement "prepared." PreparedStatement
object may have one or more IN parameters. An IN parameter is a
parameter whose value is not specified when the SQL statement is
created. Instead, the statement has a question mark
("?") as a placeholder for each IN parameter. The "?"
is also known as a parameter marker. An application must set a
value for each question mark in a prepared statement before
executing the prepared statement. Because PreparedStatement
objects are precompiled, their execution can be faster than that of
Statement objects. Consequently, an SQL statement that
is executed many times is often created as a
PreparedStatement object to increase efficiency.
Being a subclass of
Statement, PreparedStatement inherits all
the functionality of Statement. In addition, it adds a
set of methods that are needed for setting the values to be sent to
the database in place of the placeholders for IN parameters. Also,
the three methods execute, executeQuery,
and executeUpdate are modified so that they take no
argument. The Statement forms of these methods (the
forms that take an SQL statement parameter) should never be used
with a PreparedStatement object.
The following code fragment, where con is
a Connection object, creates a
PreparedStatement object containing an SQL update
statement with two placeholders for IN parameters:
PreparedStatement pstmt = con.prepareStatement(
"UPDATE table4 SET m = ? WHERE x = ?");
The object pstmt now contains the
statement "UPDATE table4 SET m = ? WHERE x = ?", which
has already been sent to the DBMS and been prepared for
execution.
As with Statement objects,
it is possible to create a PreparedStatement object
that contains a query rather than an update statement; in fact,
this is often done to improve efficiency for SQL statements that
are executed many times. Using the new version of the method
prepareStatement included in the JDBC 2.0 core API,
the PreparedStatement object can produce
ResultSet objects that are scrollable and updatable.
For example, the following code fragment creates a
PreparedStatement object such that each time it is
executed, it will produce a ResultSet object that is
scrollable and updatable.
PreparedStatement pstmt2 = con.prepareStatement(
"SELECT a, b, c FROM Table1", ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = pstmt2.executeQuery();
The object that rs represents is a result
set with all the values stored in columns a,
b, and c of Table1, and rs
is scrollable and can be updated. Each time pstmt2 is executed, it
will produce a result set that is scrollable and updatable.
Before a PreparedStatement
object is executed, the value of each ? parameter must
be set. This is done by calling a setXXX method, where
XXX is the appropriate type for the parameter. For
example, if the parameter is of type long in the Java
programming language, the method to use is setLong.
The first argument to the setXXX methods is the
ordinal position of the parameter to be set, with
numbering starting at 1. The second argument is the value
to which the parameter is to be set. For example, the following
code sets the first parameter to 123456789 and the
second parameter to 100000000:
pstmt.setLong(1, 123456789); pstmt.setLong(2, 100000000);
Once a parameter value has been set for a
given statement, it can be used for multiple executions of that
statement until it is cleared by a call to the method
clearParameters or until a new value is set.
When a connection has its auto-commit
mode enabled, each statement is committed automatically when it is
completed. Some database systems do not retain prepared statements
across commits, so for them, the driver will have to recompile the
prepared statement after each commit. This means that for these
DBMSs, it may actually be less efficient to use a
PreparedStatement object in place of a
Statement object that is executed many times.
Using pstmt, the
PreparedStatement object created above, the following
code illustrates setting values for the two parameter placeholders
and executing pstmt 10 times. In this example, the first parameter
is set to "Hi" and remains constant. The second
parameter is set to a different value each time around the
for loop, starting with 0 and ending with
9.
pstmt.setString(1, "Hi");
for (int i = 0; i < 10; i++) {
pstmt.setInt(2, i);
int rowCount = pstmt.executeUpdate();
}
New features in the JDBC 2.0 API make it
possible to set a parameter placeholder with an SQL3 data type, as
shown in the following example, where statistics is a
Blob object representing an SQL BLOB
value, and departments is an Array object representing
an SQL ARRAY value.
PreparedStatement pstmt = con.prepareStatement(
"UPDATE Table3 SET Stats = ? WHERE Depts = ?");
pstmt.setBlob(1, statistics);
pstmt.setArray(2, departments);
The XXX in a
setXXX method is a type in the Java programming
language. It also implicitly specifies a JDBC type because the
driver will map the Java type to its corresponding JDBC type
(following the mapping specified in "Java Types Mapped to JDBC Types" on page
106) and send that JDBC type to the database. For example,
the following code fragment sets the second parameter of the
PreparedStatement object pstmt to 44,
with a Java type of short:
pstmt.setShort(2, 44);
The driver will send 44 to
the database as a JDBC SMALLINT, which is the standard
mapping from a Java short.
It is the programmer's responsibility to
make sure that the type in the Java programming language for each
IN parameter maps to a JDBC type that is compatible with the JDBC
data type expected by the database. Consider the case where the
database expects a JDBC SMALLINT. If the method
setByte is used, the driver will send a JDBC
TINYINT to the database. This will probably work
because many database systems convert from one related type to
another, and generally a TINYINT can be used anywhere
a SMALLINT is used. However, for an application to
work with the most database systems possible, it is best to use
types in the Java programming language that correspond to the exact
JDBC types expected by the database. If the expected JDBC type is
SMALLINT, using setShort instead of
setByte will make an application more portable. The
table "Java Types Mapped to JDBC Types" in the chapter "Mapping SQL
and Java Types" can be used to determine which setXXX
method to use.
A programmer can explicitly convert an
input parameter to a particular JDBC type by using the method
setObject. This method can take a third argument,
which specifies the target JDBC type. The driver will convert the
Object in the Java programming language to the
specified JDBC type before sending it to the database.
If no JDBC type is given, the driver will
simply map the Java Object to its default JDBC type
and then send it to the database. This is similar to what happens
with the regular setXXX methods; in both cases, the
driver maps the Java type of the value to the appropriate JDBC type
before sending it to the database. The difference is that the
setXXX methods use the standard mapping, whereas the
setObject method uses the mapping to object types.
The capability of the method
setObject to accept any Java object allows an
application to be generic and accept input for a parameter at run
time. In this situation the type of the input is not known when the
application is compiled. By using setObject, the
application can accept any Java object type as input and convert it
to the JDBC type expected by the database.
The JDBC 2.0 core API includes a new
implementation of the method setObject that applies to
a user-defined type (UDT) that has been custom mapped to a class in
the Java programming language. The custom mapping of an SQL UDT is
specified in a class that implements the SQLData
interface. When a UDT instance is retrieved from the database via
the method getObject, it will be mapped to an instance
of the Java class that implemented SQLData for it.
When that custom mapped instance is passed to the method
setObject, setObject will call the
SQLOutput.writeObject method that is defined in the
appropriate SQLData implementation, thereby converting
the instance of a Java class back to an SQL UDT.
The details of custom mapping are hidden
from the user. When an application invokes the method
setObject, the value being stored will automatically
be custom mapped if there is a custom mapping for it. As a result,
code in which the method setObject performs a custom
mapping looks identical to code in which setObject
uses the standard mapping. UDTs can only be stored using the
setObject method, which is a way of ensuring that UDTs
with a custom mapping are mapped appropriately.
In all of the cases discussed so far, the
value passed to the method setObject was originally an
SQL data type that was retrieved from a table column. Before
returning it to the database, the driver needed to convert it back
to its SQL data type. If a database is one of the new generation of
Java-aware DBMSs, called a Java relational DBMS, it can store an
instance of a class defined in the Java programming language as
well as values defined in SQL. A class instance may be stored as a
serialized Java object or in some other format defined by the
DBMS.
The following example shows the use of
the method setObject to store emp, an instance of the
class Employee. After the salary field of emp is
increased by 50 per cent, emp is sent back to the database. The
column EMPLOYEE in the table PERSONNEL
stores instances of Employee.
emp.salary = emp.salary * 1.5;
PreparedStatement pstmt = con.prepareStatement(
"UPDATE PERSONNEL SET EMPLOYEE = ? WHERE EMPLOYEE_NO = 300485");
pstmt.setObject(1, emp);
pstmt.executeUpdate();
Note that the syntax in this example is the same as that in the JDBC 1.0 API and is also the same as that used to store instances of UDTs that have been custom mapped.
The setNull method allows a
programmer to send a JDBC NULL (a generic SQL
NULL) value to the database as an IN parameter. Note,
however, that one must still specify the JDBC type of the
parameter.
A JDBC NULL will also be sent
to the database when a Java null value is passed to a
setXXX method (if it takes Java objects as arguments).
The method setObject, however, can take a
null value only if the JDBC type is specified.
The methods setBytes and
setString are capable of sending unlimited amounts of
data. Sometimes, however, programmers prefer to pass in large blobs
of data in smaller chunks. This can be accomplished by setting an
IN parameter to a Java input stream. When the statement is
executed, the JDBC driver will make repeated calls to this input
stream, reading its contents and transmitting those contents as the
actual parameter data.
The JDBC 1.0 API provides two methods for
setting IN parameters to input streams:
setBinaryStream for streams containing uninterpreted
bytes and setAsciiStream for streams containing ASCII
characters. A third method, set-UnicodeStream for
streams containing Unicode characters, has been deprecated; the new
JDBC 2.0 core API method setCharacterStream should be
used in its place. These stream methods take one more argument than
the other setXXX methods because the total length of
the stream must be specified. This is necessary because some
database systems need to know the total transfer size before any
data is sent.
The following code illustrates using a stream to send the contents of a file as an IN parameter.
java.io.File file = new java.io.File("/tmp/data");
int fileLength = file.length();
java.io.InputStream fin = new java.io.FileInputStream(file);
java.sql.PreparedStatement pstmt = con.prepareStatement(
"UPDATE Table5 SET stuff = ? WHERE index = 4");
pstmt.setBinaryStream (1, fin, fileLength);
pstmt.executeUpdate();
When the statement executes, the input
stream fin will get called repeatedly to deliver up
its data.
Another way to send large IN parameters
to the database is to use SQL3 types like BLOB and
CLOB. This is different from using streams in that
BLOB and CLOB values are originally
retrieved from the database, where they were created as SQL types.
Using streams makes it possible to send the contents of a file
written in the Java programming language to the database.
The JDBC 2.0 core API provides the
ability to send multiple updates to the database for execution as a
batch. The Statement method addBatch is
given an SQL update statement as a parameter, and the SQL statement
is added to the Statement object's list of commands to
be executed in the next batch. The interface
PreparedStatement has its own version of the method
addBatch, which adds a set of parameters to the batch,
as shown in the following code fragment.
PreparedStatement pstmt = con.prepareStatement(
"UPDATE Table4 SET History = ? WHERE ID = ?");
pstmt.setClob(1, clob1);
pstmt.setLong(2, 350985839);
pstmt.addBatch();
pstmt.setClob(1, clob2);
pstmt.setLong(2, 350985840);
pstmt.addBatch();
int [] updateCounts = pstmt.executeBatch();
When the PreparedStatement
object in pstmt is executed, it will be executed twice, once with
the parameters clob1 and 350985839, and a second time
with the parameters clob2 and 350985840. If either
update command returns anything other than a single update count,
the method executeBatch will throw an exception.
Copyright © 1993, 2010, Oracle and/or its affiliates. All rights reserved. Please send comments using this Feedback page. |
 Java Technology |