Sun GlassFish Enterprise Server v2.1.1 Developer's Guide

Chapter 15 Using the JDBC API for Database Access

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

The JDBC specifications are available at

A useful JDBC tutorial is located at

Note –

The Enterprise 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 Enterprise Server, perform the following tasks:

For information about how to configure some specific JDBC drivers, see Configurations for Specific JDBC Drivers in Sun GlassFish Enterprise Server v2.1.1 Administration Guide.

Integrating the JDBC Driver

To use JDBC features, you must choose a JDBC driver to work with the Enterprise 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 Sun. For a list of the JDBC drivers currently supported by the Enterprise Server, see the Sun GlassFish Enterprise Server v2.1.1 Release Notes. For configurations of supported and other drivers, see Configurations for Specific JDBC Drivers in Sun GlassFish Enterprise Server v2.1.1 Administration Guide.

Note –

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

Making the JDBC Driver JAR Files Accessible

To integrate the JDBC driver into a Enterprise 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 Enterprise Server class loaders, see Chapter 2, Class Loaders.

Creating a Connection Pool

When you create a connection pool that uses JDBC technology (a JDBC connection pool) in the Enterprise 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 Sun GlassFish Enterprise Server v2.1.1 Administration Guide

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.

For information about how to tune a connection pool, see the Sun GlassFish Enterprise Server v2.1.1 Performance Tuning Guide.

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:

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 3, JDBC Resources, in Sun GlassFish Enterprise Server v2.1.1 Administration Guide.

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) 
Connection con = ds.getConnection();
Connection drivercon = ds.getConnection(con);
// Do db operations.
// Do not close driver connection.
con.close(); // return wrapped connection to pool.

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=
Connection con = ds.getConnection();
Statement stmt = null;
   stmt = con.createStatement();
catch (BadConnectionException e){
   dataSource.markConnectionAsBad(con) //marking it as bad for removal
   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:

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 16, Using the Transaction Service and Chapter 12, Transactions, in Sun GlassFish Enterprise Server v2.1.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 Enterprise Server permits specifying any isolation level your database supports. The following table defines transaction isolation levels.

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

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)
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, 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:

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 Sun GlassFish JDBC drivers for Sybase databases create a stored procedure for each parameterized PreparedStatement. On the Enterprise Server, exceptions are thrown when primary key identity generation is attempted. To disable the creation of these stored procedures, set the property PrepareMethod=direct.