Java Programming with Oracle SQLJ: Chapter 8: Contexts and Multithreading


	Java Programming with Oracle SQLJ By Jason Price August 2001 0-596-00087-1, Order Number: 0871 398 pages, $39.95

Chapter 8
Contexts and Multithreading

There are two important objects used in SQLJ that affect the execution of database operations: connection contexts and execution contexts. Connection contexts are used to connect to a database. All embedded SQL statements within a SQLJ program run in a connection context. Connection contexts make it possible to create multiple connections to a database or to connect to more than one database at a time. An execution context is used to hold the number of rows affected by a SQL operation, along with any warnings generated by the database. Execution contexts are used to control certain aspects of how a SQL statement is executed. For example, you can use an execution context to control the timeout period after which a SQL operation is abandoned.

A multithreaded program is one that is able to carry out several tasks in parallel using Java threads. As you will see in this chapter, execution contexts are very important when writing a multithreaded SQLJ program.

Connection Contexts

A connection context represents a connection to a particular database schema. So far, all the SQLJ programs you have seen have used one database connection, established using a call to the Oracle.connect( ) method. For example, the program FundamentalExample1.sqlj described in Chapter 3 used the following call to connect to the fundamental_user schema:
Oracle.connect(
  "jdbc:oracle:thin:@localhost:1521:orcl",
  "fundamental_user",
  "fundamental_password"
);
A call to the Oracle.connect( ) method creates an object of the sqlj.runtime.ref.DefaultContext class, which may then be used to access the database. This DefaultContext object is known as the default connection context, and it is the database connection that the rest of the program uses by default when performing SQL operations. You can create additional connection contexts in order to make multiple database connections.

Multiple Database Connections

Sometimes, a single database connection may not be enough for your program to accomplish its task. For example, you might need to connect to two or more schemas in the database at the same time to retrieve the data you need. It is possible to create multiple database connections by explicitly creating additional objects of the DefaultContext class.

The oracle.sqlj.runtime.Oracle class contains a method named getConnection( ) that creates and returns a DefaultContext object, which may then be stored in a DefaultContext object that you explicitly create. The syntax for the getConnection( ) method is the same as for the connect( ) method. For example, the following statement creates a DefaultContext object named conn_context1 that connects to the fundamental_user schema:
DefaultContext conn_context1 = Oracle.getConnection(
  "jdbc:oracle:thin:@localhost:1521:orcl",
  "fundamental_user",
  "fundamental_password"
);
The statement in this example calls the getConnection( ) method to create and return a DefaultContext object. That object is then assigned to conn_context1. Multiple connections may be made to the same schema using multiple DefaultContext objects. For example, the following statement creates another connection context named conn_context2, which also accesses the fundamental_user schema:
DefaultContext conn_context2 = Oracle.getConnection(
  "jdbc:oracle:thin:@localhost:1521:orcl",
  "fundamental_user",
  "fundamental_password"
);
An important point to note when working with multiple connection contexts is that each connection context establishes its own database session. As a result, each connection context has its own transaction in the database.

Specifying the connection context to use

Now that you have seen how to create connection contexts, you must be asking yourself, "How do I tell SQLJ which connection context to use?" There are two ways you can do this. The first is to include the name of the connection context in the SQLJ executable statement. This is done using the following syntax:
#sql [connection_context_name] { SQL_statement };
TIP: The brackets around the connection context name in this syntax do not indicate an optional syntax element. They are actually part of the statement.

The syntax elements are as follows:

connection_context_name

The name of the connection context to use for the embedded SQL statement.

SQL_statement

The embedded SQL statement.

