Brought to you by EarthWeb
ITKnowledge Logo Login Graphic Free CD from Sqribe Technologies, Click Here
Free CD from Sqribe Technologies, Click Here
ITKnowledge
Search this book:
 
Search the site:
 
EXPERT SEARCH ----- nav

EarthWeb Direct

EarthWeb Direct

EarthWeb sites: other sites

Previous Table of Contents Next


The method getQueryTimeout() returns the number of seconds corresponding to the time-out limit. A zero value also means there is no limit and that the driver can wait forever if necessary. Listing 5-13 shows time-outs.

Listing 5-13: Time-outs.

// time-outs
...
...
Connection myConnection =
  DriverManager.getConnection(url, “javauser”, “hotjava”);
Statement myStatement = myConnection.createStatement();
// we do not want to wait forever
myStatement.setQueryTimeout(10);
int res = myStatement.executeUpdate(“UPDATE
  employees SET salary = 1000000 WHERE name=’jones’”);
...
...

Another property may prove useful when sending data to the database: the setting for the escape sequence.

Statement

void setEscapeProcessing(boolean enable);

This statement enables or disables escape substitution by the driver. Escape substitution is the default behavior and occurs before sending the SQL statement to the database. When enabled, the driver translates escape syntax strings to native SQL (see Chapter 6 on SQL Escape Syntax).

What We Have Done So Far

This section reviews what has been covered thus far.

  Import java.sql.* to avoid having to write long member names
  Build a JDBC URL
  Load one or more specific JDBC driver with class.forName()
  If necessary, set the JDBC log stream with setLogStream()
  If necessary, adjust the connection properties
  Open a connection with getConnection()
  Create a statement object
  Build a SQL statement
  Execute the SQL statement
  Close the statement
  Terminate the connection with close()

Example

Here is an example for executing a statement. It sends a SQL query to a database with a time-out value of 180 seconds. Listing 5-14 shows the code for executing a statement.

Listing 5-14: Executing a statement.

// executing a statement
import java.sql.*;
class SimpleExample
{
  public static void main(String args[])
  {
  String url = “jdbc:odbc:mysource”;
         try
         {
         Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
         DriverManager.setLogStream(
                 java.lang.System.out);
                 Connection myConnection =
                 DriverManager.getConnection(url,
                         “javauser”, “hotjava”);
         Statement myStatement =
                         myConnection.createStatement();
         myStatement.setQueryTimeout(180);
         ResultSet rs = myStatement.executeQuery(
                   “SELECT * FROM employees”);
         myStatement.close();
                   myConnection.close();
         }
         catch(java.lang.Exception ex)
         {
                   ex.printStackTrace();
         }
  }
}

Handling Results

A DBMS usually returns results after executing a statement. The results are of different types. The executeQuery() method returns a result set object, and the executeUpdate() returns an integer. Of course, because the result of the executeQuery() method will always be used within the program, it would be useless to discard the result of a SQL query. In the case of an update, the result should also be used, at least to verify that everything happened as expected and to discover how many rows of data were affected by the update.

Fetchable Result Types

The SQL clauses listed in Table 5-1 return known result types: rows of data and/or integer value.

Table 5-1 DIFFERENT STATEMENTS RETURN DIFFERENT RESULT TYPES

Type of SQL statement Type returned
CREATE TABLE employees (...) nothing
SELECT * FROM employees rows and integer
SELECT MAX(salary) FROM employees rows and integer
UPDATE employees SET salary = 70000
WHERE name = “Jones” integer
INSERT INTO employees VALUES (...) integer
DELETE FROM employees
WHERE salary > 150000 integer

In summary, there are two possible result set return types: an integer or a result set composed of rows of table data. Of course, there are different methods for handling such results, and there is even a way to discover the result type of an unknown SQL query.

Getting the Result Set

SQL statements that return an integer such as DELETE, UPDATE, and INSERT do not need additional processing. The method to send them returns an integer and is usually interpreted as a counter. Other SQL statements do not return rows of data or a counter.

This is not the case with queries that return normal rows of data. The result set will be composed of zero or more rows coming from the database. The following step scans this result set, row by row, until all rows have been fetched. This operation is done within a loop, as shown in Figure 5-6. We will see later how to analyze the data that compose rows.


Figure 5-6:  Overview of getting the result set.

The result set object is created when sending the statement to the DBMS. It is created by executing the statement object. Closing the result set releases all data associated with the result set.

Retrieving Rows

Result sets are composed of rows. The ResultSet.next() method is used in the loop to access these rows.

ResultSet

boolean next();

Figure 5-7 illustrates the mechanism used to scan the rows in the result set.


Figure 5-7:  The next() method is used to scan a result set.

boolean next();

It is important to position the cursor before the first row of the result set. The method next() needs to be called first to access the first row. After the first call, the first row becomes the current row, and it is ready to be processed. Successive calls to next() will make the next rows current, row by row, of course. Listing 5-15 shows how to scan a result set.

Listing 5-15: How to scan a result set.

...
Connection myConnection = DriverManager.getConnection(url,
               “javauser”, “hotjava”);
Statement myStatement = myConnection.createStatement();
ResultSet rs = myStatement.executeQuery(sqlQuery);
while (rs.next())
{
  // we got a row
}
myStatement.close();
myConnection.close();
...
...


Previous Table of Contents Next
HomeAbout UsSearchSubscribeAdvertising InfoContact UsFAQs
Use of this site is subject to certain Terms & Conditions.
Copyright (c) 1996-1999 EarthWeb Inc. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.