Oracle GlassFish Server 3.0.1 Application Development Guide

Chapter 14 Using the JDBC API for Database Access

This chapter describes how to use the JavaTM Database Connectivity (JDBCTM) API for database access with the Oracle GlassFishTM Server. This chapter also provides high level JDBC implementation instructions for servlets and EJB components using the GlassFish Server. If the JDK version 1.6 is used, the GlassFish Server supports the JDBC 4.0 API.

The JDBC specifications are available at

A useful JDBC tutorial is located at

Note –

The GlassFish Server does not support connection pooling or transactions for an application’s database access if it does not use standard Java EE DataSource objects.

This chapter discusses the following topics:

General Steps for Creating a JDBC Resource

To prepare a JDBC resource for use in Java EE applications deployed to the GlassFish Server, perform the following tasks:

For information about how to configure some specific JDBC drivers, see Configuration Specifics for JDBC Drivers in Oracle GlassFish Server 3.0.1 Administration Guide.

Integrating the JDBC Driver

To use JDBC features, you must choose a JDBC driver to work with the GlassFish Server, then you must set up the driver. This section covers these topics:

Supported Database Drivers

Supported JDBC drivers are those that have been fully tested by Oracle. For a list of the JDBC drivers currently supported by the GlassFish Server, see the Oracle GlassFish Server 3.0.1 Release Notes. For configurations of supported and other drivers, see Configuration Specifics for JDBC Drivers in Oracle GlassFish Server 3.0.1 Administration Guide.

Note –

Because the drivers and databases supported by the GlassFish Server are constantly being updated, and because database vendors continue to upgrade their products, always check with Oracle technical support for the latest database support information.

Making the JDBC Driver JAR Files Accessible

To integrate the JDBC driver into a GlassFish Server domain, copy the JAR files into the domain-dir/lib directory, then restart the server. This makes classes accessible to all applications or modules deployed on servers that share the same configuration. For more information about GlassFish Server class loaders, see Chapter 2, Class Loaders.

If you are using an Oracle database with EclipseLink extensions, copy the JAR files into the domain-dir/lib/ext directory, then restart the server. For details, see Oracle Database Enhancements.

Automatic Detection of Installed Drivers

The Administration Console detects installed JDBC Drivers automatically when you create a JDBC connection pool. To create a JDBC connection pool using the Administration Console, open the Resources component, open the JDBC component, select Connection Pools, and click on the New button. This displays the New JDBC Connection Pool page.

Based on the Resource Type and Database Vendor you select on the New JDBC Connection Pool page, data source or driver implementation class names are listed in the Datasource Classname or Driver Classname field when you click on the Next button. When you choose a specific implementation class name on the next page, additional properties relevant to the installed JDBC driver are displayed in the Additional Properties section.

Creating a JDBC Connection Pool

When you create a connection pool that uses JDBC technology (a JDBC connection pool) in the GlassFish Server, you can define many of the characteristics of your database connections.

You can create a JDBC connection pool in one of these ways:

For a complete description of JDBC connection pool features, see the Oracle GlassFish Server 3.0.1 Administration Guide.

Modifying a JDBC Connection Pool

In the Administration Console, some JDBC connection pool attributes are advanced, and you cannot set them during JDBC connection pool creation. You can only set them when modifying an existing JDBC connection pool. You can also use the asadmin set command to set or reset a JDBC connection pool's attributes.

You can modify a JDBC connection pool in one of these ways:

Testing a JDBC Connection Pool

You can test a JDBC connection pool for usability in one of these ways:

Both these commands fail and display an error message unless they successfully connect to the connection pool.

You can also specify that a connection pool is automatically tested when created or reconfigured by setting the Ping attribute to true (the default is false) in one of the following ways:

Flushing a JDBC Connection Pool

Flushing a JDBC connection pool recreates all the connections in the pool and brings the pool to the steady pool size without the need for reconfiguring the pool. Connection pool reconfiguration can result in application redeployment, which is a time-consuming operation. Flushing destroys existing connections, and any existing transactions are lost and must be retired.

You can flush a JDBC connection pool in one of these ways:

Creating a JDBC Resource

A JDBC resource, also called a data source, lets you make connections to a database using getConnection. Create a JDBC resource in one of these ways:

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:


The following features pertain to statements:

Using an Initialization Statement

You can specify a statement that executes each time a physical connection to the database is created (not reused) from a JDBC connection pool. This is useful for setting request or session specific properties and is suited for homogeneous requests in a single application. Set the Init SQL attribute of the JDBC connection pool to the SQL string to be executed in one of the following ways:

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:

Statement Caching

Statement caching stores statements, prepared statements, and callable statements that are executed repeatedly by applications in a cache, thereby improving performance. Instead of the statement being prepared each time, the cache is searched for a match. The overhead of parsing and creating new statements each time is eliminated.

Statement caching is usually a feature of the JDBC driver. The GlassFish Server provides caching for drivers that do not support caching. To enable this feature, set the Statement Cache Size for the JDBC connection pool in one of the following ways:

By default, this attribute is set to zero and the statement caching is turned off. To enable statement caching, you can set any positive nonzero value. The built-in cache eviction strategy is LRU-based (Least Recently Used). When a connection pool is flushed, the connections in the statement cache are recreated.

Statement Tracing

You can trace the SQL statements executed by applications that use a JDBC connection pool. Set the SQL Trace Listeners attribute to a comma-separated list of trace listener implementation classes in one of the following ways:

The GlassFish Server provides a public interface, org.glassfish.api.jdbc.SQLTraceListener, that implements a means of recording SQLTraceRecord objects. To make custom implementations of this interface available to the GlassFish Server, place the implementation classes in as-install/lib.

The GlassFish Server provides an SQL tracing logger to log the SQL operations in the form of SQLTraceRecord objects in the server.log file. The module name under which the SQL operation is logged is javax.enterprise.resource.sqltrace. SQL traces are logged as FINE messages along with the module name to enable easy filtering of the SQL logs. A sample SQL trace record looks like this:

|ThreadID=77 | ThreadName=p: thread-pool-1; w: 6 | TimeStamp=1259317012202 
| ClassName=com.sun.gjc.spi.jdbc40.PreparedStatementWrapper40 | MethodName=executeUpdate 
| arg[0]=insert into table1(colName) values(100) | arg[1]=columnNames | |#]

This trace shows that an executeUpdate(String sql, String columnNames) operation is being done.


The following features pertain to connections:

Disabling Pooling

To disable connection pooling, set the Pooling attribute to false. The default is true. You can enable or disable connection pooling in one of the following ways:

The pooling option and the system property com.sun.enterprise.connectors.SwitchoffACCConnectionPooling, which turns off connection pooling in the Application Client Container, do not affect each other.

An exception is thrown if associate-with-thread is set to true and pooling is disabled. An exception is thrown if you attempt to flush a connection pool when pooling is disabled. A warning is logged if the following attributes are used, because they are useful only in a pooled environment:

Associating Connections with Threads

To associate connections with a thread, set the Associate With Thread attribute to true. The default is false. A true setting allows connections to be saved as ThreadLocal in the calling thread. Connections get reclaimed only when the calling thread dies or when the calling thread is not in use and the pool has run out of connections. If the setting is false, the thread must obtain a connection from the pool each time the thread requires a connection.

The Associate With Thread attribute associates connections with a thread such that when the same thread is in need of connections, it can reuse the connections already associated with that thread. In this case, the overhead of getting connections from the pool is avoided. However, when this value is set to true, you should verify that the value of the Max Pool Size attribute is comparable to the Max Thread Pool Size attribute of the thread pool. If the Max Thread Pool Size value is much higher than the Max Pool Size value, a lot of time is spent associating connections with a new thread after dissociating them from an older one. Use this attribute in cases where the thread pool should reuse connections to avoid this overhead.

You can set the Associate With Thread attribute in the following ways:

Custom Connection Validation

You can specify a custom implementation for Connection Validation that is faster or optimized for a specific database. Set the Validation Method attribute to the value custom-validation. (Other validation methods available are table (the default), auto-commit, and meta-data.) The GlassFish Server provides a public interface, org.glassfish.api.jdbc.ConnectionValidation, which you can implement to plug in your implementation. A new attribute, Validation Classname, specifies the fully qualified name of the class that implements the ConnectionValidation interface. The Validation Classname attribute is required if Connection Validation is enabled and Validation Method is set to Custom Validation.