If a connection context name is not specified, then the default connection context is used to perform the SQL statement. The following example uses the connection context conn_context1 created earlier to update a row in the customers table:
#sql [conn_context1] {
  UPDATE
    customers
  SET
    first_name = 'John',
    last_name = 'Doe'
  WHERE
    id = 1
};
The second way to indicate which connection context to use is to call the DefaultContext.setDefaultContext( ) method. This method accepts a DefaultContext object, and any SQL statements that follow it will use this connection context by default. Of course, if a connection context is explicitly specified in an executable statement, then that connection context will be used: an explicitly specified execution context always overrides the default. The following example shows the setDefaultContext( ) method being used to set the default connection context for two different SQLJ statements:
DefaultContext.setDefaultContext(conn_context1);
#sql { SQL_statement };  // uses conn_context1
 
DefaultContext.setDefaultContext(conn_context2);
#sql { SQL_statement };  // uses conn_context2
In addition to setting a default context, you can also retrieve it. A call to the getDefaultContext( ) method returns the default connection context. For example:
DefaultContext default_context = DefaultContext.getDefaultContext(  );
Understanding transactions and multiple connection contexts

I have already mentioned that each connection context establishes its own database session and transaction. This is useful because it means you can have multiple transactions against the same schema. The code in this section illustrates a simple example of this powerful feature. The following example updates a row in the customers table using the connection context conn_context1. If a transaction were not already underway, this statement would cause a new transaction to be started.
#sql [conn_context1] {
  UPDATE
    customers
  SET
    first_name = 'John',
    last_name = 'Doe'
  WHERE
    id = 1
};
Until this database transaction is completed by conn_context1 (using a commit or rollback, for example), the row is locked. If another statement uses a different connection context to attempt to modify the same row, it must wait until conn_context1 ends the transaction and the lock on the row is freed.[1] This locking behavior is not unique to connection contexts. If you connect to the fundamental_user schema using two instances of SQL*Plus to start two database sessions and then try to update the same row in a table from both sessions, one of the SQL*Plus sessions will wait until the other session ends its transaction.
In the following example, conn_context1 performs a rollback of the update, and conn_context2 is then able to modify the same row in the customers table:
#sql [conn_context1] { ROLLBACK };
 
#sql [conn_context2] {
  UPDATE
    customers
  SET
    first_name = 'Fred',
    last_name = 'Smith'
  WHERE
    id = 1
};
Making connections to multiple databases

You aren't limited to accessing one database in a SQLJ program. The Oracle.getConnection( ) method may be used to create connection contexts that access schemas in more than one database. For example, the following statement creates a connection context that accesses a schema named remote_user in the database identified by the Oracle SID orcl, which is running on a computer named remotehost:
DefaultContext remote_conn_context = Oracle.getConnection(
  "jdbc:oracle:thin:@remotehost:1521:orcl",
  "remote_user",
  "remote_password"
);
The connection context remote_conn_context may now be used in executable statements, just like any other connection context. In this way, your SQLJ programs can access as many databases as necessary.
Closing connection contexts

Once a connection context is no longer needed, it is good programming practice to either perform a commit or a rollback, and close the connection context using the close( ) method. Actually, when you close a connection context, an implicit commit is performed, but you should still perform an explicit commit instead of relying on this default behavior. If you don't close a connection context, an implicit rollback occurs when your SQLJ program ends, and any changes you made to the database in the transaction for that context will be lost.
The following statement closes the connection context conn_context1:
conn_context1.close(  );
The default connection context created by a call to the Oracle.connect( ) method may also be closed:
Oracle.close(  );
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:

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.

Adds a row to the customers table using conn_context.

Updates customer #1 using conn_context.

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.

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

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.

