3 Getting Database Connections in UCP

Using the Pool-Enabled Data Source

UCP for JDBC provides a pool-enabled data source (oracle.ucp.jdbc.PoolDataSource) that is used to get connections to a database. The oracle.ucp.jdbc.PoolDataSourceFactory factory class provides a getPoolDataSource() method that creates the pool-enabled data source instance. For example:

PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();

The pool-enabled data source requires a connection factory class in order to get an actual physical connection. The connection factory is typically provided as part of a JDBC driver and can be a data source itself. A UCP JDBC connection pool can use any JDBC driver to create physical connections that are then maintained by the pool. The setConnectionFactoryClassName(String) method is used to define the connection factory for the pool-enabled data source instance. The following example uses Oracle's oracle.jdbc.pool.OracleDataSource connection factory class included with the JDBC driver. If you are using a different vendor's JDBC driver, refer to the vendor's documentation for an appropriate connection factory class.

pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");

In addition to the connection factory class, a pool-enabled data source requires the URL, user name, and password that is used to connect to a database. A pool-enabled data source instance includes methods to set each of these properties. The following example uses an Oracle JDBC Thin driver syntax. If you are using a different vendor's JDBC driver, refer to the vendor's documentation for the appropriate URL syntax to use.

pds.setURL("jdbc:oracle:thin:@//localhost:1521/XE");
pds.setUser("user");
pds.setPassword("password");

Lastly, a pool-enabled data source provides a set of getConnection methods. The methods include:

• getConnection() – Returns a connection that is associated with the user name and password that was used to connect to the database.

• getConnection(String username, String password) – Returns a connection that is associated with the given user name and password.

• getConnection(java.util.Properties labels) – Returns a connection that matches a given label. See Chapter 5, "Labeling Connections in UCP," for detailed information on using connection labels.

• getConnection(String username, String password, java.util.Properties labels) – Returns a connection that is associated with a given user name and password and that matches a given label. See Chapter 5, "Labeling Connections in UCP," for detailed information on using connection labels.

An application uses the getConnection methods to borrow a connection handle from the pool that is of the type java.sql.Connection. If a connection handle is already in the pool that matches the requested connection (same URL, user name, and password) then it is returned to the application; or else, a new connection is created and a new connection handle is returned to the application. An example for both Oracle and MySQL are provided.

Oracle Example

The following example demonstrates borrowing a connection when using Oracle's JDBC Thin driver:

PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();

pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL("jdbc:oracle:thin:@//localhost:1521/XE");
pds.setUser("<user>");
pds.setPassword("<password>");

Connection conn = pds.getConnection();

MySQL Example

The following example demonstrates borrowing a connection when using the Connector/J JDBC driver from MySQL:

PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();

pds.setConnectionFactoryClassName("com.mysql.jdbc.jdbc2.optional.    MysqlDataSource");
pds.setURL("jdbc:mysql://host:3306/dbname");
pds.setUser("<user>");
pds.setPassword("<password>");

Connection conn = pds.getConnection();

Using the Pool-Enabled XA Data Source

UCP for JDBC provides a pool-enabled XA data source (oracle.ucp.jdbc.PoolXADataSource) that is used to get XA connections that can be enlisted in a distributed transaction. UCP JDBC XA pools have the same features as non-XA UCP JDBC pools. The oracle.ucp.jdbc.PoolDataSourceFactory factory class provides a getPoolXADataSource() method that creates the pool-enabled XA data source instance. For example:

PoolXADataSource  pds = PoolDataSourceFactory.getPoolXADataSource();

A pool-enabled XA data source instance, like a non-XA data source instance, requires the connection factory, URL, user name, and password in order to get an actual physical connection. These properties are set in the same way as a non-XA data source instance (see above). However, an XA-specific connection factory class is required to get XA connections. The XA connection factory is typically provided as part of a JDBC driver and can be a data source itself. The following example uses Oracle's oracle.jdbc.xa.client.OracleXADataSource XA connection factory class included with the JDBC driver. If a different vendor's JDBC driver is used, refer to the vendor's documentation for an appropriate XA connection factory class.

pds.setConnectionFactoryClassName("oracle.jdbc.xa.client.OracleXADataSource");
pds.setURL("jdbc:oracle:thin:@//localhost:1521/XE");
pds.setUser("user");
pds.setPassword("password");

Lastly, a pool-enabled XA data source provides a set of getXAConnection methods that are used to borrow a connection handle from the pool that is of the type javax.sql.XAConnection. The getXAConnection methods are the same as the getConnection methods previously described. The following example demonstrates borrowing an XA connection.

PoolXADataSource  pds = PoolDataSourceFactory.getPoolXADataSource();

