Part III: Relational SQL
Chapter 9. Statements
Now that you have a firm understanding of how to create a Connection object for each of the four types of clients outlined in Introduction to JDBC, and you have the DDL to create the example HR database to use as a context for the chapters on relational SQL, we're ready to change our focus from the Connection object to the Statement object. The Statement
object, which you'll create using a Connection object, allows you to execute both Data
Definition Language (DDL) and Data Manipulation Language (DML) statements. The Statement
object is the most dynamic of the JDBC objects, because you can use its execute( ) method to execute any valid SQL statement. If you use the execute( ) method, you can use its return value at runtime to determine whether there is a result set and then use the Statement object's
getResultSet( ) method to retrieve the result set, or you can use the Statement object's
getUpdateCount( ) method at runtime to determine the number of rows affected by your statement. For most situations, however, you won't need that much flexibility. Instead, you'll need to insert rows into a table, update or delete rows in a table, or select rows from a table. To that end, you'll most often use one of the Statement object's other two execute methods,
executeUpdate( ) and executeQuery( ).
In this chapter, we'll start by covering how to create a Statement object from a Connection
object. Then we'll see how to use the execute( ) method to execute the DDL from Chapter 8.
We'll continue by using the executeUpdate( ) method to insert rows into our new tables. Finally, we'll use the executeQuery( ) method to query data in the database.
9.1 Creating a Statement Object
Before you can use a Statement object to execute a SQL statement, you need to create one using the Connection object's createStatement( ) method, as in the following example:
Statement stmt = null; try { stmt = conn.createStatement( ) . . . } catch (SQLException e) { . . . } finally { . . . }
In this example, we assume that a Connection object named conn already exists. In a try
block, call the Connection object's createStatement( ) method to create a new
Statement object. If an error occurs during the call, a SQLException is thrown.
Once you've created a Statement object, you can then use it to execute a SQL statement with one of its three execute methods. Select the execute method that best suits your needs:
boolean execute(String SQL)
Returns a boolean value of true if a ResultSet object can be retrieved; otherwise, it returns false. Use this method to execute SQL DDL statements or when you need to use truly dynamic SQL.
int executeUpdate(String SQL)
Returns the numbers of rows affected by the execution of the SQL statement. Use this method to execute SQL statements for which you expect to get a number of rows affected -- for example, an INSERT, UPDATE, or DELETE statement.
ResultSet executeQuery(String SQL)
Returns a ResultSet object. Use this method when you expect to get a result set, as you would with a SELECT statement.
In the sections that follow, we'll examine the use of these three methods in detail. So let's start with the execute( ) method.
9.2 The execute( ) Method
The execute( ) method is the most generic method you can use to execute a SQL statement in JDBC. To execute a SQL statement with the execute method, call it by passing it a valid SQL statement as a String object, or as a string literal, as shown in the following example:
boolean isResultSet = false; Statement stmt = null;
try {
stmt = conn.createStatement( );
isResultSet = stmt.execute("select 'Hello '||USER from dual"); . . .
}
In this example, we assume that Connection object conn already exists. First, a boolean
variable named isResultSet is created to hold the return value from the call to the execute( ) method. Next, a variable named stmt is created to hold a reference to the Statement object. In the try block, the Statement object is created with a call to the Connection object's
createStatement( ) method. Then, the Statement object's execute( ) method is called passing a SQL SELECT statement. Since this is a SELECT statement, the execute( ) method returns a boolean true to indicate that a result set is available. You can then call the Statement
object's getResultSet( ) method to retrieve the ResultSet object that contains the data from the database. For example:
boolean isResultSet = false; Statement stmt = null;
ResultSet rslt = null; try {
stmt = conn.createStatement( );
isResultSet = stmt.execute("select 'Hello '||USER from dual"); if (isResultSet) {
rslt = stmt.getResultSet( ); }
. . . }
We'll cover result sets in great detail in Chapter 10.
If an INSERT, UPDATE, or DELETE SQL statement is passed to execute( ), the method will return a booleanfalse, indicating that no result set is available. In that case, call the
Statement object's getUpdateCount( ) method to retrieve the number of rows that were affected by the SQL statement. For example:
boolean isResultSet = false; int rslt = null;
Statement stmt = null; try {
stmt = conn.createStatement( );
isResultSet = stmt.execute("delete person"); if (!isResultSet) {
rslt = stmt.getUpdateCount( ); }
. . . }
If a DDL statement had been passed to the execute( ) method, it too would have returned false. However, since no result set was created, nor were any rows affected by DDL, there is nothing more to do after the execute( ) method is called.
If an error occurs during a call to the execute( ) method, a SQLException is thrown. This means that each call to a method from the Statement object requires you to use a try block or declare that the method from which you are calling a Statement object's method throws a
SQLException.
Now that you have the necessary background to use the Statement object's execute( )
method, let's use it to execute the DDL we created in Chapter 8.
9.2.1 Executing DDL
In Chapter 8, we documented the DDL statements required to create the objects for our HR database. We will now execute those statements via JDBC. To do this, we need to choose an appropriate execute method. A DDL statement to create a database object does not affect any rows, nor does it return a result set. Consequently, the execute( ) method is the best candidate for executing our DDL.
Example 9-1 shows a sample program that reads and executes SQL statements contained in a text file. Specify the name of the SQL command file on the command line when you run the program. The program allows each SQL statement in the file to span one or more lines and
expects each SQL statement to be terminated with a forward slash character (/) on a separate line following the statement.
Example 9-1. An application that executes DDL statements from a file
import java.io.*; import java.sql.*;
public class ExecuteDDL { Connection conn;
public ExecuteDDL( ) { 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 {
if (args.length < 1) {
System.err.println("Usage: java ExecuteDDL <dml file>"); System.exit(1);
}
new ExecuteDDL( ).process(args[0]); }
public void process(String fileName) throws IOException, SQLException {
boolean rslt = false;
BufferedReader in = new BufferedReader(new FileReader(fileName)); Statement stmt = null;
StringBuffer sql = new StringBuffer(1024 ); String line = null;
while ((line = in.readLine( )) != null) { System.out.println(line);
if (line.length( ) == 1 && line.indexOf("/") > -1) { try { stmt = conn.createStatement( ); rslt = stmt.execute(sql.toString( )); System.out.println("OK"); System.out.println(" "); } catch (SQLException e) { System.err.println(e.getMessage( )); } finally { if (stmt != null)
try { stmt.close( ); } catch (SQLException ignore) { } }
} else { sql.append(line); sql.append(" "); } } System.out.println(sql); in.close( ); }
protected void finalize( ) throws Throwable {
if (conn != null)
try { conn.close( ); } catch (SQLException ignore) { } super.finalize( );
} }
Our DDL execution program is named ExecuteDDL. In its main( ) method, it first verifies that a parameter has been passed on the command line. Then it creates an anonymous instance of itself and executes that instance's process method. The filename parameter is passed to the
process( ) method, which then parses and executes the DDL contained in the specified file. A database connection is made when the ExecuteDDL( ) object instantiates itself. Its default constructor, ExecuteDDL( ), loads the Oracle driver and then connects to the database using the DriverManager.getConnection( ) method.
The process( ) method begins by allocating five variables:
rslt
A boolean to receive the return value from the execute( ) method
in
A BufferedReader object used to read the contents of a SQL command file
stmt
A Statement object to execute the DDL
sql
A StringBuffer object used to hold a SQL statement read from the SQL command file
line
A String to hold the results of the BufferedReader.readLine( ) method The process( ) method continues by entering a while loop in which lines are read from the specified SQL command file until the end of the file has been reached. Inside the while loop, the method performs the following steps:
1. The current SQL statement is echoed to the screen.
2. An if statement tests to see if the line has a length of 1 and contains a forward-slash (/) character. If these conditions are met, the current statement in the buffer is executed. 3. If the conditions in step 2 are not met, the current input line is appended to the
If step 2 indicates that a complete SQL statement has been read into the buffer, the if statement will execute a try block. Inside the try block, the following steps are taken to execute the SQL statement contained in the buffer:
1. A Statement object is created using the Connection object's createStatement( )
method.
2. The SQL statement is executed using the Statement object's execute( ) method. The current contents of the StringBuffer object named sql are passed as a String
parameter to that method.
3. To give the user of the program a warm fuzzy feeling that everything is working as expected, the word "OK" followed by a blank line is displayed.
If an error occurs inside the try block, execution branches immediately to the catch clause following the try block. There, the code prints the current error message to the screen. Upon completion of the try block, regardless of whether an exception occurs, the finally clause closes the Statement object if it exists (an error could occur prior to the instantiation of the
Statement object). The sql buffer is then reinitialized to hold another SQL statement. When there are no more SQL statement lines to read, the while loop ends. Any partial, unexecuted SQL statement still in the buffer is displayed, and the BufferedReader object is then closed. The program terminates after calling the finalize( ) method, which closes the database connection.
There are some very important points to note about the code in Example 9-1. First, in the
process( ) method, the Statement variable stmt is declared outside the try block. This is done so that the stmt variable is accessible in the finally clause. Had it been declared inside the try block, it would be out of the scope of the catch and finally clauses. Second, the
finally clause guarantees that any open Statement object is closed regardless of whether the statement executed correctly or failed and threw an exception.