Rolls back the changes.

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 (which has a separate database transaction
      // 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);
 
ay 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
Execution Contexts

A SQLJ executable statement contains an embedded SQL statement, which is said to run within an execution context. Each SQLJ executable statement uses an execution context that is either implicitly or explicitly associated with it. An execution context is an object of the class sqlj.runtime.ExecutionContext. The ExecutionContext class provides a number of useful methods that you can use to control how the SQL statement is run. You can also use these methods to get information on the results of the previously executed SQL statement, and to enable batch processing (see Chapter 10 for details on batch processing).
Execution contexts are also used in multithreaded SQLJ applications, as you will see later in this chapter. An execution context should not be confused with a connection context: a connection context is used to specify a connection to a database; an execution context is used to run a SQL statement. I know all this may sound confusing, but it will make more sense once you look at some examples.
The Default Execution Context

A default execution context is created for every connection context. When you create a default connection context via a call to the Oracle.connect( ) method, a default execution context is also created. Therefore, if you create five connection contexts, you will also get five execution contexts.
No special effort on your part is required to use a connection's default execution context when executing a SQL statement. For example, the following statement updates customer #1 using the default execution context associated with the default connection context:
#sql {
  UPDATE
    customers
  SET
    first_name = 'John',
    last_name = 'Doe'
  WHERE
    id = 1
};
There is nothing special about this example: it simply uses the default execution context associated with the default connection context. The following example is another variation on this same theme, and uses the default execution context associated with the connection context conn_context, created earlier in this chapter, to perform the same update:
#sql [conn_context] {
  UPDATE
    customers
  SET
    first_name = 'John',
    last_name = 'Doe'
  WHERE
    id = 1
};
You can retrieve the default execution context for a connection by using the getExecutionContext( ) method of the connection context object. The following statement declares an execution context named exec_context, and retrieves the default execution context from the default connection context using the getDefaultContext( ) and getExecutionContext( ) methods:
ExecutionContext exec_context =
  DefaultContext.getDefaultContext.getExecutionContext(  );
The following example does much the same thing, but uses a connection that you've created. It declares an execution context named exec_context2, and retrieves the default execution context from conn_context using the getExecutionContext( ) method:
ExecutionContext exec_context2 = conn_context.getExecutionContext(  );
Once you have your execution context, you can use the execution context methods. These are described in the next section.
Execution Context Methods

The ExecutionContext class provides a number of useful methods that may be used in your SQLJ programs by importing the sqlj.runtime.ExecutionContext class. The more useful methods in the ExecutionContext class are as follows:

getWarnings( )

getUpdateCount( )

setQueryTimeout( )

getQueryTimeout( )

setMaxRows( )

getMaxRows( )

The following sections describe each of these methods in detail.
getWarnings( )

The getWarnings( ) method returns a java.sql.SQLWarning object that contains the first warning from the most recently executed SQLJ statement within the execution context. The following statement declares a SQLWarning object named sql_warning and copies the results of a call to the getWarnings( ) method into it:
SQLWarning sql_warning = exec_context.getWarnings(  );
The SQLWarning class contains the method getSQLState( ), which returns a string containing the text of the SQL warning. The following statement makes use of this method to display the SQL warning string for the sql_warning object just created:
System.out.println(sql_warning.getSQLState(  ));
getUpdateCount( )

The getUpdateCount( ) method returns an int value indicating the number of rows modified by the last SQLJ statement executed within the specified execution context. For example, the following statement displays the number of rows modified by the last SQLJ statement executed in the exec_context execution context:
System.out.println(exec_context.getUpdateCount(  ));
setQueryTimeout( )

The setQueryTimeout( ) method changes the amount of time a query will wait before a timeout occurs; setQueryTimeout( ) accepts an int value that represents the timeout duration in seconds. The default is 0, which means that there is no timeout. The following statement sets the query timeout to 30 seconds for the execution context named exec_context:
exec_context.setQueryTimeout(30);
getQueryTimeout( )

The getQueryTimeout( ) method returns an int value that represents the amount of time in seconds a query will wait before a timeout occurs. The following statement displays the query timeout for the execution context named exec_context:
System.out.println(exec_context.getQueryTimeout(  ));
setMaxRows( )

The setMaxRows( ) method changes the maximum number of rows that may be returned by an iterator. The default is 0, which means that there is no maximum number of rows set. The following statement sets the maximum number of rows to 30 for the execution context named exec_context:
exec_context.setMaxRows(30);
getMaxRows( )

