Skip Headers
Oracle® Universal Connection Pool for JDBC Developer's Guide,
11g Release 1 (11.1.0.7.0)

E10788-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

3 Getting Database Connections

The following sections are included in this chapter:

Borrowing Connections

An application borrows connections using a pool-enabled data source. The UCP for JDBC API provides two pool-enabled data sources; one for borrowing regular connections; and one for borrowing XA connections. These data sources provide access to UCP JDBC connection pool functionality and include a set of getConnection methods that are used to borrow connections. The same pool features are included in both XA and non-XA UCP JDBC connection pools.

UCP JDBC connection pools maintain both available connections and borrowed connections. A connection is reused from the pool if an application requests to borrow a connection that matches an available connection. A new connection is created if no available connection in the pool match the requested connection. The number of available connections and borrowed connections are subject to pool properties that control pool size, timeouts, and validation rules.

Note:

The instructions in this section use a pool-enabled data source to implicitly create and start a connection pool. See Chapter 6, "Using the Connection Pool Manager"for instructions on using the connection pool manager to explicitly create a connection pool.

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");

Note:

See the Oracle Database JDBC Developer's Guide and Reference for detailed Oracle URL syntax usage.

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," 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," 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 MySQL's Connector/J JDBC driver:

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.

Note:

See the Oracle Database JDBC Developer's Guide and Reference for a detailed list of supported properties.

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);

Setting Connection Pool Properties

UCP JDBC connection pools are configured using connection pool properties. The properties have get and set methods that are available through a pool-enabled data source instance. The methods are a convenient way to programmatically configure a pool. If no pool properties are set, then a connection pool uses default property values.

The following example demonstrates configuring connection pool properties. The example sets the connection pool name and the maximum/minimum number of connections allowed in the pool. See Chapter 4, "Optimizing Connection Pool Behavior," for a detailed description of all the supported properties as well as their default values.

PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();

pds.setConnectionPoolName("JDBC_UCP");
pds.setMinPoolSize(4);pds.setMaxPoolSize(20);

UCP JDBC connection pool properties may be set in any order and can be dynamically changed at runtime. For example, setMaxPoolSize could be changed at any time and the pool recognizes the new value and adapts accordingly.

Validating Connections

Connections can be validated using pool properties when the connection is borrowed, and also programmatically using the ValidConnection interface. Both approaches are detailed in this section. Invalid connections can affect application performance and availability.

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.

Note:

The setSQLForValidateConnection property is not recommended when using an Oracle JDBC driver. UCP for JDBC performs an internal ping when using an Oracle JDBC driver. The mechanism is faster than executing an SQL statement and is overridden if this property is set. Instead, set the setValidateConnectionOnBorrow property to true and do not include the setSQLForValidateConnection property.

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

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 the Pool" 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 7, "Using Oracle RAC Features," for more information on Fast Connection Failover.

Note:

The isValid method checks with the pool instance and Oracle JDBC driver to determine whether a connection is still valid. The isValid method results in a roundtrip to the database only if both the pool and the driver report that a connection is still valid. The roundtrip is used to check for database failures that are not immediately discovered by the pool or the driver.

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
}

Returning Borrowed Connections

Borrowed connections that are no longer being used should be returned to the pool so that they can be available for the next connection request. The close method is used to close connections and automatically returns the connections to the pool. The close method does not physically remove the connection from the pool.

Borrowed connections that are not closed will remain borrowed; subsequent requests for a connection result in a new connection being created if no connections are available. This behavior can cause many connections to be created and can affect system performance.

The following example demonstrates closing a connection and returning it to the pool:

Connection conn = pds.getConnection();

//do some work with the connection.

conn.close();
conn=null;

Removing Connections From the Pool

The setInvalid method of the ValidConnection interface indicates that a connection should be removed from the connection pool when it is closed. The method is typically used when a connection is no longer usable, such as after an exception or if the isValid method of the ValidConnection interface returns false. The method can also be used if an application deems the state on a connection to be bad. The following example demonstrates using the setInvalid method to close and remove a connection from the pool:

Connection conn = pds.getConnection();
...

((ValidConnection) conn).setInvalid();
...

conn.close();
conn=null;

Third-Party Integration

Third-party products, such as middleware platforms or frameworks, can use UCP to provide connection pooling functionality for their applications and services. UCP integration includes the same connection pool features that are available to stand-alone applications and offers the same tight integration with the Oracle Database.

Two data source classes are available as integration points with UCP: PoolDataSourceImpl for non-XA connection pools and PoolXADataSourceImpl for XA connection pools. Both classes are located in the oracle.ucp.jdbc package. These classes are implementations of the PoolDataSource and PoolXADataSource interfaces, respectively, and contain default constructors. For more information on the implementation classes refer to the Oracle Universal Connection Pool Java API Reference.

These implementations explicitly create connection pool instances and can return connections. For example:

PoolXADataSource pds = new PoolXADataSourceImpl();

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();

Third-party products can instantiate these data source implementation classes. In addition, the methods of these interfaces follow the JavaBean design pattern and can be used to set connection pool properties on the class using reflection. For example, a UCP data source that uses an Oralce JDBC connection factory and database might be defined as follows and loaded into a JNDI registry:

<data-sources>
   <data-source
      name="UCPDataSource"
      jndi-name="jdbc/UCP_DS"
      data-source-class="oracle.ucp.jdbc.PoolDataSourceImpl">
      <property name="ConnectionFactoryClassName"
                value="oracle.jdbc.pool.OracleDataSource"/>
      <property name="URL" value="jdbc:oracle:thin:@//localhost:1521:oracle"/>
      <property name="User" value"user"/>
      <property name="Password" value="password"/>
      <property name="ConnectionPoolName" value="MyPool"/>
      <property name="MinPoolSize" value="5"/>
      <property name="MaxPoolSize" value="50"/>
   </data-source>
</data-sources>

When using reflection, the name attribute matches (case sensitive) the name of the setter method used to set the property. An application could then use the data source as follows:

Connection connection = null;
try {
   InitialContext context = new InitialContext();
   DataSource ds = (DataSource) context.lookup( "jdbc/UCP_DS" );
   connection = ds.getConnection();
   ...