4 - Statement

4.1    Overview

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

    Connection con = DriverManager.getConnection(url, "sunny", "");
    Statement stmt = con.createStatement();

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

4.1.2     Executing Statements Using Statement objects

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

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

4.1.5     SQL Escape Syntax in Statement Objects

An escape clause is demarcated by curly braces and a key word:

      {keyword . . . parameters . . . }

• 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

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

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

    
    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