The getMaxRows( ) method returns an int value that represents the maximum number of rows that may be returned by an iterator. The following statement displays the maximum number of rows that the exec_context execution context allows to be stored in an iterator:
System.out.println(exec_context.getMaxRows(  ));
Creating a New Execution Context

Execution contexts may be created using the ExecutionContext class. As you will soon see, creating execution contexts is very important when writing multithreaded programs. The following statement creates an execution context named exec_context:
ExecutionContext exec_context = new ExecutionContext(  );
When you create an execution context, it isn't necessarily tied to a particular connection context: you can use it with any connection context. The only execution context tied to a particular connection context is the default execution context for that connection context. The next section shows you how to specify which execution context to use in a SQLJ statement.
Specifying an Execution Context

You can specify connection contexts and execution contexts together in an embedded SQL operation. The following syntax illustrates how to do this:
#sql [connection_context_name, execution_context_name] { SQL_statement };
The syntax elements are as follows:

connection_context_name

The name of the connection context to use when executing the statement

execution_context_name

The name of the execution context in which to execute the statement

SQL_statement

The embedded SQL statement that you want to execute[2]

TIP: You can specify both a connection context and an execution context. If you do, the connection context must come first.

Both the connection context name and the execution context name are optional. If you specify a connection context without specifying an execution context, then the default execution context for that connection context is used to perform the SQL statement. Similarly, if you specify an execution context without specifying a connection context, then the default connection context is used. If you specify neither a connection context nor an execution context, then the default connection and execution contexts are used.

TIP: SQLJ statements that use the same connection context share the same database transaction even if they use different execution contexts.

In the following example, only an execution context is specified. The execution context exec_context is used together with the default connection context to update customer #1:
#sql [exec_context] {
  UPDATE
    customers
  SET
    first_name = 'John',
    last_name = 'Doe'
  WHERE
    id = 1
};
In this next example, both a connection context and an execution context are specified. The connection context conn_context is used along with the execution context exec_context to update customer #2:
#sql [conn_context, exec_context] {
  UPDATE
    customers
  SET
    first_name = 'Jean',
    last_name = 'Smith'
  WHERE
    id = 2
};
Because this example uses a connection context different from that used in the first example, it also uses a different database transaction. The following example uses the same connection context as the previous example (conn_context), but a different execution context:
#sql [conn_context, exec_context2] {
  UPDATE
    customers
  SET
    first_name = 'Fred',
    last_name = 'Doe'
  WHERE id = 2
};
Because this example uses the same connection context as the previous one, it also shares the same database transaction. Therefore, the customer name for customer #2 will be changed from "Jean Smith" to "Fred Doe", overwriting the previous update.

Example Program: ContextExample2.sqlj

This section contains a complete program that illustrates the transactional behavior of execution contexts. The program ContextExample2.sqlj (Example 8-2) performs the following major steps:

Creates a connection context, named conn_context, that connects to the fundamental_user schema.

Creates two execution contexts, named exec_context and exec_context2.

Displays all rows in the customers table using the program's displayCustomers( ) method.

Updates the customer name to "John Doe" for customer #1 using conn_context and exec_context.

Updates the customer name to "Jean Smith" for customer #1 using conn_context and exec_context2. This overwrites the previous update, illustrating that SQLJ statements that use the same connection context share the same database transaction.

Displays all the rows in the customers table, showing the change made to customer #1.

Rolls back the changes made to the customers table.

Example 8-2: ContextExample2.sqlj
/*
   The program ContextExample2.sqlj illustrates how to create
   and use execution context objects.
*/
 
import java.sql.*;
import oracle.sqlj.runtime.Oracle;
import sqlj.runtime.ref.DefaultContext;
import sqlj.runtime.ExecutionContext;
 
public class ContextExample2 {
 