pds.setConnectionFactoryClassName("oracle.jdbc.xa.client.OracleXADataSource");
pds.setURL("jdbc:oracle:thin:@//localhost:1521/XE");
pds.setUser("<user>");
pds.setPassword("<password>");

XAConnection conn = pds.getXAConnection();

Setting Connection Properties

Oracle's connection factories support properties that configure connections with specific features. UCP for JDBC pool-enabled data sources provide the setConnectionProperties(Properties) method, which is used to set properties on a given connection factory. The following example demonstrates setting connection properties for Oracle's JDBC driver. If you are using a different vendor's JDBC driver, refer to their documentation to check whether setting properties in this manner is supported and what properties are available:

Properties connProps = new Properties();
connProps.put("fixedString", false);
connProps.put("remarksReporting", false);
connProps.put("restrictGetTables", false);
connProps.put("includeSynonyms", false);
connProps.put("defaultNChar", false);
connProps.put("AccumulateBatchResult", false);

pds.setConnectionProperties(connProps);

The UCP JDBC connection pool does not remove connections that are already created if setConnectionProperties is called after the pool is created and in use.

Using JNDI to Borrow a Connection

A connection can be borrowed from a connection pool by performing a JNDI look up for a pool-enabled data source and then calling getConnection() on the returned object. The pool-enabled data source must first be bound to a JNDI context and a logical name. This assumes that an application includes a Service Provider Interface (SPI) implementation for a naming and directory service where object references can be registered and located.

The following example uses Sun's file system JNDI service provider, which can be downloaded from the JNDI software download page:

http://java.sun.com/products/jndi/downloads/index.html

The example demonstrates creating an initial context and then performing a lookup for a pool-enabled data source that is bound to the name MyPooledDataSource. The object returned is then used to borrow a connection from the connection pool.

Hashtable env = new Hashtable();
env.put(Context.INITIAL_CONTEXT_FACTORY,   
   "com.sun.jndi.fscontext.RefFSContextFactory");
env.put(Context.PROVIDER_URL, "file:/tmp");

ctx = new InitialContext(env);

PoolDataSource jpds = (PoolDataSource)ctx.lookup(MyPooledDataSource);
Connection conn = jpds.getConnection();

In the example, MyPoolDataSource must be bound to the context. For example:

PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();

pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL("jdbc:oracle:thin:@//localhost:1521/XE");
pds.setUser("<user>");
pds.setPassword("<password>");

ctx.bind(MyPooledDataSource, pds);

Validate When Borrowing

A connection can be validated by executing an SQL statement on a connection when the connection is borrowed from the connection pool. Two connection pool properties are used in conjunction in order to enable connection validation:

• setValidateConnectionOnBorrow(boolean) – Specifies whether or not connections are validated when the connection is borrowed from the connection pool. The method enables validation for every connection that is borrowed from the pool. A value of false means no validation is performed. The default value is false.

• setSQLForValidateConnection(String) – Specifies the SQL statement that is executed on a connection when it is borrowed from the pool.

The following example demonstrates validating a connection when borrowing the connection from the pool. The example uses Connector/J JDBC driver from MySQL:

PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();

pds.setConnectionFactoryClassName("com.mysql.jdbc.jdbc2.optional.    MysqlDataSource");
pds.setURL("jdbc:mysql://host:3306/mysql");
pds.setUser("<user>");
pds.setPassword("<password>");

pds.setValidateConnectionOnBorrow(true);
pds.setSQLForValidateConnection("select * from mysql.user");

Connection conn = pds.getConnection();

Checking If a Connection Is Valid

The oracle.ucp.jdbc.ValidConnection interface provides two methods: isValid and setInvalid. The isValid method returns whether or not a connection is usable and the setInvalid method is used to indicate that a connection should be removed from the pool instance. See "Removing Connections from UCP" for more information on using the setInvalid method.

The isValid method is used to check if a connection is still usable after an SQL exception has been thrown. This method can be used at any time to check if a borrowed connection is valid. The method is particularly useful in combination with a retry mechanism, such as the Fast Connection Failover actions that are triggered after a RAC-down event. See Chapter 8, "Using Oracle RAC Features," for more information on Fast Connection Failover.

The isValid method is also helpful when used in conjunction with the connection timeout and connection harvesting features. These features may return a connection to the pool while a connection is still held by an application. In such cases, the isValid method returns false, allowing the application to get a new connection.

The following example demonstrates using the isValid method:

try {  conn = poolDataSouorce.getConnection  ...}catch (SQLException sqlexc)
{
   if (conn == null || !((ValidConnection) conn).isValid())
        
   // take the appropriate action
   
...
conn.close();
}