![]() | 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.
A Statement object is used to
send SQL statements to a database. There are actually three kinds
of Statement objects, all of which act as containers
for executing SQL statements on a given connection:
Statement, PreparedStatement, which
inherits from Statement, and
CallableStatement, which inherits from
PreparedStatement. They are specialized for sending
particular types of SQL statements; a Statement object
is used to execute a simple SQL statement with no parameters, a
PreparedStatement object is used to execute a
precompiled SQL statement with or without IN parameters, and a
CallableStatement object is used to execute a call to
a database stored procedure.
The Statement interface
provides basic methods for executing statements and retrieving
results. The PreparedStatement interface adds methods
for dealing with IN parameters; the CallableStatement
interface adds methods for dealing with OUT parameters.
In the JDBC 2.0 core API, the
ResultSet interface has a set of new
updateXXX methods and other new related methods that
make it possible to update table column values programmatically.
This new API also adds methods to the Statement
interface (and PreparedStatement and
CallableStatement interfaces) so that update
statements may be executed as a batch rather than singly.
Once a connection to a particular database
is established, that connection can be used to send SQL statements.
A Statement object is created with the
Connection method createStatement, as in
the following code fragment:
Connection con = DriverManager.getConnection(url, "sunny", ""); Statement stmt = con.createStatement();
The SQL statement that will be sent to the
database is supplied as the argument to one of the
execute methods on a Statement object.
This is demonstrated in the following example, which uses the
method executeQuery:
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table2");
The variable rs references a result set
that cannot be updated and in which the cursor can move only
forward, which is the default behavior for ResultSet
objects. The JDBC 2.0 core API adds a new version of the method
Connection.createStatement that makes it possible to
create Statement objects that produce result sets that
are scrollable and/or updatable.
The Statement interface
provides three different methods for executing SQL statements:
executeQuery, executeUpdate, and
execute. The correct method to use is determined by
what the SQL statement produces.
The method executeQuery is
designed for statements that produce a single result set, such as
SELECT statements.
The method executeUpdate is
used to execute INSERT, UPDATE, or
DELETE statements and also SQL DDL (Data Definition
Language) statements like CREATE TABLE,
DROP TABLE, and ALTER TABLE.
The effect of an INSERT, UPDATE, or
DELETE statement is a modification of one or more
columns in zero or more rows in a table. The return value of
executeUpdate is an integer (referred to as the update
count) that indicates the number of rows that were affected. For
statements such as CREATE TABLE or
DROP TABLE, which do not operate on rows,
the return value of executeUpdate is always zero.
The method execute is used
to execute statements that return more than one result set, more
than one update count, or a combination of the two. Because it is
an advanced feature that the majority of programmers will never
use, it is explained in its own section later in this overview.
All of the methods for executing
statements close the calling Statement object's
current result set if there is one open. This means that any
processing of the current ResultSet object needs to be
completed before a Statement object is
re-executed.
It should be noted that the
PreparedStatement interface, which inherits all of the
methods in the Statement interface, has its own
versions of the methods executeQuery,
executeUpdate and execute.
Statement objects do not themselves contain an SQL
statement; therefore, one must be provided as the argument to the
Statement.execute methods.
PreparedStatement objects do not supply an SQL
statement as a parameter to these methods because they already
contain a precompiled SQL statement. CallableStatement
objects inherit the PreparedStatement forms of these
methods. Supplying a parameter to the
PreparedStatement or CallableStatement
versions of these methods will cause an SQLException
to be thrown.
When a connection is in auto-commit mode,
the statements being executed within it are committed or rolled
back when they are completed. A statement is considered complete
when it has been executed and all its results have been returned.
For the method executeQuery, which returns one result
set, the statement is completed when all the rows of the
ResultSet object have been retrieved. For the method
executeUpdate, a statement is completed when it is
executed. In the rare cases where the method execute
is called, however, a statement is not complete until all of the
result sets or update counts it generated have been retrieved.
Some DBMSs treat each statement in a
stored procedure as a separate statement; others treat the entire
procedure as one compound statement. This difference becomes
important when auto-commit is enabled because it affects when the
method commit is called. In the first case, each
statement is individually committed; in the second, all are
committed together.
Statement objects will be
closed automatically by the Java garbage collector. Nevertheless,
it is recommended as good programming practice that they be closed
explicitly when they are no longer needed. This frees DBMS
resources immediately and helps avoid potential memory
problems.
Statement objects may
contain SQL statements that use SQL escape syntax. Escape syntax
signals the driver that the code within it should be handled
differently. When escape processing is enabled (by calling
Statement.setEscapeProcessing(true) or
RowSet.setEscapeProcessing(true)), the driver will
scan for any escape syntax and translate it into code that the
particular database understands. This makes escape syntax
DBMS-independent and allows a programmer to use features that might
not otherwise be available.
An escape clause is demarcated by curly braces and a key word, which indicates the kind of escape clause.
{keyword . . . parameters . . . }
The following keywords are used to identify escape clauses:
%) and
underscore underbar (_) wildcard>(_)
characters work like wild cards in SQL LIKE clauses
(% matches zero or more characters, and _
matches exactly one character). In order to interpret them
literally, they can be preceded by a backslash (\),
which is a special escape character in strings. One can
specify which character to use as the escape character by including
the following syntax at the end of a query:
For example, the following query, using the backslash character as an escape character, finds identifier names that begin with an underbar.{escape 'escape-character'}
stmt.executeQuery("SELECT name FROM Identifiers
WHERE Id LIKE '\_%' {escape '\'}");
Almost all DBMSs have numeric, string, time, date, system,
and conversion functions on scalar values. One of these functions
can be used by putting it in escape syntax with the keyword
fn followed by the name of the desired function and
its arguments. For example, the following code calls the function
concat with two arguments to be concatenated:
{fn concat("Hot", "Java")};
The name of the current database user can
be obtained with the following syntax:
{fn user()};
Scalar functions may be supported by
different DBMSs with slightly different syntax, and they may not be
supported by all drivers. Various DatabaseMetaData
methods will list the functions that are supported. For example,
the method getNumericFunctions returns a
comma-separated list of the Open Group CLI names of numeric
functions, the method getStringFunctions returns
string functions, and so on. The driver will
either map the escaped function call into the appropriate syntax or
implement the function directly itself. However, a driver is
required to implement only those scalar functions that the DBMS
supports.
{d 'yyyy-mm-dd'}
In this syntax, yyyy is the year, mm
is the month, and dd is the day. The driver will
replace the escape clause with the equivalent DBMS-specific
representation. For example, the driver might replace {d
1999-02-28} with '28-FEB-99' if that is the
appropriate format for the underlying database. There are analogous
escape clauses for TIME and
TIMESTAMP:
{t 'hh:mm:ss'}
{ts 'yyyy-mm-dd hh:mm:ss.f . . .'}
The fractional seconds (.f . .
.) portion of the TIMESTAMP can be
omitted.[ ]) indicate that what is between them is
optional, and they are not part of the syntax.
{call procedure_name[(?, ?, . . .)]}
or, where a procedure returns a result parameter:
{? = call procedure_name[(?, ?, . . .)]}
Input arguments may be either literals or
parameters. See the section "Numbering of Parameters" on page
81 for more information. One can
call the method
DatabaseMetaData.supportsStoredProcedures to see if
the database supports stored procedures.
{oj outer-join}
In this syntax, outer-join
has the form
table {LEFT|RIGHT|FULL} OUTER JOIN {table | outer-join}
ON search-condition
(Note that curly braces ({})
in the preceding line indicate that one of the items between them
must be used; they are not part of the syntax.) The following
SELECT statement uses the escape syntax for an outer
join.
Statement stmt = con.createStatement("SELECT * FROM {oj TABLE1
LEFT OUTER JOIN TABLE2 ON DEPT_NO = 003420930}");
Outer joins are an advanced feature and
are not supported by all DBMSs; consult the SQL grammar for an
explanation of them. JDBC provides three
DatabaseMetaData methods for determining the kinds of
outer joins a driver supports: supportsOuterJoins,
supportsFullOuterJoins, and
supportsLimitedOuterJoins.
Statement.setEscapeProcessing turns
escape processing on or off, with the default being on. A
programmer might turn it off to cut down on processing time when
performance is paramount, but it would normally be turned on. It
should be noted that the method setEscapeProcessing
does not work for PreparedStatement objects because
the statement may have already been sent to the database before it
can be called. See page
69, the overview of the PreparedStatement
interface, regarding precompilation.
The batch update facility provided by
the JDBC 2.0 core API allows a Statement object to
submit multiple update commands together as a single unit, or
batch, to the underlying DBMS. This ability to submit multiple
updates as a batch rather than having to send each update
individually can improve performance greatly in some
situations.
The following code fragment
demonstrates how to send a batch update to a database. In this
example, a new row is inserted into three different tables in order
to add a new employee to a company database. The code fragment
starts by turning off the Connection object con's
auto-commit mode in order to allow multiple statements to be sent
together as a transaction. After creating the
Statement object stmt, it adds three SQL
INSERT INTO commands to the batch with
the method addBatch and then sends the batch to the
database with the method executeBatch. The code looks
like this:
Statement stmt = con.createStatement();
con.setAutoCommit(false);
stmt.addBatch("INSERT INTO employees VALUES (1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO departments VALUES (260, 'Shoe')");
stmt.addBatch("INSERT INTO emp_dept VALUES (1000, '260')");
int [] updateCounts = stmt.executeBatch();
Because the connection's auto-commit mode is disabled, the application is free to decide whether or not to commit the transaction if an error occurs or if some of the commands in the batch fail to execute. For example, the application may not commit the changes if any of the insertions fail, thereby avoiding the situation where employee information exists in some tables but not in others.
In the JDBC 2.0 core API, a
Statement object is created with an associated list of
commands. This list is empty to begin with; commands are added to
the list with the Statement method
addBatch. The commands added to the list must all
return only a simple update count. If, for example, one of the
commands is a query (a SELECT statement), which will
return a result set, the method executeBatch will
throw a BatchUpdateException. A Statement
object's list of commands can be emptied by calling the method
clearBatch on it.
In the preceding example, the method
executeBatch submits stmt's list of commands to the
underlying DBMS for execution. The DBMS executes each command in
the order in which it was added to the batch and returns an update
count for each command in the batch, also in order. If one of the
commands does not return an update count, its return value cannot
be added to the array of update counts that the method
executeBatch returns. In this case, the method
executeBatch will throw a
BatchUpdateException. This exception keeps track of
the update counts for the commands that executed successfully
before the error occurred, and the order of these update counts
likewise follows the order of the commands in the batch.
In the following code fragment, an
application uses a try/catch block, and
if a BatchUpdateException is thrown, it retrieves the
exception's array of update counts to discover which commands in a
batch update executed successfully before the
BatchUpdateException object was thrown.
try {
stmt.addBatch("INSERT INTO employees VALUES (" +
"1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO departments VALUES (260, 'Shoe')");
stmt.addBatch("INSERT INTO emp_dept VALUES (1000, '260')");
int [] updateCounts = stmt.executeBatch();
} catch(BatchUpdateException b) {
System.err.println("Update counts of successful commands: ");
int [] updateCounts = b.getUpdateCounts();
for (int i = 0; i < updateCounts.length; i ++) {
System.err.print(updateCounts[i] + " ");
}
System.err.println("");
}
If a printout was generated and looked similar to the following, the first two commands succeeded and the third one failed.
Update counts of successful commands: 1 1
JDBC drivers are not required to
support batch updates, so a particular driver might not implement
the methods addBatch, clearBatch, and
executeBatch. Normally a programmer knows whether a
driver that he/she is working with supports batch updates, but if
an application wants to check, it can call the
DatabaseMetaData method
supportsBatchUpdates to find out. In the following
code fragment, a batch update is used only if the driver supports
batch updates; otherwise, each update is sent as a separate
statement. The connection's auto-commit mode is disabled so that in
either case, all the updates are included in one
transaction.
con.setAutoCommit(false);
if(dbmd.supportsBatchUpdates) {
stmt.addBatch("INSERT INTO . . .");
stmt.addBatch("DELETE . . .");
stmt.addBatch("INSERT INTO . . .");
. . .
stmt.executeBatch();
} else {
System.err.print("Driver does not support batch updates; ");
System.err.println("sending updates in separate statements.");
stmt.executeUpdate("INSERT INTO . . .");
stmt.executeUpdate("DELETE . . .");
stmt.executeUpdate("INSERT INTO . . .");
. . .
con.commit();
The Statement interface
contains two methods for giving performance hints to the driver:
setFetchDirection and setFetchSize. These
methods are also available in the ResultSet interface
and do exactly the same thing. The difference is that the
Statement methods set the default for all of the
ResultSet objects produced by a particular
Statement object, whereas the ResultSet
methods can be called any time during the life of the
ResultSet object to change the fetch direction or the
fetch size. See the section "Providing Performance Hints" on page
54 for a full discussion of these methods.
Both the Statement and
ResultSet interfaces have the corresponding
get methods: getFetchDirection and
getFetchSize. If
Statement.getFetchDirection is called before a fetch
direction has been set, the value returned is
implementation-specific, that is, it is up to the driver. The same
is true for the method Statement.getFetchSize.
The execute method should
be used only when it is possible that a statement may return more
than one ResultSet object, more than one update count,
or a combination of ResultSet objects and update
counts. These multiple possibilities for results, though rare, are
possible when one is executing certain stored procedures or
dynamically executing an unknown SQL string (that is, unknown to
the application programmer at compile time). For example, a user
might execute a stored procedure (using a
CallableStatement object), and that stored procedure
could perform an update, then a select, then an update, then a
select, and so on. In more typical situations, someone using a
stored procedure will already know what it returns.
Copyright © 1993, 2010, Oracle and/or its affiliates. All rights reserved. Please send comments using this Feedback page. |
Java Technology |