11.2 Sharing Database Connections
When a connection to the database is established by a SQLJ program using the connect( ) or getConnection( ) methods, an underlying JDBC connection is created. Through that connection, communication between the program and the database takes place. You can extract this underlying JDBC connection from the SQLJ connection context and use it to access the database using JDBC statements. In this way, one connection is used for both SQLJ and JDBC statements. Similarly, you can also create or re-use a JDBC connection and use it to create a SQLJ connection context, which may then be used by SQLJ statements to access the database.
 | Shared database connections also share the same database session and transaction. |
|
I discuss sharing a SQLJ connection with JDBC first, and then discuss sharing a JDBC connection with SQLJ.
11.2.1 Sharing a SQLJ Connection with JDBC
To share a database connection established by a SQLJ connection context with JDBC, you must perform the following steps:
-
Call the getConnection( ) method in the sqlj.runtime.ref.DefaultContext class using a SQLJ connection context; this method returns the underlying JDBC connection for the connection context.
-
Assign the JDBC connection returned by getConnection( ) to a JDBC connection object.
The following example creates a SQLJ connection context named sqlj_conn_context that accesses the fundamental_user schema. It then retrieves and assigns the underlying JDBC connection from this connection context to a new JDBC connection named jdbc_conn:
// create a SQLJ connection context DefaultContext sqlj_conn_context = Oracle.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password" ); // assign the underlying JDBC connection for the SQLJ connection // context to a new JDBC connection Connection jdbc_conn = sqlj_conn_context.getConnection( );
Subsequent JDBC statements may now use jdbc_conn to access the fundamental_user schema. You can also share the underlying JDBC connection for the default SQLJ connection context created when you call the Oracle.connect( ) method to connect to the database. The following example creates a default SQLJ connection context, and then retrieves and assigns the underlying JDBC connection to jdbc_conn2:
// create a default SQLJ connection context Oracle.connect( "jdbc:oracle:thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password" ); // assign the underlying JDBC connection for the default SQLJ connection // context to a new JDBC connection Connection jdbc_conn2 = DefaultContext.getDefaultContext().getConnection( );
The getDefaultContext( ) method returns the default connection context, and the getConnection( ) method returns the underlying JDBC connection. The underlying JDBC connection is then assigned to the JDBC connection object jdbc_conn2.
When an underlying JDBC connection in a SQLJ connection context is shared with JDBC, the database session and transaction are also shared. This means that any SQL operations that modify the database are visible to both, regardless of whether they are performed using the SQLJ connection context or the JDBC connection; this includes COMMIT and ROLLBACK statements. For example, if you update a row in the customers table using the SQLJ connection context sqlj_conn_context, this change is also visible to the JDBC connection jdbc_conn.
11.2.2 Sharing a JDBC Connection with SQLJ
You may also share a database connection established by JDBC with SQLJ. To do this, pass the JDBC connection object to the constructor of the DefaultContext class when creating a new SQLJ connection context. The following example creates a JDBC connection to the fundamental_user schema, which is then passed to the DefaultContext( ) constructor used to create a new SQLJ connection context:
// create a JDBC connection Connection jdbc_conn = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password" ); // create a new SQLJ connection context and pass the JDBC connection // into the constructor of DefaultContext, this shares the database // connection with SQLJ DefaultContext sqlj_conn_context = new DefaultContext(jdbc_conn);
The JDBC connection jdbc_conn is assigned to the underlying JDBC connection for sqlj_conn_context, which may then be used by subsequent SQLJ statements to access the fundamental_user schema. By default, JDBC connections are created with the auto-commit flag set to true; you can set it to false using the following statement:
jdbc_conn.setAutoCommit(false);
11.2.3 Closing Shared Database Connections
When closing a SQLJ connection, you can either keep the underlying JDBC connection open, or you can close it. A SQLJ connection context is closed using the close( ) method, which accepts one optional parameter. This parameter may be set to one of the following predefined constants. These two constants are defined in the sqlj.runtime.ConnectionContext interface, which you should import into your SQLJ program if you want to use it, and they indicate whether the underlying JDBC connection for the connection context should also be closed.
- CLOSE_CONNECTION
-
Closes the underlying JDBC connection.
- KEEP_CONNECTION.
-
Keeps the underlying JDBC connection open.
If you don't specify a parameter to the close( ) method, SQLJ's default behavior is to close the underlying JDBC connection. The following example closes the connection context sqlj_conn_context, and by default closes the underlying JDBC connection, which in this case is jdbc_conn:
sqlj_conn_context.close( );
The following example closes sqlj_conn_context but keeps the underlying JDBC connection (jdbc_conn in this case) open:
sqlj_conn_context.close(ConnectionContext.KEEP_CONNECTION);
By keeping jdbc_conn open, you allow jdbc_conn and any subsequent JDBC statements that use it to continue to access the database.
11.2.4 Example Program: JdbcSqljExample1.sqlj
This section contains a complete program, JdbcSqljExample1.sqlj (Example 11-2), that illustrates the sharing of database connections and transactions between SQLJ and JDBC. The program performs the following steps:
-
Creates a SQLJ connection context, named sqlj_conn_context, that accesses the fundamental_user schema.
-
Shares the database connection for sqlj_conn_context with a new JDBC connection named jdbc_conn.
-
Displays the first_name and last_name columns for customer #1 in the customers table using sqlj_conn_context.
-
Displays the same columns using jdbc_conn.
-
Updates the same columns using sqlj_conn_context. Because jdbc_conn shares the database transaction, this update is also visible to jdbc_conn.
-
Displays the columns using both sqlj_conn_context and jdbc_conn, demonstrating that the update is indeed visible to both.
-
Performs a rollback, undoing the previous update.
-
Closes sql_conn_context, keeping the underlying JDBC connection open so that jdbc_conn can still access the database.
-
Displays the columns again using jdbc_conn, demonstrating that it can indeed still access the database.
-
Closes jdbc_conn.
-
Creates a new JDBC connection named jdbc_conn2. The database connection is then shared with a new SQLJ connection context named sqlj_conn_context2.
-
Displays the columns using sqlj_conn_context2, demonstrating that this connection context can access the database using the shared connection.
-
Closes sqlj_conn_context2, which also closes jdbc_conn2.
Example 11-2. JdbcSqljExample1.sqlj
/* The program JdbcSqljExample1.sqlj illustrates the use of sharing database connections and transactions between SQLJ and JDBC. */ import java.sql.*; import oracle.sqlj.runtime.Oracle; import sqlj.runtime.ref.DefaultContext; import sqlj.runtime.ConnectionContext; public class JdbcSqljExample1 { public static void main(String [] args) { try { // create a SQLJ connection context System.out.println("Connecting to the database using SQLJ."); DefaultContext sqlj_conn_context = Oracle.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password" ); // assign the underlying JDBC connection for the default SQLJ // connection context to a new JDBC connection System.out.println("Sharing SQLJ connection with JDBC."); Connection jdbc_conn = sqlj_conn_context.getConnection( ); // display customer using SQLJ connection context displayCustomer(sqlj_conn_context, 1); // display customer using JDBC connection displayCustomer(jdbc_conn, 1); // update customer name using SQLJ connection context System.out.println("Updating customer 1 name to Fred Heyson using " + "SQLJ connection."); #sql [sqlj_conn_context] { UPDATE customers SET first_name = 'Fred', last_name = 'Heyson' WHERE id = 1 }; // display customer using SQLJ connection context displayCustomer(sqlj_conn_context, 1); // display customer using JDBC connection, the update is // visible because it shares the same transaction // as the SQLJ connection context displayCustomer(jdbc_conn, 1); // perform an explicit ROLLBACK using SQLJ connection context System.out.println("Performing rollback using SQLJ connection."); #sql [sqlj_conn_context] { ROLLBACK }; // close the SQLJ connection context, but keep the underlying JDBC // connection open System.out.println("Closing SQLJ connection, but keeping JDBC " + "connection open."); sqlj_conn_context.close(ConnectionContext.KEEP_CONNECTION); // display customer again, this shows that the JDBC connection is // still open displayCustomer(jdbc_conn, 1); // close the JDBC connection jdbc_conn.close( ); // load the Oracle driver, this enables the JDBC driver manager // to communicate with the Oracle database DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver( )); // create a new JDBC connection System.out.println("Connecting to the database using JDBC."); Connection jdbc_conn2 = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password" ); // create a new SQLJ connection context and pass the JDBC connection // into the constructor of DefaultContext, this shares the database // connection with SQLJ System.out.println("Sharing JDBC connection with SQLJ."); DefaultContext sqlj_conn_context2 = new DefaultContext(jdbc_conn2); displayCustomer(sqlj_conn_context2, 1); // close the SQLJ connection context, which also closes the // JDBC connection sqlj_conn_context2.close( ); } catch ( SQLException e ) { System.err.println("SQLException " + e); System.exit(1); } } // end of main( ) private static void displayCustomer( DefaultContext conn_context, int id ) throws SQLException { String first_name; String last_name; #sql [conn_context] { SELECT first_name, last_name INTO :first_name, :last_name FROM customers WHERE id = :id }; System.out.println("Customer " + id + " details using SQLJ connection:"); System.out.println("first_name = " + first_name); System.out.println("last_name = " + last_name); } // end of displayCustomer( ) private static void displayCustomer( Connection jdbc_conn, int id ) throws SQLException { String sql_statement = "SELECT first_name, last_name FROM " + "customers WHERE id = " + id; // create a prepared statement PreparedStatement prepared_statement = jdbc_conn.prepareStatement(sql_statement); // create and populate a result set with the retrieved rows from // the prepared statement ResultSet result_set = prepared_statement.executeQuery( ); System.out.println("Customer " + id + " details using JDBC connection:"); // process the result set and display the customer details while (result_set.next( )) { System.out.println("first name = " + result_set.getString(1)); System.out.println("last name = " + result_set.getString(2)); } // close the result set result_set.close( ); // close the prepared statement prepared_statement.close( ); } // end of displayCustomer( ) } The output from the program is as follows:
Connecting to the database using SQLJ. Sharing SQLJ connection with JDBC. Customer 1 details using SQLJ connection: first_name = John last_name = Smith Customer 1 details using JDBC connection: first name = John last name = Smith Updating customer 1 name to Fred Heyson using SQLJ connection. Customer 1 details using SQLJ connection: first_name = Fred last_name = Heyson Customer 1 details using JDBC connection: first name = Fred last name = Heyson Performing rollback using SQLJ connection. Closing SQLJ connection, but keeping JDBC connection open. Customer 1 details using JDBC connection: first name = John last name = Smith Connecting to the database using JDBC. Sharing JDBC connection with SQLJ. Customer 1 details using SQLJ connection: first_name = John last_name = Smith
