Sun Java System Application Server Platform Edition 8.1 2005Q2 Update 2 Developer's Guide

- Previous: General Steps for Creating a JDBC Resource
- Next: Configurations for Specific JDBC Drivers

# Creating Applications That Use the JDBC API

An application that uses the JDBC API is an application that looks up and connects to one or more databases. This section covers these topics:

- Sharing Connections

- Obtaining a Physical Connection from a Wrapped Connection

- Using Non-Transactional Connections

- Using JDBC Transaction Isolation Levels

## Sharing Connections

When multiple connections acquired by an application use the same JDBC resource, the connection pool provides connection sharing within the same transaction scope. For example, suppose Bean A starts a transaction and obtains a connection, then calls a method in Bean B. If Bean B acquires a connection to the same JDBC resource with the same sign-on information, and if Bean A completes the transaction, the connection can be shared.

Connections obtained through a resource are shared only if the resource reference declared by the J2EE component allows it to be shareable. This is specified in a component’s deployment descriptor by setting the `res-sharing-scope` element to `Shareable` for the particular resource reference. To turn off connection sharing, set `res-sharing-scope` to `Unshareable`.

For general information about connections and JDBC URLs, see the Sun Java System Application Server Platform Edition 8.1 2005Q2 Update 2 Administration Guide.

## Obtaining a Physical Connection from a Wrapped Connection

The `DataSource` implementation in the Application Server provides a `getConnection` method that retrieves the JDBC driver’s `SQLConnection` from the Application Server’s `Connection` wrapper. The method signature is as follows:

    public java.sql.Connection getConnection(java.sql.Connection con)
    throws java.sql.SQLException

For example:

    InitialContext ctx = new InitialContext();
    com.sun.appserv.DataSource ds = (com.sun.appserv.DataSource)
       ctx.lookup("jdbc/MyBase");
    Connection con = ds.getConnection();
    Connection drivercon = ds.getConnection(con);
    // Do db operations.
    con.close();

## Using Non-Transactional Connections

The `DataSource` implementation in the Application Server provides a `getNonTxConnection` method, which retrieves a JDBC connection that is not in the scope of any transaction. There are two variants, as follows:

    public java.sql.Connection getNonTxConnection() throws java.sql.SQLException

    public java.sql.Connection getNonTxConnection(String user, String password)
    throws java.sql.SQLException

Another way to get a non-transactional connection is to create a resource with the JNDI name ending in `__nontx`. This forces all connections looked up using this resource to be non transactional.

Typically, a connection is enlisted in the context of the transaction in which a `getConnection` call is invoked. However, a non-transactional connection is not enlisted in a transaction context even if a transaction is in progress.

The main advantage of using non-transactional connections is that the overhead incurred in enlisting and delisting connections in transaction contexts is avoided. However, use such connections carefully. For example, if a non-transactional connection is used to query the database while a transaction is in progress that modifies the database, the query retrieves the unmodified data in the database. This is because the in-progress transaction hasn’t committed. For another example, if a non-transactional connection modifies the database and a transaction that is running simultaneously rolls back, the changes made by the non-transactional connection are not rolled back.

Here is a typical use case for a non-transactional connection: a component that is updating a database in a transaction context spanning over several iterations of a loop can refresh cached data by using a non-transactional connection to read data before the transaction commits.

## Using JDBC Transaction Isolation Levels

For general information about transactions, see Chapter 12, Using the Transaction Serviceand the Sun Java System Application Server Platform Edition 8.1 2005Q2 Update 2 Administration Guide. For information about last agent optimization, which can improve performance, see Transaction Scope.

Not all database vendors support all transaction isolation levels available in the JDBC API. The Application Server permits specifying any isolation level your database supports. The following table defines transaction isolation levels.

Table 11–1 Transaction Isolation Levels

Transaction Isolation Level | Description
--- | ---
`TRANSACTION_READ_UNCOMMITTED` | Dirty reads, non-repeatable reads and phantom reads can occur.
`TRANSACTION_READ_COMMITTED` | Dirty reads are prevented; non-repeatable reads and phantom reads can occur.
`TRANSACTION_REPEATABLE_READ` | Dirty reads and non-repeatable reads are prevented; phantom reads can occur.
`TRANSACTION_SERIALIZABLE` | Dirty reads, non-repeatable reads and phantom reads are prevented.

Note that you cannot call `setTransactionIsolation()` during a transaction.

You can set the default transaction isolation level for a JDBC connection pool. For details, see Creating a Connection Pool.

To verify that a level is supported by your database management system, test your database programmatically using the `supportsTransactionIsolationLevel()` method in `java.sql.DatabaseMetaData`, as shown in the following example:

    java.sql.DatabaseMetaData db;
    if (db.supportsTransactionIsolationLevel(TRANSACTION_SERIALIZABLE)
       { Connection.setTransactionIsolation(TRANSACTION_SERIALIZABLE); }

For more information about these isolation levels and what they mean, see the JDBC 3.0 API specification.

Note –

Applications that change the isolation level on a pooled connection programmatically risk polluting the pool, which can lead to errors.

- Previous: General Steps for Creating a JDBC Resource
- Next: Configurations for Specific JDBC Drivers

- © 2010, Oracle Corporation and/or its affiliates