Java Programming with Oracle SQLJ: Contexts and Multithreading
Pages: 1, 2, 3, 4, 5

Example Program: ContextExample1.sqlj

This section contains a complete example program named ContextExample1.sqlj (Example 8-1) that illustrates the use of connection contexts to make multiple connections to a database. The program ContextExample1.sqlj performs the following major steps:

1. Makes two database connections: one using a call to the connect( ) method to create a default connection context, and the other using a call to getConnection( ) to create a connection context named conn_context.

2. Adds a row to the customers table using conn_context.

3. Updates customer #1 using conn_context.

4. Displays all the rows in the customers table using the default connection context. This is done by calling the program's displayCustomers( ) method. The changes made to the customers table in Steps 2 and 3 are not displayed in the output from displayCustomers( ) because those changes were made using conn_context. Remember, conn_context represents a separate connection, which has a database transaction separate from the default connection context used by the displayCustomers( ) method.

5. Switches the default connection context to conn_context by making a call to the program's setDefaultContext( ) method.

6. Displays all the rows in the customers table again via another call to displayCustomers( ). The changes are now visible because the default connection context has been switched to conn_context, and displayCustomers( ) uses the default context.

7. Rolls back the changes.

8. Closes both the conn_context connection context and the default connection context.

Example 8-1: ContextExample1.sqlj

/*
   The program ContextExample1.sqlj illustrates how to use
   DefaultContext connection context objects to make 
   multiple connections to a database.
*/
 
import java.sql.*;
import oracle.sqlj.runtime.Oracle;
import sqlj.runtime.ref.DefaultContext;
 
public class ContextExample1 {
 
  // declare the iterator class
  #sql private static iterator CustomerIteratorClass (
    int id, String first_name, String last_name
  );
 
  public static void main(String [] args) {
 
    try {
 
      // set the default connection context using the 
      // Oracle.connect(  ) method
      Oracle.connect(
        "jdbc:oracle:thin:@localhost:1521:orcl",
        "fundamental_user",
        "fundamental_password"
      );
 
      // connect to database using a second connection
      // context named conn_context
      DefaultContext conn_context = Oracle.getConnection(
        "jdbc:oracle:thin:@localhost:1521:orcl",
        "fundamental_user",
        "fundamental_password"
      );
 
      // add a row to the customers table using conn_context
      System.out.println("Adding customer Jason Price 
        using " + conn_context.");
      #sql [conn_context] {
        INSERT INTO
          customers (id, first_name, last_name)
        VALUES
          (6, 'Jason', 'Price')
      };
 
      // update the first row in the customers table 
      // using conn_context
      System.out.println("Updating customer 1 name to 
        John Doe using " + "conn_context.");
      #sql [conn_context] {
        UPDATE
          customers
        SET
          first_name = 'John',
          last_name  = 'Doe'
        WHERE
          id = 1
      };
 
      // display all rows in the customers table using 
      // the default connection context, the new row and
      // the update are not visible to the default context
      // because the row was added using conn_context transaction
      // (which has a separate database associated with it)
      displayCustomers(  );
 
      // switch the default context to conn_context
      System.out.println("Switching default connection
         context " + "to conn_context.");
      DefaultContext.setDefaultContext(conn_context);
 
      // display all the rows in the customers table again 
      // using the default connection context, the changes 
      // are now visible because the default context 
      // has been switched to conn_context
      displayCustomers(  );

      // rollback the changes
      #sql { ROLLBACK };
 
      // close the conn_context connection context
      conn_context.close(  );
 
      // close the default connection context
      Oracle.close(  );
 
    } catch ( SQLException e ) {
 
      System.err.println("SQLException " + e);
      System.exit(1);
 
    }
 
  } // end of main(  )
 
 
  private static void displayCustomers(  )
  throws SQLException {
 
    // declare a named iterator object
    CustomerIteratorClass customer_iterator;
 
    // use the default context when populating the iterator
    #sql customer_iterator = {
      SELECT
        id, first_name, last_name
      FROM
        customers
      ORDER BY
        id
    };
 
    System.out.println("List of customers using default
       connection " + "context.");
 
    // access the contents of the iterator
    while (customer_iterator.next(  )) {
 
      // display the customer
      System.out.println("Customer:");
      System.out.println("id = " +
         customer_iterator.id(  ));
      System.out.println("first_name = " +
         customer_iterator.first_name(  ));
      System.out.println("last_name = " +
         customer_iterator.last_name(  ));
 
    } // end of while loop
 
    // close the iterator
    customer_iterator.close(  );
 
  } // end of displayCustomers(  )
 
}

The output from the program ContextExample1.sqlj is as follows:

Adding customer Jason Price using conn_context.
Updating customer 1 name to John Doe using conn_context.
List of customers using default connection context.
Customer:
id = 1
first_name = John
last_name = Smith
Customer:
id = 2
first_name = Cynthia
last_name = Stevens
Customer:
id = 3
first_name = Steve
last_name = Seymour
Customer:
id = 4
first_name = Gail
last_name = Williams
Customer:
id = 5
first_name = Doreen
last_name = Heyson
Switching default connection context to conn_context.
List of customers using default connection context.
Customer:
id = 1
first_name = John
last_name = Doe
Customer:
id = 2
first_name = Cynthia
last_name = Stevens
Customer:
id = 3
first_name = Steve
last_name = Seymour
Customer:
id = 4
first_name = Gail
last_name = Williams
Customer:
id = 5
first_name = Doreen
last_name = Heyson
Customer:
id = 6
first_name = Jason
last_name = Price

Pages: 1, 2, 3, 4, 5

Next Page