Thursday Sep 17, 2015

Using Universal Connection Pooling (UCP) with JBoss AS

Even though WebLogic with Active GridlLink is Oracle's suggested approach to deploy Java applications that use Oracle Real Applications Clusters (RAC), there might be scenarios in which you can't make that choice (e.g.: certification issues, licensing, library dependency, etc.). Application servers and their database connection pool mechanisms might end up being a bottleneck in the architecture of your applications and a critical component to provide performance, scalability and high-availability.

UCP, besides providing connection pooling and all its intrinsic benefits, leverages features specific from RAC.
These features are:

  • Runtime Connection Load Balancing (RCLB)
  • Fast Connection Failover (FCF)
  • Transaction Affinity
  • Built-in support for Database Resident Connection Pooling (DRCP) and Application Continuity (AC)

You won't be able to exploit this features by using out of the box JBoss' connection pools.

In this simple example we will show you how to configure UCP to be used in a JBoss deployed application, wrapping UCP around a Singleton Enterprise Java Bean (EJB) and using a Servlet as client to the wrapper. We will also show you how to monitor and administer UCP using JBoss JMX-Console without needing extra configuration, coding or deployment.

In an ideal scenario the application server would provide us the possibility to set a custom implementation of a connection pooling (by implementing an interface, for example), but that is not the case with JBoss. In JBoss you can configure all the necessary information to create a connection (driver, url, user, password, etc) and some configuration for the pool (minimum size, maximum size, etc), but you can't configure the class that is going to provide the implementation for connection pooling. There's a workaround for that in this article: , which explains how to set a UCP data-source using Spring). But for both scenarios you're declaring beans, or wrappers, to contain the UCP configuration.

1. Download and start JBoss AS:

You can download it from this site:
For this sample we used “JBoss AS 6.1.0.Final”:
After unzipping the file you should set your JAVA_HOME env variable to an existing JDK directory (jdk7, it won't work with jdk8).
To start the app server you go to the unzipped directory /bin and run or standalone.bat, depending on your OS (you can skip this step if you install JBoss Developer Studio.
You can check successful start-up by opening a browser to http://localhost:8080/

2. Download and install JBoss Developer Studio:

This is also an optional step, you can download JBoss Developer Studio from after unzipping you start it by running jbdevstudio executable. This is an extension of Eclipse with JBoss specific plugins installed.

JBoss Developer Studio

3. Configure JBoss AS in JBoss Developer Studio:

In “servers” view choose “new” / “server”, select “JBoss Community, JBoss AS 6.x”, select “Home Directory” to the previously downloaded JBoss AS and leave default values.
After this step you can start/stop application server from JBoss Developer Studio and deploy / un-deploy applications by selecting “Add and Remove..” in Server's contextual menu.

4. Add ucp.jar and ojdbc.jar both to Jboss's runtime:

Add both jars in (JBOSS_HOME/common/lib) and in project's build path (project / properties / Java Build Path / libraries). You could also deploy this jars as specifics for the app, off course.

5. Create the Singleton Wrapper for UCP as this:

@Singleton(name = "UcpDemoSingletonWrapper")
public class UcpDemoSingletonWrapper {

@Resource(name = "connectionFactoryClassName")
String connectionFactoryClassName;

@Resource(name = "url")
String url;

@Resource(name = "user")
String user;

@Resource(name = "password")
String password;

@Resource(name = "initialPoolSize")
Integer initialPoolSize;

* Initialize method for the Singleton. This wrapper
* could also implement JMX APIs to be able to be
* managed through the JMX Console (or any other 
* JMX-compliant Tool)
void init() {

try {

// Retrieve values from bean's configuration

* Override example's desired pool
* properties, will be
* used through JMX Console
* later in this example

} catch (Exception e) {



// Wrapped pool
private PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();

* Retrieves connections using wrapped pool
* @return a pooled connection
* @throws SQLException
public Connection getConnection() throws SQLException {

* Trivial implementation to retrieve connections.
* Logic intrinsic to the pool could be set here.
return pds.getConnection();



6. Create EJB configuration file (WEB-INF/ejb-jar.xml) with this values:

<ejb-jar xmlns="" version="3.1" metadata-complete="false">





















7. Create the client as this sample servlet:

* Simple client to show how to use wrapped ucp's EJB
public class UcpDemoServletClient extends HttpServlet {

UcpDemoSingletonWrapper ucpWrapper;

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

doPost(request, response);


protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

try (

// Get connection from pool, for this trivial example we avoid ORM
Connection connection = ucpWrapper.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from test_table")) {

// Send output to response
PrintWriter printWriter = response.getWriter();

while ( {



} catch (SQLException sqlException) {

throw new ServletException(sqlException);




8. From Servers's view execute “Publish” and “Start” (or just execute from project's context menu “run in server”).

JBoss deploy

9. Administer EJB from JMX-Console:

Login to http://localhost:8080/jmx-console/
Look for UCP's MBEANs:

JMX Console

Invoke getConnectionPoolName (you will get what was setted on the EJB, “ucp_demo.UcpDemoSingletonWrapper").
Go through oracle.ucp.admin.UniversalConnectionPoolMBean and look for availableConnectionsCount.

10. Invoke servlet:

Typing in a browser http://localhost:8080/ucp_jboss_demo_web_project/UcpDemoServletClient
Check that after several executions the amount of available connections remains constant.
Do the same but not closing the connections in the wrapper (remove from the try with resources block, avoiding connection.close())
Or just execute Connection connection = ucpWrapper.getConnection(); without closing the connection.
You will check that available connections decreases until exception is thrown.
Use JMX-console to administer life-cycle of UCP (start / stop / purge connection pool, etc.).
A full-fledged test would include modifications in the RAC (shutting down/up nodes, checking load balance between them, validating transaction affinity, etc.), in these scenarios UCP would show all its capacities and features (as described at the beginning of this article).

Thursday May 21, 2015

UCP with Spring Framework

This article illustrates the steps to use Oracle Universal Connection Pool (UCP) with the Spring framework, using a sample application built using the JDBC template.

  • Assume there is a simple table EMP in the database with a single column “name” that is loaded with employee information (i.e., employee names).
  • Consider the following example DAO class in the package “test”:
  • The following is an example of the Row mapper implementation class for the EMP table:
  • The following class is the example of a java class that uses the JDBC Template for implementing the business logic:
  • The XML configuration file should specify UCP's oracle.ucp.jdbc.PoolDataSourceImpl as the data source class along with relevant connection pool properties, such as the initial-pool-size, max-pool-size, etc. For this sample, the XML configuration file is named "HelloAppConf.xml".
    <?xml version="1.0" encoding="UTF-8"?> <beans xmlns="" xmlns:xsi="" xsi:schemaLocation=""> <!-- Initialization for data source --> <bean id="dataSource" class="oracle.ucp.jdbc.PoolDataSourceImpl"> <property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleDataSource"/> <property name="URL" value="jdbc:oracle:thin:@//host:port/service_name"/> <property name="user" value="scott"/> <property name="password" value="tiger"/> <property name="maxPoolSize" value="10"/> <property name="initialPoolSize" value="5"/> </bean> <!-- Definition for EmpJDBCTemplate bean --> <bean id="EmpJDBCTemplate" class="test.EmpJDBCTemplate"> <property name="dataSource" ref="dataSource"/> </bean> </beans>
  • The main application code looks like the following. The application using the JDBC template will internally use the Universal Connection Pool (UCP) for connection check-outs and check-ins.

Use UCP with Hibernate

Hibernate ORM is an object/relational mapping framework for Java. Out-of-box, Hibernate supports two open source connection pools C3P0 and Proxool.

Universal Connection Pool (UCP) is Oracle’s feature-rich Java connection pool, replacing the Implicit Connection Cache (ICC), which has been de-supported in Oracle Database 12c. Besides standard connection pooling features, UCP has also been designed for scalability and high-availability during planned and unplanned database downtimes, with seamless support for Oracle Real Application Clusters (RAC), Active Data Guard (ADG) and Global Data Services (GDS).

This article illustrates a couple of options for using Oracle Universal Connection Pool (UCP) with Hibernate. The code samples are for demonstration purpose only.

Using UCP data source via JNDI lookup

If the target application can use JNDI to lookup a data source (for example, when using Tomcat), then users can specify a UCP data source for Hibernate to use, by using the Hibernate property "hibernate.connection.datasource". This can be done either declaratively in a Hibernate configuration file (XML or properties file), or programmatically on org.hibernate.cfg.Configuration.

For example, after binding a UCP PoolDataSource in JNDI, users can specify in hibernate.cfg.xml:

<hibernate-configuration> <session-factory> <property name="hibernate.connection.datasource"> java:comp/env/UCP_PoolDataSource_JNDI_NAME </property>

All the UCP connection pool and data source configurations should be done on the PoolDataSource, before binding it in JNDI.

Using UCP data source with Spring framework

If the target application can use Spring along with Hibernate, then UCP data sources can be configured in Spring configuration XML files.

For example:

<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="" xmlns:xsi="" xsi:schemaLocation=""> <!-- Initialization for data source --> <bean id="dataSource" class="oracle.ucp.jdbc.PoolDataSourceImpl"> <property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleDataSource"/> <property name="URL" value="jdbc:oracle:thin:@//host:port/service_name"/> <property name="user" value="scott"/> <property name="password" value="tiger"/> <property name="maxPoolSize" value="10"/> <property name="initialPoolSize" value="5"/> </bean> <!-- Definition for EmpJDBCTemplate bean --> <bean id="EmpJDBCTemplate" class="test.EmpJDBCTemplate"> <property name="dataSource" ref="dataSource"/> </bean> </beans>

Implementing UCP as a Hibernate ConnectionProvider

If the target application is standalone and cannot use the two options above, Hibernate provides the ConnectionProvider interface to integrate with a third-party JDBC connection provider. Users need to implement this interface and then specify the implementation class to Hibernate.

The ConnectionProvider interface has changed with different Hibernate versions. As of Hibernate 4.x, the interface is org.hibernate.engine.jdbc.connections.spi.ConnectionProvider. We will use that version for illustration.

The following is an example implementation of ConnectionProvider that plugs in a UCP data source. It has been tested using Hibernate 4.3.8. Note that the Hibernate 4.x version of the interface no longer provides the configure(Properties hibernateProperties) method, so this example simply overrides the DriverManagerConnectionProviderImpl which provides a similar method. An alternative is for the interface implementation to load the configuration properties from its own properties file or explicitly from the file.

/* Copyright (c) 2015 Oracle and/or its affiliates. All rights reserved. */ package oracle.ucp.hibernate.sample; import; import; import; import java.sql.Connection; import java.sql.SQLException; import java.util.Map; import java.util.logging.Logger; import oracle.ucp.UniversalConnectionPoolException; import oracle.ucp.admin.UniversalConnectionPoolManager; import oracle.ucp.admin.UniversalConnectionPoolManagerImpl; import oracle.ucp.jdbc.PoolDataSource; import oracle.ucp.jdbc.PoolDataSourceFactory; import org.hibernate.HibernateException; import org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl; import org.hibernate.engine.jdbc.connections.spi.ConnectionProvider; public class UCPConnectionProvider extends DriverManagerConnectionProviderImpl { private PoolDataSource pds; private static final Logger logger = Logger.getLogger(UCPConnectionProvider.class.getCanonicalName()); private static final String URL = "hibernate.ucp.url"; private static final String USER = "hibernate.ucp.user"; private static final String PASSWORD = "hibernate.ucp.password"; private static final String CONN_FACTORY = "hibernate.ucp.connectionFactoryClassName"; private static final String POOL_NAME = "hibernate.ucp.connectionPoolName"; private static final String MAX_POOL_SIZE = "hibernate.ucp.maxPoolSize"; private static final String MIN_POOL_SIZE = "hibernate.ucp.minPoolSize"; private static final String INITIAL_POOL_SIZE = "hibernate.ucp.initialPoolSize"; private static final String FAN_ENABLED = "hibernate.ucp.fastConnectionFailoverEnabled"; private static final String ONS_CONFIG = "hibernate.ucp.onsConfiguration"; private static final String CONN_VALIDATE = "hibernate.ucp.validateConnectionOnBorrow"; public UCPConnectionProvider() { try { pds = PoolDataSourceFactory.getPoolDataSource(); logger.finest("PoolDataSource initialized: " + pds); } catch (Exception exc) { logger.warning(getStackTraceString(exc)); } } public void configure(Map props) throws HibernateException { if(pds == null) throw new HibernateException("PoolDataSource was not initialized."); if (props == null) throw new HibernateException("Null configuration properties passed in."); try { logger.finest("Passed in properties: " + props); String tempval = (String) props.get(CONN_FACTORY); if (tempval != null) pds.setConnectionFactoryClassName(tempval); tempval = (String) props.get(URL); if (tempval != null) pds.setURL(tempval); tempval = (String) props.get(USER); if (tempval != null) pds.setUser(tempval); tempval = (String) props.get(PASSWORD); if (tempval != null) pds.setPassword(tempval); tempval = (String) props.get(POOL_NAME); if (tempval != null) pds.setConnectionPoolName(tempval); tempval = (String) props.get(MAX_POOL_SIZE); if (tempval != null) pds.setMaxPoolSize(Integer.parseInt(tempval)); tempval = (String) props.get(MIN_POOL_SIZE); if (tempval != null) pds.setMinPoolSize(Integer.parseInt(tempval)); tempval = (String) props.get(INITIAL_POOL_SIZE); if (tempval != null) pds.setInitialPoolSize(Integer.parseInt(tempval)); tempval = (String) props.get(FAN_ENABLED); if (tempval != null) pds.setFastConnectionFailoverEnabled(Boolean.parseBoolean(tempval)); tempval = (String) props.get(ONS_CONFIG); if (tempval != null) pds.setONSConfiguration(tempval); tempval = (String) props.get(CONN_VALIDATE); if (tempval != null) pds.setValidateConnectionOnBorrow(Boolean.parseBoolean(tempval)); } catch (SQLException sqlexc) { logger.warning(getStackTraceString(sqlexc)); } } public Connection getConnection() throws SQLException { final Connection conn = pds.getConnection(); logger.finest("Got connection " + conn + " from " + pds + ", number of available connections = " + pds.getAvailableConnectionsCount() + ", borrowed connections = " + pds.getBorrowedConnectionsCount()); return conn; } public void closeConnection(Connection conn) throws SQLException { conn.close(); logger.finest("Closed connection " + conn + " from " + pds + ", number of available connections = " + pds.getAvailableConnectionsCount() + ", borrowed connections = " + pds.getBorrowedConnectionsCount()); } public void close() { try { final UniversalConnectionPoolManager mgr = UniversalConnectionPoolManagerImpl.getUniversalConnectionPoolManager(); mgr.destroyConnectionPool(pds.getConnectionPoolName()); logger.finest("Closed PoolDataSource " + pds); } catch (UniversalConnectionPoolException exc) { logger.warning(getStackTraceString(exc)); } } public boolean supportsAggressiveRelease() { return true; } public boolean isUnwrappableAs(Class cls) { return false; } public <T> T unwrap(Class<T> cls) { return null; } private String getStackTraceString(Throwable exc) { final Writer stackTraceWriter = new StringWriter(1024); final PrintWriter pw = new PrintWriter(stackTraceWriter); exc.printStackTrace(pw); return stackTraceWriter.toString(); } }

With UCP ConnectionProvider implementation ready, application can declaratively specify the implementation class name using the Hibernate property "hibernate.connection.provider_class".

Below is an example hibernate.cfg.xml file including the UCP-specific configuration properties:

<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" ""> <!-- Copyright (c) 2015 Oracle and/or its affiliates. All rights reserved. --> <hibernate-configuration> <session-factory> <property name="hibernate.connection.provider_class"> oracle.ucp.hibernate.sample.UCPConnectionProvider </property> <property name="hibernate.ucp.url"> jdbc:oracle:thin:@//host:1521/service_name </property> <property name="hibernate.ucp.connectionFactoryClassName"> oracle.jdbc.pool.OracleDataSource </property> <property name="hibernate.ucp.user">scott</property> <property name="hibernate.ucp.password">tiger</property> <property name="hibernate.ucp.maxPoolSize">2</property> </session-factory> </hibernate-configuration>


In this article, we illustrated three options for using UCP with Hibernate. In all three cases, UCP will function as the connection pool for Hibernate’s JDBC connections and intercept JDBC connection checkouts and checkins without additional application code changes. This allows applications to utilize UCP’s full pooling capabilities including all the scalability and high-availability features for Oracle RAC, ADG, and GDS.

Wednesday Oct 09, 2013

Write recovery code with Transaction Guard

This article demonstrates how to use in Java a new 12C database feature called Transaction Guard.[Read More]

Phil Wang-Oracle


« October 2015