3.4 Transactions
In the previous chapter, I explained the concept of database transactions and how to use the SQL COMMIT statement to make permanent any changes you make to the database. I also showed how to use the ROLLBACK statement to undo changes made in a transaction. The same concepts apply to SQL statements executed from your SQLJ programs.
To issue the SQL COMMIT and ROLLBACK statements in a SQLJ statement, use the following syntax:
#sql { COMMIT [WORK] }; #sql { ROLLBACK [WORK] }; The syntax elements are as follows:
- COMMIT
-
Commits a transaction, making the changes permanent.
- ROLLBACK
-
Rolls back a transaction, returning the database to the state it was in when the transaction first began. The effects of all SQL statements issued during the transaction will be erased.
- WORK
-
An optional word that is part of the supported SQL syntax.
This example performs an INSERT statement followed by a ROLLBACK statement:
#sql { INSERT INTO customers (id, first_name, last_name, dob, phone) VALUES ('7', 'John', 'Smith', '01-JAN-1970', '650-555-1212') }; #sql { ROLLBACK }; Here, one row is inserted into the customers table, and that insert is undone as a result of the ROLLBACK statement.
3.4.1 Auto-Commit
By default, you must issue a COMMIT statement at the end of each transaction to make the changes permanent. However, it is also possible to have SQLJ automatically perform a commit after each DML statement. This can be convenient if your transactions consist of only one statement or if you don't want to worry about transactions at all. This functionality is referred to as auto-commit, and it may be enabled when you initially connect to a database.
To enable auto-commit, you make use of an optional fourth parameter to the oracle.sqlj.runtime.Oracle class's connect( ) method. The Boolean true/false parameter indicates whether the auto-commit feature is enabled and defaults to false. Setting it to true enables auto-commit. The following example shows you how to set auto-commit using the Oracle.connect( ) method:
Oracle.connect( "jdbc.oracle.thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password", true );
Now, all SQL statements that you issue via this connection are implicitly followed by a COMMIT. In other words, each statement is a transaction in itself. You don't need to worry about sending COMMIT statements to the database because SQLJ does it for you.
The auto-commit feature may also be enabled when creating a default connection context object. (I discuss connection contexts in detail in Chapter 9.) Remember, the auto-commit feature is disabled by default, which means that you must, by default, commit or roll back each transaction explicitly. Commits are automatic only when auto-commit is enabled.
You have now seen how to write SQLJ statements that connect to a database schema and that contain embedded SQL DML, DDL, and transaction control statements. The following section contains a complete SQLJ program that illustrates the use of such statements.
3.4.2 Example Program: FundamentalExample1.sqlj
The program FundamentalExample1.sqlj, shown in Example 3-1, is a complete SQLJ program that uses SQLJ executable statements and host expressions to access the fundamental_user database schema. The program performs the following tasks:
-
Connects to the fundamental_user schema using the Oracle.connect( ) method.
-
Adds a new row to the customers table using an INSERT statement.
-
Uses a SELECT statement to retrieve the column values for the new row that was added to the customers table, and then displays those values.
-
Removes the new row from the customers table using a DELETE statement.
-
Permanently records the transaction, which consists of the INSERT and DELETE statements, using a COMMIT statement.
-
Modifies the price column of a row in the products table using an UPDATE statement.
-
Uses a SELECT statement to retrieve the column values for the modified row in the products table, and then displays those values.
-
Undoes the transaction, which consists of the UPDATE statement, using a ROLLBACK statement.
-
Creates a new table named addresses using a CREATE TABLE statement.
-
Drops the addresses table using a DROP TABLE statement.
-
Disconnects from the database using the Oracle.close( ) method from within a finally block.
Example 3-1. FundamentalExample1.sqlj
/* The program FundamentalExample1.sqlj illustrates how to connect to a database, how to embed SQL DML operations in SQLJ executable statements, and how to use host expressions. */ // import required packages import java.sql.*; import oracle.sqlj.runtime.Oracle; public class FundamentalExample1 { public static void main(String [] args) { try { Oracle.connect( "jdbc:oracle:thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password" ); // add a new customer int customer_id = 6; String first_name = "Jerry"; String last_name = "Fieldtop"; Date dob = new Date(80, 1, 1); String phone = "650-555-1222"; #sql { INSERT INTO customers (id, first_name, last_name, dob, phone) VALUES (:customer_id, :first_name, :last_name, :dob, :phone) }; // display new customer #sql { SELECT first_name, last_name, dob, phone INTO :first_name, :last_name, :dob, :phone FROM customers WHERE id = :customer_id }; System.out.println("Customer with id " + customer_id + " has the following details:"); System.out.println(" First name: " + first_name); System.out.println(" Last name: " + last_name); System.out.println(" DOB: " + dob); System.out.println(" Phone: " + phone); // delete the customer #sql { DELETE FROM customers WHERE id = :customer_id }; // commit the transaction #sql { COMMIT }; // update the first product price int product_id = 1; double product_price = 11.25; #sql { UPDATE products SET price = :product_price WHERE id = :product_id }; // display the first product int type_id = 0; String name = null; String description = null; double price = 0.0; #sql { SELECT type_id, name, description, price INTO :type_id, :name, :description, :price FROM products WHERE id = :product_id }; System.out.println("Product with id " + product_id + " has the following details: "); System.out.println(" Type id: " + type_id); System.out.println(" Name: " + name); System.out.println(" Description: " + description); System.out.println(" Price: " + price); // rollback the update #sql { ROLLBACK }; // create a table to hold customer addresses #sql { CREATE TABLE addresses ( id NUMBER CONSTRAINT addresses_pk PRIMARY KEY, customer_id NUMBER CONSTRAINT addresses_fk_customers REFERENCES customers(id), street VARCHAR2(255) NOT NULL, city VARCHAR2(255) NOT NULL, state CHAR(2) NOT NULL, country VARCHAR2(255) NOT NULL ) }; System.out.println("Successfully created addresses table."); // drop the addresses table #sql { DROP TABLE addresses }; } catch ( SQLException e ) { System.err.println("SQLException " + e); } finally { try { Oracle.close( ); } catch ( SQLException e ) { System.err.println("SQLException " + e); } } } // end of main( ) } To translate and compile the FundamentalExample1.sqlj program, type the following command at your operating system command prompt:
sqlj FundamentalExample1.sqlj
The sqlj command-line utility translates the FundamentalExample1.sqlj file into a file named FundamentalExample1.java that contains calls to the SQLJ runtime library. Next, sqlj calls the Java complier (javac) to compile the FundamentalExample1.java file into the class file FundamentalExample1.class. Chapter 1 discusses the sqlj command-line utility and this translation process in detail. When you execute the resulting FundamentalExample1 class, you should get the following output:
Customer with id 6 has the following details: First name: Jerry Last name: Fieldtop DOB: 1980-02-01 Phone: 650-555-1222 Product with id 1 has the following details: Type id: 1 Name: Beyond Understanding Description: The frontiers of human knowledge Price: 11.25 Successfully created addresses table.
