DDL Statements and Transactions
Pages: 1, 2

Example Program: FundamentalExample1.sqlj

Download 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

Also in Learning SQLJ: DML Statements Database Connections and SQLJ Statements Setting Up Your Environment to Develop SQLJ Programs

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.