memory and lose database cursors.
9.2.2 Creating the HR Tables
You can use the program in Example 9-1 to create the tables for the HR example schema used in this book. Begin by entering the commands to create the HR database tables from Chapter 8 into separate text files. Then, if you have any errors in your SQL, it won't be so hard to correct them. Use one file per table and place a CREATE TABLE statement with all related ALTER TABLE, CREATE INDEX, and CREATE SEQUENCE statements into each file. End each command with a forward- slash character (/) on a separate line. Then compile the program in Example 9-1 and execute it for each file using the following syntax:
java ExecuteDDL filename
If you have any syntax errors in your command files, you will get a fairly informative SQL
diagnostic message from the database. Make any necessary corrections and re-execute the files. Continue that process until you have no SQL creation errors. I say creation errors, because you may encounter "object already exists" errors when you reexecute your SQL after making corrections. You can safely ignore any "object already exists" errors.
9.3 The executeUpdate( ) Method
Now that we've created some tables using the execute( ) method, we can continue by using the executeUpdate( ) method to insert, update, and delete rows in those tables. The
executeUpdate( ) method works just like the execute( ) method, except that it returns an integer value that reports the number of rows affected by the SQL statement. The
executeUpdate( ) method effectively combines the execute( ) and getUpdateCount( )
methods into one call:
int rslt = 0; Statement stmt = null; try { stmt = conn.createStatement( ); rslt = stmt.executeUpdate("delete person"); . . . }
In this example, we once again assume that a Connection object named conn already exists. The example starts by declaring the int variable rslt to hold the number of rows affected 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 using the Connection
object's createdStatement( ) method, and a reference to it is stored in stmt. Then, the
Statement object's executeUpdate( ) method is called to execute the SQL DELETE statement, returning the number of rows affected into rslt. Now that you have the general idea, let's see the executeUpdate( ) method in action.
9.3.1 Executing an INSERT, UPDATE, or DELETE Statement
Example 9-2, shows an insert , update, and delete program which uses the executeUpdate( ) method.
Example 9-2. An application to execute, insert, update, or delete DML
import java.io.*; import java.sql.*;
public class ExecuteIUD { Connection conn; public ExecuteIUD( ) { 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 {
ExecuteIUD iud = new ExecuteIUD( );
iud.executeIUD(
"(code, description, inactive_date) " + "values ('EID', 'Employee ID', NULL)");
iud.executeIUD(
"insert into PERSON_IDENTIFIER_TYPE " + "(code, description, inactive_date) " + "values ('PHONE', 'Phone Number', NULL)");
iud.executeIUD(
"insert into PERSON_IDENTIFIER_TYPE " + "(code, description, inactive_date ) " +
"values ('SSN', 'Social Socurity Number', NULL)");
iud.executeIUD(
"update PERSON_IDENTIFIER_TYPE " +
"set description = 'Social Security Number' " + "where code = 'SSN'");
iud.executeIUD(
"delete PERSON_IDENTIFIER_TYPE " + "where code = 'PHONE'");
}
public void executeIUD(String sql) throws IOException, SQLException { int rslt = 0; Statement stmt = null; System.out.println(sql); try { stmt = conn.createStatement( ); rslt = stmt.executeUpdate(sql);
System.out.println(Integer.toString(rslt) + " rows affected"); System.out.println(" "); } catch (SQLException e) { System.err.println(e.getMessage( )); } finally { 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( );
} }
Our insert, update, and delete program, ExecuteIUD, starts out in its main( ) method by instantiating a copy of itself. Then it calls the executeIUD( ) method three times to insert three identifier type codes into the PERSON_IDENTIFIER_TYPE table. These inserts are followed by an UPDATE statement to change the description for type code SSN. Finally, a DELETE
The executeIUD( ) method begins by creating two variables. One is an int named rslt that holds the return value from the executeUpdate( ) method. The other is a Statement object named stmt that is used to execute the SQL statements. The method continues by echoing the passed SQL statement to the screen. It then executes the try block, in which the SQL statement is executed.
Inside the try block, the program creates a Statement object and then proceeds to execute the passed SQL statement by using the Statement object's executeUpdate( ) method. The
executeUpdate( ) method returns the number of rows affected by the statement, and the program displays that number followed by a blank line on the screen.
If an error occurs in the try block, program execution immediately branches to the
SQLExceptioncatch clause where the Oracle SQL diagnostic error message is sent to the screen. Upon completion of the try block, the finally clause closes the Statement object if it is open.
The only notable difference between this example and the last, as if you haven't already heard this enough times already, is that the executeUpdate( ) method returns an integer value that reports the number of rows affected by the SQL statement just executed.
9.3.2 Auto-Commit
When you use executeUpdate( ) to perform your inserts, updates, and deletes, be aware that auto-commit is on by default. This means that as each SQL statement is executed, it is also committed. Effectively, each statement execution becomes its own transaction. If you are executing multiple statements, it is not efficient to commit after each one. In addition, if you are performing complex insertion processes such as those involving both parent and child tables, you probably don't want your parent rows to be inserted without the corresponding child rows also being inserted. So for reasons of both performance and transaction integrity, you may want or need to turn off auto-commit. You can do that using the Connection object's setAutoCommit( ) method, passing it a booleanfalse:
conn.setAutoCommit(false);
Once you've turned off auto-commit, you can execute any number of executeUpdate( ) calls, which will all form one transaction. Then, when you are done making all your executeUpdate( ) calls, you'll need to call the Connection object's commit( ) method to make your changes permanent.
9.3.3 Oracle and SQL92 Escape Syntax
Another issue to be concerned about when using Statement.executeUpdate( ) is that it requires you to perform rather complex string concatenations. Because executeUpdate( )
requires a String object as an input parameter, you have to convert any values stored in other data types that are required to build your SQL statements into String objects before
concatenating them to build your SQL statement. To accomplish this task, you must write your own helper functions and use either Oracle's built-in database functions or SQL92's escape syntax.
As you convert values in other data types to Strings and concatenate them into a larger
String object to represent a SQL statement, you must consider the following issues: • You must escape any use of the single quote, or tick character.
• You must convert date and time data types to strings and then wrap them with an appropriate database function to convert the string representation of the date or time values to the database's date type.
The next few sections talk about these and other issues in detail. Keep in mind that in Chapter 11, we'll cover an alternative to the Statement object, a PreparedStatement object that eliminates the need for handling these issues.
9.3.3.1 Handling ticks
You must replace any occurrences of a tick character (') within your SQL statement with double ticks (''), so they can be parsed correctly by the database. The double tick is Oracle's escape syntax for the tick character. For example, consider a SQL statement such as the following, in which a value contains a tick character:
delete person where last_name = 'O'Reilly'
Before trying to execute this statement, you must replace the tick character in O'Reilly with a double-tick character:
delete person where last_name = 'O''Reilly'