ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.


AddThis Social Bookmark Button

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 

// import required packages
import java.sql.*;
import oracle.sqlj.runtime.Oracle;

public class FundamentalExample1 {

  public static void main(String [] args) {

    try {


      // 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 {
          customers (id, first_name, last_name, dob, phone)
          (:customer_id, :first_name, :last_name, :dob, :phone)

      // display new customer
      #sql {
          first_name, last_name, dob, phone
          :first_name, :last_name, :dob, :phone
          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 {
          id = :customer_id

      // commit the transaction
      #sql { COMMIT };

      // update the first product price
      int product_id = 1;
      double product_price = 11.25;
      #sql {
          price = :product_price
          id = :product_id

      // display the first product
      int type_id = 0;
      String name = null;
      String description = null;
      double price = 0.0;
      #sql {
          type_id, name, description, price
          :type_id, :name, :description, :price
          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 {


      } 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.