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 updater methods (updateInt, updateBoolean, updateString, and so on) 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. There are also versions of the method Connection.createStatement that create Statement objects that produce ResultSet objects that are scrollable, that are updatable, and that remain open after a transaction is committed or rolled back.
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.
The Statement methods executeQuery and executeUpdate 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, which call one of the DBMS's stored procedures, inherit the PreparedStatement forms of these methods. Supplying an SQL statement to the PreparedStatement or CallableStatement versions of the methods executeQuery, executeUpdate, or execute 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.
Many DBMSs automatically generate a unique key field when a new row is inserted into a table. Methods and constants added in the JDBC 3.0 API make it possible to retrieve these keys, which is a two-step process. First the driver is alerted that it should make the keys available for retrieval. The second step is to access the generated keys by calling the Statement method getGeneratedKeys. The rest of this section explains these two steps more fully.
Statement objects, is when the statement is executed. Three new versions of the method executeUpdate and three new versions of the method execute signal the driver about making automatically generated keys available. These six new methods take two parameters, the first being in all cases an SQL INSERT statement. The second parameter is either a constant indicating whether to make all generated keys retrievable (Statement.RETURN_GENERATED_KEYS or Statement.NO_GENERATED_KEYS) or an array indicating which specific key columns should be made retrievable. The array elements are either the indexes of the columns to be returned or the names of the columns to be returned.
Note that although it is possible to use the method execute for executing a DML (Data Manipulation Language) statement, this method is generally reserved for executing CallableStatement objects that produce multiple return values.
PreparedStatement object, the SQL statement is sent to the DBMS to be precompiled when the PreparedStatement object is created with one of the Connection.prepareStatement methods. Thus, the driver is notified about making automatically generated keys retrievable via these methods. See the chapter on PreparedStatement objects (page 96) for examples.
Statement method getGeneratedKeys. This method returns a ResultSet object, with each row being a generated key. If there are no automaticallty generated keys, the ResultSet object will be empty.
The following code fragment creates a Statement object and signals the driver that it should be able to return any keys that are automatically generated as a result of executing the statement. The example then retrieves the keys that were generated and prints them out. If there are no generated keys, the printout says that there are none.
String sql = "INSERT INTO AUTHORS (LAST, FIRST, HOME) VALUES " +
"'PARKER', 'DOROTHY', 'USA', keyColumn";
int rows = stmt.executeUpdate(sql,
Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()) {
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
do {
for (int i = 1; i <= colCount; i++) {
String key = rs.getString(i);
System.out.println("key " + i + "is " + key);
}
}
while (rs.next();)
}
else {
System.out.println("There are no generated keys.");
}
Instead of telling the driver to make all automatically-generated keys available, it is possible to tell the driver to make particular columns retrievable. The following code fragment uses an array of column indexes (in this case, an array with one element) to indicate which columns with an automatically-generated key should be made available for retrieval.
String sql = "INSERT INTO AUTHORS (LAST, FIRST, HOME) VALUES " +
"'PARKER', 'DOROTHY', 'USA', keyColumn";
int [] indexes = {4};
int rows = stmt.executeUpdate(sql, indexes);
The following code fragment shows a third alternative-supplying an array of column names to indicate which ResultSet columns to make available. In this case, the driver is told to make the automatically-generated key in the column AUTHOR_ID retrievable.
String sql = "INSERT INTO AUTHORS (LAST, FIRST, HOME) VALUES " +
"'PARKER', 'DOROTHY', 'USA', keyColumn";
String [] keyColumn = {"AUTHOR_ID"};
int rows = stmt.executeUpdate(sql, keyColumn);
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:
The percent sign (%) and underscore (_) 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:
{escape 'escape-character'}
For example, the following query, using the backslash character as an escape character, finds identifier names that begin with an underbar.
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.
DBMSs differ in the syntax they use for date, time, and timestamp literals. The JDBC API supports ISO standard format for the syntax of these literals, using an escape clause that the driver must translate to the DBMS representation. For example, a date is specified in a JDBC SQL statement with the following syntax:
{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.
If a database supports stored procedures, they can be invoked from JDBC with the syntax shown below. Note that the square brackets ([ ]) 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 103 for more information.
One can call the method DatabaseMetaData.supportsStoredProcedures to see if the database supports stored procedures.
The syntax for an outer join is:
{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.
;"'PARKER', 'DOROTHY', 'USA', keyColumn";
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.
The method 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 89, the overview of the PreparedStatement interface, regarding precompilation.
A Statement object may 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 Java 2 platform, 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();
If one of the commands in a batch update fails, the method executeBatch will throw a BatchUpdateException. The BatchUpdateException method getUpdateCounts can be called to get an array of the update counts that were returned. In the previous examples, as soon as a command in a batch failed, the driver stopped processing commands, so the array contained update counts for only those commands that were executed before the first failure. A driver may be implemented so that it continues to process subsequent commands instead of stopping with a failure. In this case, the array of update counts returned by the method getUpdateCounts will contain a value for every command in the batch. The value for a command that failed is Statement.EXECUTE_FAILED.
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 for that particular ResultSet object only. See the section "Providing Performance Hints" on page 72 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.