Using the JDBC API for Database Access

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:

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:

If statements are not closed by an application after use, it is possible for the application to run out of cursors. Enabling statement leak detection causes statements to be considered as leaked if they are not closed within a specified period. Additionally, leaked statements can reclaimed automatically.

To enable statement leak detection, set Statement Leak Timeout In Seconds for the JDBC connection pool to a positive, nonzero value in one of the following ways:

When selecting a value for Statement Leak Timeout In Seconds, make sure that:

After enabling statement leak detection, enable leaked statement reclamation by setting Reclaim Leaked Statements for the JDBC connection pool to a true value in one of the following ways:

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 Eclipse GlassFish 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.

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 Eclipse GlassFish 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 Eclipse GlassFish, place the implementation classes in as-install/lib.

The Eclipse GlassFish 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 jakarta.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:

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

When SQL statement tracing is enabled and JDBC connection pool monitoring is enabled, Eclipse GlassFish maintains a tracing cache of recent queries and their frequency of use. The following JDBC connection pool properties can be configured to control this cache and the monitoring statistics available from it:

Set these parameters in one of the following ways:

When the properties or attributes of a JDBC connection pool are changed, the connection pool is destroyed and re-created. Normally, applications using the connection pool must be redeployed as a consequence. This restriction can be avoided by enabling transparent JDBC connection pool reconfiguration. When this feature is enabled, applications do not need to be redeployed. Instead, requests for a new connections are blocked until the reconfiguration operation completes. Connection requests from any in-flight transactions are served using the old pool configuration so as to complete the transaction. Then, connections are created using the pool’s new configuration, and any blocked connection requests are served with connections from the re-created pool..

To enable transparent JDBC connection pool reconfiguration, set the dynamic-reconfiguration-wait-timeout-in-seconds property of the JDBC connection pool to a positive, nonzero value in one of the following ways:

This property specifies the time in seconds to wait for in-use connections to close and in-flight transactions to complete. Any connections in use or transaction in flight past this time must be retried.

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:

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:

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 Eclipse GlassFish 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 DB2, Apache Derby, MSSQL, MySQL, Oracle, PostgreSQL and Sybase databases. Additionally, for JDBC 4.0 compliant database drivers, a validation mechanism is provided that uses the Connection.isValid(0) implementation.

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 Jakarta 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 "Administering Database Connectivity" in Eclipse GlassFish Administration Guide.

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

For example:

If a ConnectionErrorOccured event occurs, the Eclipse GlassFish 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:

For details, see the Eclipse GlassFish Reference Manual.

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

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:

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

For example:

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.

By default, DataDirect and Oracle JDBC drivers for Sybase databases create a stored procedure for each parameterized PreparedStatement. On the Eclipse GlassFish, 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.