Java Programming with Oracle SQLJ: Contexts and Multithreading
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 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
Prev [1] [2] [3] [4] [5] Next