The following sections are included in this chapter:
UCP for JDBC has the following design-time and run-time requirements:
A JDBC diver or a connection factory class capable of returning a java.sql.Connection
and javax.sql.XAConnection
object
Oracle drivers from releases 10.1 or higher are supported. Advanced Oracle Database features, such as Oracle RAC and Fast Connection Failover, require the Oracle Notification Service library (ons.jar
) that is included with the Oracle Client software.
The ucp.jar
library must be included in an application's classpath.
A database that supports SQL. Advanced features, such as Oracle RAC and Fast Connection Failover, require an Oracle Database.
UCP for JDBC provides a pool-enabled data source that is used by applications to borrow connections from a UCP JDBC connection pool. A connection pool is not explicitly defined for the most basic use case. Instead, a default connection pool is implicitly created when the connection is borrowed.
The following steps describe how to get a connection from a UCP for JDBC pool-enabled data source in order to access a database. The complete example is provided in Example 2-1, "Basic Connection Example":
Use the UCP for JDBC data source factory (oracle.ucp.jdbc.PoolDataSourceFactory
) to get an instance of a pool-enabled data source using the getPoolDataSource
method. The data source instance must be of the type PoolDataSource
. For example:
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
Set the connection properties that are required to get a physical connection to a database. These properties are set on the data source instance and include: the URL, the user name, and password to connect to the database and the connection factory used to get the physical connection. These properties are specific to a JDBC driver and database. For example:
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource"); pds.setURL("jdbc:oracle:thin:@//localhost:1521/XE"); pds.setUser("<user>"); pds.setPassword("<password>");
Set any pool properties in order to override the connection pool's default behavior. the pool properties are set on the data source instance. For example:
pds.setInitialPoolSize(5);
Get a connection using the data source instance. The returned connection is a logical handle to a physical connection in the data source's connection pool. For example:
Connection conn = pds.getConnection();
Use the connection to perform some work on the database:
Statement stmt = conn.createStatement (); stmt.execute("SELECT * FROM foo");
Close the connection and return it to the pool.
conn.close();
The following example is a program that connects to a database to do some work and then exits. The example is simple and in some cases not very practical; however, it does demonstrate the basic steps required to get a connection from a UCP for JDBC pooled-enabled data source in order to access a database.
Example 2-1 Basic Connection Example
import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import oracle.ucp.jdbc.PoolDataSourceFactory; import oracle.ucp.jdbc.PoolDataSource; public class BasicConnectionExample { public static void main(String args[]) throws SQLException { try { //Create pool-enabled data source instance. PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource(); //set the connection properties on the data source. pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource"); pds.setURL("jdbc:oracle:thin:@//localhost:1521/XE"); pds.setUser("<user>"); pds.setPassword("<password>"); //Override any pool properties. pds.setInitialPoolSize(5); //Get a database connection from the datasource. Connection conn = pds.getConnection(); System.out.println("\nConnection obtained from " + "UniversalConnectionPool\n"); //do some work with the connection. //Statement stmt = conn.createStatement (); //stmt.execute("select * from foo"); //Close the Connection. conn.close(); conn=null; System.out.println("Connection returned to the " + "UniversalConnectionPool\n"); } catch(SQLException e) { System.out.println("BasicConnectionExample - " + "main()-SQLException occurred : " + e.getMessage()); } } }
The following section provides a quick overview of the most commonly used packages of the UCP for JDBC API. Refer to the Oracle Universal Connection Pool Java API Reference for complete details on the API.
This package includes various interfaces and classes that are used by applications to work with JDBC connections and a connection pool. Among the interfaces found in this package, the PoolDataSource
and PoolXADataSource
data source interfaces are used by an application to get connections as well as get and set connection pool properties. Data source instances implementing these two interfaces automatically create a connection pool.
This package includes interfaces for using a connection pool manager as well as MBeans that allow users to access connection pool and the connection pool manager operations and attributes using JMX operations. Among the interfaces, the UniversalConnectionPoolManager
interface provides methods for creating and maintaining connection pool instances.
This package includes both required and optional callback interfaces that are used to implement connection pool features. For example, the ConnectionAffinityCallback
interface is used to create a callback that enables or disables connection affinity and can also be used to customize connection affinity behavior. As another example, the abandon connection feature returns a borrowed connection to the pool after it has been inactive for a specified amount of time. The AbandonedConnectionTimeoutCallback
interface can be used to customize whether or not a connection should be returned to the pool after the abandonment timeout occurs and also allows a connection to be processed before it is returned to the pool.
UCP for JDBC leverages the JDK logging facility (java.util.logging
). Logging is not enabled by default and must be configured in order to print log messages. Logging can be configured using a log configuration file as well as through API-level configuration.
Note:
The default log level isnull
. This ensures that a parent logger's log level is used by default.Logging can be configured using a properties file. The location of the properties file must be set as a Java property for the logging configuration file property. For example:
java -Djava.util.logging.config.file=log.properties
The logging properties file defines the handler to use for writing logs, the formatter to use for formatting logs, a default log level, as well as log levels for specific packages or classes. For example:
handlers = java.util.logging.ConsoleHandler java.util.logging.ConsoleHandler.level = ALL java.util.logging.ConsoleHandler.formatter = java.util.logging.SimpleFormatter oracle.ucp.level = FINEST oracle.ucp.jdbc.PoolDataSource = WARNING
A custom formatter is included with UCP for JDBC and can be entered as the value for the formatter property. For example:
java.util.logging.ConsoleHandler.formatter = oracle.ucp.util.logging.UCPFormatter
Logging can be dynamically configured though either the UCP for JDBC API or the JDK API. When using the UCP for JDBC API, logging is configured using a connection pool manager. When using the JDK, logging is configured using the java.util.logging
implementation.
The following example demonstrates using the UCP for JDBC API to configure logging:
UniversalConnectionPoolManager mgr = UniversalConnectionPoolManagerImpl. getUniversalConnectionPoolManager(); mgr.setLogLevel(Level.FINE);
The following example demonstrate using the JDK logging implementation directly:
Logger.getLogger("oracle.ucp").setLevel(Level.FINEST); Logger.getLogger("oracle.ucp.jdbc.PoolDataSource").setLevel(Level.FINEST);
The following list describes each of the log levels that are supported for JDBC. Levels lower than FINE
produce output that may not be meaningful to users. Levels lower than FINER
will produce very large volumes of output.
INTERNAL_ERROR
– Internal Errors
SEVERE
– SQL Exceptions
WARNING
– SQL Warnings and other invisible problems
INFO
– Public events such as connection attempts or Oracle RAC events
CONFIG
– SQL statements
FINE
– Public APIs
TRACE_10
– Internal events
FINER
– Internal APIs
TRACE_20
– Internal debug
TRACE_30
– High volume internal APIs
FINEST
– High volume internal debug