Statements (Java Enterprise in a Nutshell)

2.5. Statements

Once you have created a Connection, you can begin using it to execute SQL statements. This is usually done via Statement objects. There are actually three kinds of statements in JDBC:

Statement: Represents a basic SQL statement
PreparedStatement: Represents a precompiled SQL statement, which can offer improved performance
CallableStatement: Allows JDBC programs complete access to stored procedures within the database itself

We're just going to discuss the Statement object for now; PreparedStatement and CallableStatement are covered in detail later in this chapter.

To get a Statement object, you call the createStatement() method of a Connection:

Statement stmt = con.createStatement();

Once you have created a Statement, you use it to execute SQL statements. A statement can either be a query that returns results or an operation that manipulates the database in some way. If you are performing a query, use the executeQuery() method of the Statement object:

ResultSet rs = stmt.executeQuery("SELECT * FROM CUSTOMERS");

Here we've used executeQuery() to run a SELECT statement. This call returns a ResultSet object that contains the results of the query (we'll take a closer look at ResultSet in the next section).

Statement also provides an executeUpdate() method, for running SQL statements that do not return results, such as the UPDATE and DELETE statements. executeUpdate() returns an integer that indicates the number of rows in the database that were altered.

If you don't know whether a SQL statement is going to return results (such as when the user is entering the statement in a form field), you can use the execute() method of Statement. This method returns true if there is a result associated with the statement. In this case, the ResultSet can be retrieved using the getResultSet() method and the number of updated rows can be retrieved using getUpdateCount():

Statement unknownSQL = con.createStatement();
if(unknownSQL.execute(sqlString)) {
  ResultSet rs = unknownSQL.getResultSet();
  // display the results
} 
else {
  System.out.println("Rows updated: " + unknownSQL.getUpdateCount());
}

It is important to remember that a Statement object represents a single SQL statement. A call to executeQuery(), executeUpdate(), or execute() implicitly closes any active ResultSet associated with the Statement. In other words, you need to be sure you are done with the results from a query before you execute another query with the same Statement object. If your application needs to execute more than one simultaneous query, you need to use multiple Statement objects. As a general rule, calling the close() method of any JDBC object also closes any dependent objects, such as a Statement generated by a Connection or a ResultSet generated by a Statement, but well-written JDBC code closes everything explicitly.

2.5.1. Multiple Result Sets

It is possible to write a SQL statement that returns more than one ResultSet or update count (exact methods of doing so vary depending on the database). The Statement object supports this functionality via the getMoreResults() method. Calling this method implicitly closes any existing ResultSet and moves to the next set of results for the statement. getMoreResults() returns true if there is another ResultSet available to be retrieved by getResultSet(). However, the method returns false if the next statement is an update, even if there is another set of results waiting farther down the line. To be sure you've processed all the results for a Statement, you need to check that getMoreResults() returns false and that getUpdateCount() returns -1.

We can modify the previous execute() example to handle multiple results:

Statement unknownSQL = con.createStatement();
unknownSQL.execute(sqlString);
while (true) { 
  rs = unknownSQL.getResultSet();
  if(rs != null)
    // display the results
  else
    // process the update data
  
 // Advance and quit if done
  if((unknownSQL.getMoreResults() == false) && 
     (unknownSQL.getUpdateCount() == -1))
    break; 
}

Statements that return multiple results are actually quite rare. They generally arise from stored procedures or SQL implementations that allow multiple statements to be executed in a batch. Under SyBase, for instance, multiple SELECT statements may be separated by newline (\n) characters.