To enable this feature, set Connection Validation, Validation Method, and Validation Classname for the JDBC connection pool in one of the following ways:

By default, optimized validation mechanisms are provided for Java DB, MySQL, Oracle, and PostgreSQL databases. Additionally, for JDBC 4.0 compliant database drivers, a validation mechanism is provided that uses the Connection.isValid(0) implementation.

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 14, Administering Database Connectivity , in Oracle GlassFish Server 3.0.1 Administration Guide.

Marking Bad Connections

The DataSource implementation in the GlassFish 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=
Connection con = ds.getConnection();
Statement stmt = null;
   stmt = con.createStatement();
catch (BadConnectionException e){
   ds.markConnectionAsBad(con) //marking it as bad for removal
   con.close(); //Connection will be destroyed during close.

Handling Invalid Connections

If a ConnectionErrorOccured event occurs, the GlassFish Server considers the connection invalid and removes the connection from the connection pool. Typically, a JDBC driver generates a ConnectionErrorOccured event when it finds a ManagedConnection object unusable. Reasons can be database failure, network failure with the database, fatal problems with the connection pool, and so on.

If the fail-all-connections setting in the connection pool configuration is set to true, and a single connection fails, all connections are closed and recreated. If this setting is false, individual connections are recreated only when they are used. The default is false.

The is-connection-validation-required setting specifies whether connections have to be validated before being given to the application. If a resource’s validation fails, it is destroyed, and a new resource is created and returned. The default is false.

The prefer-validate-over-recreate property specifies that validating idle connections is preferable to closing them. This property has no effect on non-idle connections. If set to true, idle connections are validated during pool resizing, and only those found to be invalid are destroyed and recreated. If false, all idle connections are destroyed and recreated during pool resizing. The default is false.

You can set the fail-all-connections, is-connection-validation-required, and prefer-validate-over-recreate configuration settings during creation of a JDBC connection pool. Or, you can use the asadmin set command to dynamically reconfigure a setting. For example:

asadmin set"true"
asadmin set"true"
asadmin set"true"

For details, see the Oracle GlassFish Server 3.0.1 Reference Manual.

The interface ValidatingManagedConnectionFactory exposes the method getInvalidConnections to allow retrieval of the invalid connections. The GlassFish Server checks if the JDBC driver implements this interface, and if it does, invalid connections are removed when the connection pool is resized.

Connection Wrapping

The following features pertain to connection wrapping:

Wrapping Connections

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

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.

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

Obtaining a Physical Connection From a Wrapped Connection

The DataSource implementation in the GlassFish Server provides a getConnection method that retrieves the JDBC driver’s SQLConnection from the GlassFish 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) 
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 GlassFish 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.


The following features pertain to transactions:

Using Non-Transactional Connections

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

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 15, Using the Transaction Service and Chapter 21, Administering Transactions, in Oracle GlassFish Server 3.0.1 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 GlassFish Server permits specifying any isolation level your database supports. The following table defines transaction isolation levels.

Table 14–1 Transaction Isolation Levels

Transaction Isolation Level 



Dirty reads, non-repeatable reads, and phantom reads can occur. 


Dirty reads are prevented; non-repeatable reads and phantom reads can occur. 


Dirty reads and non-repeatable reads are prevented; phantom reads can occur. 


Dirty reads, non-repeatable reads and phantom reads are prevented. 

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

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 JDBC 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)
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.

Other Features

The following additional features related to JDBC are provided:

Allowing Non-Component Callers

You can allow non-Java-EE components, such as servlet filters, lifecycle modules, 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:

Accessing a DataSource using the Synchronization.beforeCompletion() method requires setting Allow Non Component Callers to true. For more information about the Transaction Synchronization Registry, see The Transaction Manager, the Transaction Synchronization Registry, and UserTransaction.

Restrictions and Optimizations

This section discusses restrictions and performance optimizations that affect using the JDBC API.

Disabling Stored Procedure Creation on Sybase

By default, DataDirect and Oracle JDBC drivers for Sybase databases create a stored procedure for each parameterized PreparedStatement. On the GlassFish Server, exceptions are thrown when primary key identity generation is attempted. To disable the creation of these stored procedures, set the property PrepareMethod=direct for the JDBC connection pool.