Contents | Prev | Next | JDBCTM Guide: Getting Started

# 4 - Statement

This overview is excerpted from JDBCTM Database Access from JavaTM: 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

Copyright © 1996, 1997 Sun Microsystems, Inc. All rights reserved.