DDL Statements and Transactions

This final column in my Learning SQLJ series explores how to add SQLJ statements to your Java programs that use embedded SQL Data Definition Language (DDL) statements. DDL statements are used to create and modify database objects, and consist of statements such as CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, CREATE SQUENCE, and so forth.

These columns reference numerous SQL scripts, source code, and other files that are available for download at O'Reilly's Web site. For more specific information about the files used, or to get an introduction to SQLJ, read my first column, "Setting Up Your Environment to Develop SQLJ Programs."

Using Data Definition Language Statements

In an SQLJ program, you execute a DDL statement just as you would any other SQL statement —- you enclose it within a #sql token. The following example illustrates the use of the CREATE TABLE statement to create a table named addresses that will be used to store 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
  )
};

Transactions

In my previous column, I explained the concept of database transactions, and how to use the SQL COMMIT statement to make any changes you make to the database permanent. 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:

The following 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 };

In this example, one row will be inserted into the customers table. Then that insert will be undone as a result of the ROLLBACK statement.

Related Reading Java Programming with Oracle SQLJ By Jason Price Table of Contents Index Sample Chapter Read Online--Safari Search this book on Safari:   Only This Book All of Safari Code Fragments only

Auto-Commit

By default, you must issue a COMMIT statement at the end of each transaction in order 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' connect() method; this boolean true/false parameter indicates whether the auto-commit feature is enabled, and defaults to false. You enable auto-commit by setting it to true. The following is an example that 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
);

All SQL statements that you issue via this connection will be implicitly followed by a COMMIT. In other words, each statement will be a transaction in itself. You don't need to worry about sending COMMIT statements to the database, because SQLJ will do that for you.

The auto-commit feature may also be enabled when creating a default connection context object. Remember, the auto-commit feature is disabled by default, which means that you must, by default, commit or rollback each transaction explicitly. Commits are only automatic 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.

Example Program: FundamentalExample1.sqlj

The program FundamentalExample1.sqlj 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:

1. Connects to the fundamental_user schema using the Oracle.connect() method.
2. Adds a new row to the customers table using an INSERT statement.
3. 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.
4. Removes the new row from the customers table using a DELETE statement.
5. Permanently records the transaction, which consists of the INSERT and DELETE statements, using a COMMIT statement.
6. Modifies the price column of a row in the products table, using an UPDATE statement.
7. Uses a SELECT statement to retrieve the column values for the modified row in the products table, and then displays those values.
8. Undoes the transaction, which consists of the UPDATE statement, using a ROLLBACK statement.
9. Creates a new table named addresses using a CREATE TABLE statement.
10. Drops the addresses table using a DROP TABLE statement.
11. Disconnects from the database using the Oracle.close() method from within a finally block.

Here's the code for FundamentalExample1.sqlj (you can also download this code here):

/*
   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, the sqlj command-line utility calls the Java complier (javac) to compile the FundamentalExample.java file into the class file FundamentalExample.class. My first column in this series discusses the sqlj command-line utility and this translation process in detail. When you execute the resulting FundamentalExample 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.

I hope you've enjoyed reading this series on learning SQLJ. For more information on SQLJ, pick up a copy of Java Programming with Oracle SQLJ.

Jason Price is currently employed as a product manager with Oracle Corporation. He has contributed to many products at Oracle, including the database server, the application server, and several CRM applications.

Read more Learning SQLJ columns.

Return to ONJava.com.

Copyright © 2007 O'Reilly Media, Inc.