Tick characters are also referred to as single-quote characters.
9.4 The executeQuery( ) Method
Now that you've learned how to insert, update, and delete data in a table, it's time to learn how to use a SELECT statement to retrieve data. Whereas the execute( ) and executeUpdate( )
methods discussed in previous sections return primitive data types -- a boolean and int, respectively -- the method normally used with a SELECT statement, executeQuery( ), returns a ResultSet object. The executeQuery( ) method effectively combines the execute( )
and getResultSet( ) methods into one call:
ResultSet rset = null; Statement stmt = null; try {
stmt = conn.createStatement( );
rset = stmt.executeQuery("select last_name, first_name from person"); . . .
}
In this example, we once again assume that a Connection object, conn, already exists. The example starts out by declaring a ResultSet variable, rset, to hold the reference to the
ResultSet object generated by the SQL statement. Next, it declares a Statement variable,
stmt, to hold the reference to a Statement object. In the try block, the Statement object is created and stored in stmt using the Connection object's createdStatement( ) method. Then, the Statement object's executeQuery( ) method is called to execute the SQL SELECT statement, returning a ResultSet into rset.
A ResultSet (which we will cover in great detail in Chapter 10) is an object that has a set of
accessor methods that allow you to get to the data returned from the database. These include methods for positioning the cursor, doing in-place updates, and performing a variety of other functions.
To create a result set, we begin by creating a SQL SELECT statement in a fashion similar to how we created INSERT, UPDATE, and DELETE statements. We then call the executeQuery( )
method to execute the statement and get a ResultSet object. Take a look at the program in Example 9-5, which issues a SELECT statement to query the PERSON_IDENTIFIER_TYPE table.
Example 9-5. An application that demonstrates executeQuery( )
import java.io.*; import java.sql.*; import java.text.*;
public class ExecuteSelect { Connection conn; public ExecuteSelect( ) { try { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver( )); conn = DriverManager.getConnection(
"jdbc:oracle:thin:@dssw2k01:1521:orcl", "scott ", "tiger"); } catch (SQLException e) { System.err.println(e.getMessage( )); e.printStackTrace( ); } }
public static void main(String[] args) throws Exception, IOException {
ExecuteSelect s = new ExecuteSelect( );
s.executeSelect(
"select code, description, inactive_date " + "from PERSON_IDENTIFIER_TYPE " +
"order by code"); }
public void executeSelect(String sql) throws IOException, SQLException { Date inactive_date = null ; DateFormat df = DateFormat.getDateInstance(DateFormat.SHORT); int rows = 0; ResultSet rslt = null; Statement stmt = null; System.out.println(sql); try { stmt = conn.createStatement( ); rslt = stmt.executeQuery(sql); while (rslt.next( )) { rows++; System.out.print(rslt.getString("code") + " "); System.out.print(rslt.getString("description") + " "); inactive_date = rslt.getDate( "inactive_date");
System.out.println(df.format(inactive_date)); else
System.out.println("NULL"); }
System.out.println(Integer.toString(rows) + " rows selected"); System.out.println(" "); } catch (SQLException e) { System.err.println(e.getMessage( )); } finally { if (rslt != null)
try { rslt.close( ); } catch (SQLException ignore) { } if (stmt != null)
try { stmt.close( ); } catch (SQLException ignore) { } }
}
protected void finalize( ) throws Throwable {
if (conn != null)
try { conn.close( ); } catch (SQLException ignore) { } super.finalize( );
} }
In main( ), the program instantiates a copy of itself. The main( ) method then calls the
executeSelect( ) method, passing the SQL SELECT statement that will be executed as a parameter. In the executeSelect( ) method, the program starts by creating five variables:
inactive_date
A Date to hold the inactive date from the database for each row as the while loop moves through cursor values
df
A DateFormat used to convert the inactive date into a formatted String
rows
An int used to count the number of rows selected from the database
rslt
A ResultSet to hold the return value from the executeQuery( ) method
stmt
A Statement used to execute the SELECT statement
After the program creates its local variables, it continues by echoing the SQL statement to the screen and then enters a try block.
In the try block, the program first creates a Statement object by calling the Connection
object's createStatement( ) method. Next, the program executes the SELECT statement using the Statement object's executeQuery( ) method. This method returns a ResultSet
object that contains all the rows and columns from the database that satisfy the query. The program proceeds by entering a while loop in which the ResultSet is tested for more results. The program determines if there are more rows by calling the ResultSet object's next( )
method. If there are more results, the row count is incremented and the String values of the columns are displayed on the screen.
If an SQLException occurs during the execution of the statements in the try block, the program immediately branches to the catch clause where the Oracle diagnostic error message is displayed on the screen. Upon completion of the try block, execution branches to the
finally clause where the ResultSet and Statement objects are closed. After that, the program terminates.
Notice that I have told you nothing about the ResultSet's methods. This is because we will focus on them in Chapter 10. For now, it is important that you understand that the results from the database are accessed through the ResultSet object returned from the executeQuery( )
method.
9.4.2 Defining Columns
In Example 9-5, a program invoked the executeQuery( ) method to execute a query against the database and returned a result set. But what exactly happened when that executeQuery( ) method was called? First, the Oracle driver parsed the SQL statement. Next, it queried the database to identify the data types for the columns specified in the SELECT statement. Then it submitted the SELECT statement to the database for processing. Upon completion, the database returned the results to the driver, and the driver in turn returned a ResultSet object to the program. In this scenario, for every SELECT statement we execute, the driver must make two round trips to the database: one to get the query's metadata and another to get the query's results. If we could eliminate the first round trip to the database, we would get a 50%
improvement in efficiency and response time for a singleton -- that is, a one-row result -- query. Oracle has a proprietary solution for this problem, called defining columns, which allows you to predefine the column data types.
You can specify the column data types for a query before it is executed, thus avoiding the round trip to the database to retrieve column metadata. You specify the data type for a column using the
oracle.jdbc.driver.OracleStatement object's define-ColumnType( ) method. This proprietary method has the following signature:
defineColumnType(int column_index, int type) throws SQLException
which breaks down as:
column_index
The relative number of the column in the SELECT statement, starting with 1 and increasing from left to right
type
One of the java.sql.Types constants In our last example, we used the following query:
select code, description, inactive_date from PERSON_IDENTIFIER_TYPE
order by code
The column code in this example is column index 1, and its database data type is VARCHAR2. An appropriate java.sql.Types constant for an Oracle VARCHAR2 column would be
VARCHAR. The second column, description, is column index 2, and it would also be a
VARCHAR. The third column, inactive_date, is column index 3, and its database data type is DATE. An appropriate java.sql.Types constant for an Oracle DATE column would be
TIMESTAMP.
To use the defineColumnType( ) method, you must use an OracleStatement object instead of a Statement object or cast your Statement object to an OracleStatement, as in
Example 9-6. Notice in the example that the calls to defineColumnType( ) precede the creation of the ResultSet.
Example 9-6. An application that predefines columns
import java.io.*; import java.sql.*; import java.text.*;
import oracle.jdbc.driver.*;
public class ExecuteDefinedSelect { Connection conn;
public ExecuteDefinedSelect( ) { try {
DriverManager.registerDriver(new oracle.j dbc.driver.OracleDriver( ));
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@dssw2k01:1521:orcl", "scott", "tiger"); } catch (SQLException e) { System.err.println(e.getMessage( )); e.printStackTrace( ); } }
public static void main(String[] args) throws Exception, IOException {
ExecuteDefinedSelect s = new ExecuteDefinedSelect( );
s.executeDefinedSelect(
"select code, description, inactive_date " + "from PERSON_IDENTIFIER_TYPE " +
"order by code"); }
public void executeDefinedSelect(String sql) throws IOException, SQLException {
Date inactive_date = null; DateFormat df = DateFormat.getDateInstance(DateFormat.SHORT); int rows = 0; ResultSet rslt = null; Statement stmt = null; System.out.println(sql); try { stmt = conn.createStatement( ); ((OracleStatement)stmt).defineColumnType(1, Types.VARCHAR); ((OracleStatement)stmt).defineColumnType(2, Types.VARCHAR); ((OracleStatement)stmt).defineColumnType(3, Types.TIMESTAMP); rslt = stmt.executeQuery(sql); while (rslt.next( )) { rows++; System.out.print(rslt.getString(1) + " "); System.out.print(rslt.getString(2) + " "); inactive_date = rslt.getDate(3);
if (inactive_date != null)
System.out.println(df.format(inactive_date)); else
System.out.println("NULL"); }
System.out.println(Integer.toString(rows) + " rows selected"); System.out.println(" "); } catch (SQLException e) { System.err.println(e.getMessage( )); } finally { if (rslt != null)
try { rslt.close( ); } catch (SQLException ign ore) { } if (stmt != null)
try { stmt.close( ); } catch (SQLException ignore) { } }
}
protected void finalize( ) throws Throwable {
if (conn != null)
try { conn.close( ); } catch (SQLException ignore) { } super.finalize( );
} }
The program in Example 9-6, ExecuteDefinedSelect, is similar to that in Example 9-5 but
with two differences. First, the column types are predefined. After the Statement object is created using the Connection.createStatement( ) method, and before it is used to
execute the SELECT statement with the Statement.executeQuery( ) method, I added three calls to the OracleStatement.defineColumnType( ) method. These calls cast the JDBC 2.0 interface Statement object to Oracle's implementation of the interface, implemented by the
OracleStatement class, using the following syntax:
(OracleStatement(Statement))
The first two calls to defineColumnType( ) set the data types for the result set's code and
description columns to VARCHAR. The third call sets the inactive_date column's data type to TIMESTAMP.
The second difference between the two programs is that the use of defineColumnType( )
requires you to reference the columns by number rather than by name when you get the values; otherwise, the driver is forced to query the database for metadata. Hence, in Example 9-6, you see rslt.getString(1) instead of rslt.getString("code"). The same holds true for the other columns as well.