  // declare the iterator class
  #sql private static iterator CustomerIteratorClass (
    int id, String first_name, String last_name
  );
 
  public static void main(String [] args) {
 
    try {
 
      // connect to database using a connection context
      // named conn_context
      DefaultContext conn_context = Oracle.getConnection(
        "jdbc:oracle:thin:@localhost:1521:orcl",
        "fundamental_user",
        "fundamental_password"
      );
 
      // create an execution context named exec_context
      ExecutionContext exec_context = new ExecutionContext(  );
 
      // create a second execution context named exec_context2
      ExecutionContext exec_context2 = new ExecutionContext(  );
 
      // display all customers using conn_context and exec_context
      System.out.println("List of customers using connection context " +
        "conn_context and execution context exec_context.");
      displayCustomers(conn_context, exec_context);
 
      // update customer using conn_context and exec_context
      System.out.println("Updating customer 1 name to John Doe using " +
        "conn_context and exec_context.");
      #sql [conn_context, exec_context] {
        UPDATE
          customers
        SET
          first_name = 'John',
          last_name  = 'Doe'
        WHERE
          id = 1
      };
 
      // update customer using conn_context and exec_context2,
      System.out.println("Updating customer 1 name to Jean Smith using " +
        "conn_context and exec_context2.");
      #sql [conn_context, exec_context2] {
        UPDATE
          customers
        SET
          first_name = 'Jean',
          last_name  = 'Smith'
        WHERE
          id = 1
      };
 
      // display all customers using the conn_context and exec_context
      System.out.println("List of customers using conn_context " +
        "and exec_context.");
      displayCustomers(conn_context, exec_context);
 
      // perform an explicit ROLLBACK using conn_context and exec_context
      #sql [conn_context, exec_context] { ROLLBACK };
 
      // close the connection context
      conn_context.close(  );
 
    } catch ( SQLException e ) {
 
      System.err.println("SQLException " + e);
      System.exit(1);
 
    }
 
  } // end of main(  )
 
 
  private static void displayCustomers(
    DefaultContext   conn_context,
    ExecutionContext exec_context
  ) throws SQLException {
 
    // instantiate a named iterator object
    CustomerIteratorClass customer_iterator;
 
    #sql [conn_context, exec_context] customer_iterator = {
      SELECT
        id, first_name, last_name
      FROM
        customers
      ORDER BY
        id
    };
 
    // access the contents of the iterator
    while (customer_iterator.next(  )) {
 
      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 ContextExample2.sqlj is as follows:
List of customers using connection context conn_context and execution context exec_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
Updating customer 1 name to John Doe using conn_context and exec_context.
Updating customer 1 name to Jean Smith using conn_context and xec_context2.
List of customers using conn_context and exec_context.
Customer:
id = 1
first_name = Jean
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
Multithreaded SQL J Programs

A Java program can perform a number of actions in parallel, with each action being performed by a separate process. Such a process is also known as a thread. For example, a multithreaded program might perform database access using one thread while another thread handles the display. In the case of a SQLJ program, you might want to perform several SQL operations in parallel using a separate thread for each one. Multithreading is a very powerful feature of the Java language. The downside to writing a multithreaded program is the additional complexity of dealing with the thread model.

If a SQLJ program were to use multiple threads, then the program would run faster, right? Not always: it depends on where the program runs. If the program is deployed in the Oracle JServer JVM (described in Chapter 6), then the multithreaded program may not run faster than a comparable program that is not multithreaded. This is because the JServer JVM executes each thread serially through a single operating system thread and schedules the execution of each thread using a round-robin algorithm. However, this doesn't mean that the JServer JVM is slow! The JServer JVM has other ways to increase the performance of Java programs. If a SQLJ program is not going to be run using the JServer JVM, then the program may benefit from the use of threads.

Designing a Multithreaded SQL J Program

How would you structure a SQLJ program to use threads? The basic problem that must be solved is that when a program has two or more threads that attempt to modify the same row, the threads may conflict with one another. One thread may begin to make modifications to a row, only to have those modifications overwritten by a second thread that is modifying the same row. This is known as a race condition, and might occur when the threads use the same execution context. There are two solutions to this problem:

Have each thread use a different execution context

Have each thread use a different connection context

Both these solutions basically result in each thread using a different execution context, solving the race condition problem. If you use the same connection context for the threads, you should explicitly specify different execution contexts for each thread. Each connection context has its own default execution context, so if you use different connection contexts for each thread, then by default they will use different execution contexts.

Example Program: MultithreadedExample1.sqlj

This section contains a complete program that illustrates how to write a multithreaded SQLJ program. The program MultithreadedExample1.sqlj (Example 8-3) uses two threads, each of which uses a separate execution context to update the same row in the customers table. Because this program requires a little more explanation than the others in this chapter, I have provided a full description of the program after the program listing.

Example 8-3: MultithreadedExample1.sqlj
/*
   The program MultithreadedExample1.sqlj illustrates the use of
   multithreading to update customer names.
*/
 
import java.sql.*;
import oracle.sqlj.runtime.Oracle;
import sqlj.runtime.ExecutionContext;
import java.util.Random;
 
public class MultithreadedExample1 extends Thread {
 
  // declare the iterator class
  #sql private static iterator CustomerIteratorClass (
    int id, String first_name, String last_name
  );
 
  int customer_id;
  String first_name;
  String last_name;
 
  MultithreadedExample1(
    int customer_id,
    String first_name,
    String last_name
  ) {
    this.customer_id = customer_id;
    this.first_name = first_name;
    this.last_name = last_name;
  }
 
  public static void main(String [] args) {
 
    try {
 
      Oracle.connect(
        "jdbc:oracle:thin:@localhost:1521:orcl",
        "fundamental_user",
        "fundamental_password"
      );
 
      // display all customers
      displayCustomers(  );
 
      MultithreadedExample1 thread1 =
        new MultithreadedExample1(1, "John", "Doe");
      MultithreadedExample1 thread2 =
        new MultithreadedExample1(1, "Jean", "Smith");
 
      // start the threads using the start(  ) method
      thread1.start(  );
      thread2.start(  );
 
      // wait for each thread to complete using the join(  ) method
      thread1.join(  );
      thread2.join(  );
 
      // display all customers
      displayCustomers(  );
 
      #sql { ROLLBACK };
      Oracle.close(  );
 
    } catch ( SQLException e ) {
 
      System.err.println("SQLException " + e);
      System.exit(1);
 
    } catch ( Exception e ) {
 
      System.err.println("Exception " + e);
      System.exit(1);
 
    }
 
  } // end of main(  )
 
 
  public void run(  ) {
 
    try {
 
      System.out.println("Updating customer " + customer_id +
        " name to " + first_name + " " + last_name + ".");
 
      // create new execution context
      ExecutionContext exec_context = new ExecutionContext(  );
 
      #sql [exec_context] {
        UPDATE
          customers
        SET
          first_name = :first_name,
          last_name  = :last_name
        WHERE
          id = :customer_id
      };
 
    } catch ( SQLException e ) {
 
      System.err.println("SQLException " + e);
      System.exit(1);
 
    } catch ( Exception e ) {
 
      System.err.println("Exception " + e);
      System.exit(1);
 
    }
 
  } // end of run(  )
 
 
  private static void displayCustomers(  )
  throws SQLException {
 
    // declare a named iterator object
    CustomerIteratorClass customer_iterator;
 
    #sql customer_iterator = {
      SELECT
        id, first_name, last_name
      FROM
        customers
      ORDER BY
        id
    };
 
