Sun GlassFish Enterprise Server v3 Prelude Developer's Guide

Creating Web Applications That Use the JDBC API

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

Setting a Statement Timeout

An abnormally long running JDBC query executed by an application may leave it in a hanging state unless a timeout is explicitly set on the statement. Setting a statement timeout guarantees that all queries automatically time out if not completed within the specified period. When statements are created, the queryTimeout is set according to the statement timeout setting. This works only when the underlying JDBC driver supports queryTimeout for Statement, PreparedStatement, CallableStatement, and ResultSet.

You can specify a statement timeout in the following ways:

Enter a Statement Timeout value in the JDBC Connection Pools page in the Administration Console. For more information, click the Help button in the Administration Console.
Specify the --statementtimeout option in the asadmin create-jdbc-connection-pool command. For more information, see the Sun GlassFish Enterprise Server v3 Prelude Reference Manual.

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 Java EE 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 Chapter 5, Administering Database Connectivity , in Sun GlassFish Enterprise Server v3 Prelude Administration Guide.

Wrapping Connections

If the Wrap JDBC Objects option is true, wrapped JDBC objects are returned for Statement, PreparedStatement, CallableStatement, ResultSet, and DatabaseMetaData. The default is false.

This option ensures that Statement.getConnection() is the same as DataSource.getConnection(). Therefore, this option should be true when both Statement.getConnection() and DataSource.getConnection() are done. The default is false to avoid breaking existing applications.

You can specify the Wrap JDBC Objects option in the following ways:

Check or uncheck the Wrap JDBC Objects box on the JDBC Connection Pools page in the Administration Console. For more information, click the Help button in the Administration Console.
Specify the --wrapjdbcobjects option in the asadmin create-jdbc-connection-pool command. For more information, see the Sun GlassFish Enterprise Server v3 Prelude Reference Manual.

Obtaining a Physical Connection From a Wrapped Connection

The DataSource implementation in the Enterprise Server provides a getConnection method that retrieves the JDBC driver’s SQLConnection from the Enterprise 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.jdbc.DataSource ds = (com.sun.appserv.jdbc.DataSource) 
   ctx.lookup("jdbc/MyBase");
Connection con = ds.getConnection();
Connection drivercon = ds.getConnection(con); //get physical connection from wrapper
// Do db operations.
// Do not close driver connection.
con.close(); // return wrapped connection to pool.

Using the `Connection.unwrap()` Method

If the JDK version 1.6 is used, the Enterprise Server supports JDBC 4.0 if the JDBC driver is JDBC 4.0 compliant. Using the Connection.unwrap() method on a vendor-provided interface returns an object or a wrapper object implementing the vendor-provided interface, which the application can make use of to do vendor-specific database operations. Use the Connection.isWrapperFor() method on a vendor-provided interface to check whether the connection can provide an implementation of the vendor-provided interface. Check the JDBC driver vendor's documentation for information on these interfaces.

Marking Bad Connections

The DataSource implementation in the Enterprise Server provides a markConnectionAsBad method. A marked bad connection is removed from its connection pool when it is closed. The method signature is as follows:

public void markConnectionAsBad(java.sql.Connection con)

For example:

com.sun.appserv.jdbc.DataSource ds=
   (com.sun.appserv.jdbc.DataSource)context.lookup("dataSource");
Connection con = ds.getConnection();
Statement stmt = null;
try{
   stmt = con.createStatement();
   stmt.executeUpdate("Update");
}
catch (BadConnectionException e){
   ds.markConnectionAsBad(con) //marking it as bad for removal
}
finally{
   stmt.close();	
   con.close(); //Connection will be destroyed during close.
}

Using Non-Transactional Connections

You can specify a non-transactional database connection in any of these ways:

Check the Non-Transactional Connections box on the JDBC Connection Pools page in the Administration Console. The default is unchecked. For more information, click the Help button in the Administration Console.
Specify the --nontransactionalconnections option in the asadmin create-jdbc-connection-pool command. For more information, see the Sun GlassFish Enterprise Server v3 Prelude Reference Manual.
Use the DataSource implementation in the Enterprise Server, which provides a getNonTxConnection method. This method retrieves a JDBC connection that is not in the scope of any transaction. There are two variants.
```
public java.sql.Connection getNonTxConnection() throws java.sql.SQLException
```
```
public java.sql.Connection getNonTxConnection(String user, String password) 
   throws java.sql.SQLException
```
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 10, Using the Transaction Service. 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 Enterprise Server permits specifying any isolation level your database supports. The following table defines transaction isolation levels.

Table 9–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.

You can specify the transaction isolation level in the following ways:

Select the value from the Transaction Isolation drop-down list on the JDBC Connection Pools page in the Administration Console. For more information, click the Help button in the Administration Console.
Specify the --isolationlevel option in the asadmin create-jdbc-connection-pool command. For more information, see the Sun GlassFish Enterprise Server v3 Prelude Reference Manual.

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:

InitialContext ctx = new InitialContext();
DataSource ds = (DataSource)
ctx.lookup("jdbc/MyBase");
Connection con = ds.getConnection();
DatabaseMetaData dbmd = con.getMetaData();
if (dbmd.supportsTransactionIsolationLevel(TRANSACTION_SERIALIZABLE)
{ Connection.setTransactionIsolation(TRANSACTION_SERIALIZABLE); }

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

Note –

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

Allowing Non-Component Callers

You can allow non-Java-EE components, such as servlet filters and third party persistence managers, to use this JDBC connection pool. The returned connection is automatically enlisted with the transaction context obtained from the transaction manager. Standard Java EE components can also use such pools. Connections obtained by non-component callers are not automatically closed at the end of a transaction by the container. They must be explicitly closed by the caller.

You can enable non-component callers in the following ways:

Check the Allow Non Component Callers box on the JDBC Connection Pools page in the Administration Console. The default is false. For more information, click the Help button in the Administration Console.
Specify the --allownoncomponentcallers option in the asadmin create-jdbc-connection-pool command. For more information, see the Sun GlassFish Enterprise Server v3 Prelude Reference Manual.
Create a JDBC resource with a __pm suffix.