JDBC^TM Guide: Getting Started

4 - Statement

This overview is excerpted from JDBC^TM Database Access from Java^TM: A Tutorial and Annotated Reference, currently in progress at JavaSoft. This book, both a tutorial and the definitive reference manual for JDBC, will be published in the spring of 1997 by Addison-Wesley Publishing Company as part of the Java series.

4.1 Overview

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; CallableStatement adds methods for dealing with OUT parameters.

4.1.1 Creating Statement Objects

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 methods for executing a Statement object:

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table2");

4.1.2 Executing Statements Using Statement objects

The Statement interface provides three different methods for executing SQL statements, executeQuery, executeUpdate, and execute. The one 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 and DROP 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 indicating the number of rows that were affected (referred to as the update count). 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 most programmers will never need, 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 one needs to complete any processing of the current ResultSet object before re-executing a Statement object.

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. Using a query parameter with the PreparedStatement or CallableStatement versions of these methods will cause an SQLException to be thrown.

4.1.3 Statement Completion

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.

4.1.4 Closing Statement Objects

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.

4.1.5 SQL Escape Syntax in Statement Objects

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. 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:

{keyword . . . parameters . . . } The keyword indicates the kind of escape clause, as shown below.

escape for LIKE escape characters

The characters "%" and "_" 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 `\'};

fn for scalar functions

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 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.

d, t, and ts for date and time literals

DBMSs differ in the syntax they use for date, time, and timestamp literals. JDBC 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.

call or ? = call for stored procedures

If a database supports stored procedures, they can be invoked from JDBC with the following syntax:

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

or, where a procedure returns a result parameter:

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

The square brackets indicate that the material enclosed between them is optional. They are not part of the syntax.
Input arguments may be either literals or parameters. See Section 7, "CallableStatement," of this JDBC Guide for more information.
One can call the method DatabaseMetaData.supportsStoredProcedures to see if the database supports stored procedures.

oj for outer joins

The syntax for an outer join is

      {oj outer-join}

where outer-join is of the form

      table LEFT OUTER JOIN {table | outer-join} ON search-condition

Outer joins are an advanced feature, and one can check 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; the default is for it to be 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 setEscapeProcessing does not work for PreparedStatement objects because the statement may have already been sent to the database before it can be called. See PreparedStatement regarding precompilation.

4.1.6 Using the Method execute

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-see CallableStatement on page 135), and that stored procedure could perform an update, then a select, then an update, then a select, and so on. Typically someone using a stored procedure will know what it returns.

Because the method execute handles the cases that are out of the ordinary, it is no surprise that retrieving its results requires some special handling. For instance, suppose it is known that a procedure returns two result sets. After using the method execute to execute the procedure, one must call the method getResultSet to get the first result set and then the appropriate getXXX methods to retrieve values from it. To get the second result set, one needs to call getMoreResults and then getResultSet a second time. If it is known that a procedure returns two update counts, the method getUpdateCount is called first, followed by getMoreResults and a second call to getUpdateCount.

Those cases where one does not know what will be returned present a more complicated situation. The method execute returns true if the result is a ResultSet object and false if it is a Java int. If it returns an int, that means that the result is either an update count or that the statement executed was a DDL command. The first thing to do after calling the method execute, is to call either getResultSet or getUpdateCount. The method getResultSet is called to get what might be the first of two or more ResultSet objects; the method getUpdateCount is called to get what might be the first of two or more update counts.

When the result of an SQL statement is not a result set, the method getResultSet will return null. This can mean that the result is an update count or that there are no more results. The only way to find out what the null really means in this case is to call the method getUpdateCount, which will return an integer. This integer will be the number of rows affected by the calling statement or -1 to indicate either that the result is a result set or that there are no results. If the method getResultSet has already returned null, which means that the result is not a ResultSet object, then a return value of -1 has to mean that there are no more results. In other words, there are no results (or no more results) when the following is true:

((stmt.getResultSet() == null) && (stmt.getUpdateCount() == -1)) If one has called the method getResultSet and processed the ResultSet object it returned, it is necessary to call the method getMoreResults to see if there is another result set or update count. If getMoreResults returns true, then one needs to again call getResultSet to actually retrieve the next result set. As already stated above, if getResultSet returns null, one has to call getUpdateCount to find out whether null means that the result is an update count or that there are no more results.

When getMoreResults returns false, it means that the SQL statement returned an update count or that there are no more results. So one needs to call the method getUpdateCount to find out which is the case. In this situation, there are no more results when the following is true:

((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1)) The code below demonstrates one way to be sure that one has accessed all the result sets and update counts generated by a call to the method execute:

stmt.execute(queryStringWithUnknownResults); while (true) { int rowCount = stmt.getUpdateCount(); if (rowCount > 0) { // this is an update count System.out.println("Rows changed = " + count); stmt.getMoreResults(); continue; } if (rowCount == 0) { // DDL command or 0 updates System.out.println(" No rows changed or statement was DDL command"); stmt.getMoreResults(); continue; } // if we have gotten this far, we have either a result set // or no more results ResultSet rs = stmt.getResultSet; if (rs != null) { . . . // use metadata to get info about result set columns while (rs.next()) { . . . // process results stmt.getMoreResults(); continue; } break; // there are no more results

Contents | Prev | Next

jdbc@wombat.eng.sun.com or jdbc-odbc@wombat.eng.sun.com