the contents of the iterator
    while (customer_iterator.next(  )) {
 
      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 following sections describe some of the more important aspects of this example program.

The MultithreadedExample1 class

The MultithreadedExample1 class extends the Thread class; this indicates that the program is multithreaded. The MultithreadedExample1 class contains the following three variables that are used to hold information from the customer table:
int customer_id;
String first_name;
String last_name;
The constructor for the class MultithreadedExample1 accepts three parameters. These are used to initialize the three variables when a new object of that class is created:
MultithreadedExample1(
  int customer_id,
  String first_name,
  String last_name
) {
  this.customer_id = customer_id;
  this.first_name = first_name;
  this.last_name = last_name;
}
The main( ) method

Using a call to Oracle.connect( ), the main( ) method creates a default connection context that connects to the fundamental_user schema:
Oracle.connect(
  "jdbc:oracle:thin:@localhost:1521:orcl",
  "fundamental_user",
  "fundamental_password"
);
All the rows in the customers table are then selected and displayed via a call to the displayCustomers( ) method:
displayCustomers(  );
Next, two threads are created. These are named thread1 and thread2:
MultithreadedExample1 thread1 =
  new MultithreadedExample1(1, "John", "Doe");
MultithreadedExample1 thread2 =
  new MultithreadedExample1(1, "Jean", "Smith");
The thread named thread1 sets the customer_id, first_name, and last_name object variables to 1, "John", and "Doe" respectively. The thread named thread2 sets the customer_id, first_name, and last_name object variables to 1, "Jean", and "Smith" respectively. Each thread is an instance of the MultithreadedExample1 class, and the class's constructor is used to initialize the object variables when the two thread objects are created. The two threads are then started by calling each thread's start( ) method:
thread1.start(  );
thread2.start(  );
You don't have to write the start( ) method: it comes with the Thread class. You do, however, have to write a run( ) method in your class, and the start( ) method calls that run( ) method. The run( ) method updates the first_name and last_name columns of the row in the customers table for the customer whose ID number is specified in the customer_id object variable. I will describe more details of the run( ) method shortly. After starting the two threads, the program waits until both threads are completed. This is done by calling the join( ) method for the two threads:
thread1.join(  );
thread2.join(  );
The next statement in the main( ) method is not executed until both threads are completed. At that point, all the rows in the customers table are displayed again via another call to the displayCustomers( ) method:
displayCustomers(  );
Finally, the main( ) method rolls back the changes made to the customers table by the run( ) methods for the two threads and disconnects from the database:
#sql { ROLLBACK };
Oracle.close(  );
The run( ) method

The run( ) method begins by creating an execution context named exec_context:
ExecutionContext exec_context = new ExecutionContext(  );
In this way, each thread has its own execution context, thereby preventing any race condition that might otherwise occur. The execution context uses the database transaction established by the default connection context at the start of the main( ) method. Both execution contexts created by the two threads share this database transaction.

The execution context exec_context is then used to update the first_name and last_name columns for the row with an id equal to the value stored in the customer_id variable. The values for the first_name and last_name columns come from the variables first_name and last_name:
#sql [exec_context] {
  UPDATE
    customers
  SET
    first_name = :first_name,
    last_name  = :last_name
  WHERE
    id = :customer_id
};
Program output

The output from MultithreadedExample1.sqlj is as follows:
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
Updating customer 1 name to John Doe.
Updating customer 1 name to Jean Smith.
Customer:
id = 1
first_name = Jean
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
1. Actually, this is a simplification. In the next chapter, I discuss the default transactional behavior and how to change it.

2. In the case of a FETCH statement, the connection context and execution context originally used to populate the iterator is used.

Back to: Java Programming with Oracle SQLJ

Java Programming with Oracle SQLJ

Chapter 8 Contexts and Multithreading

Connection Contexts

Multiple Database Connections

Specifying the connection context to use

Understanding transactions and multiple connection contexts

Making connections to multiple databases

Closing connection contexts

Example Program: ContextExample1.sqlj

Execution Contexts

The Default Execution Context

Execution Context Methods

getWarnings( )

getUpdateCount( )

setQueryTimeout( )

getQueryTimeout( )

setMaxRows( )

getMaxRows( )

Creating a New Execution Context

Specifying an Execution Context

Example Program: ContextExample2.sqlj

Multithreaded SQL J Programs

Designing a Multithreaded SQL J Program

Example Program: MultithreadedExample1.sqlj

The MultithreadedExample1 class

The main( ) method

The run( ) method

Program output

Chapter 8
Contexts and Multithreading