Using Oracle Universal Connection Pool with Spring from JDeveloper 11.1.2

In this example we simply setup Oracle UCP to be used by Spring from JDeveloper 11g 11.1.2. When using read write backing maps with Oracle Coherence that are using a back end database such as RAC it's UCP RAC integration which makes this UCP the ideal choice. In this example we are just using a single instance Oracle database but can easily add the FCF properties required for UCP.

1. Create a spring framework beans.xml file as follows

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" 
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans 
                           http://www.springframework.org/schema/beans/spring-beans.xsd">
    <bean id="ucpDataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
      <property name="URL" value="jdbc:oracle:thin:@beast.au.oracle.com:1524/linux11gr2" />
      <property name="user" value="scott" />
      <property name="password" value="tiger" />
      <property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleDataSource" />
      <property name="connectionPoolName" value="TEST_POOL" />
      <property name="minPoolSize" value="1" />
      <property name="maxPoolSize" value="10" />
      <property name="initialPoolSize" value="1" />
    </bean>
    
  <bean id="ucpJDBCTest" class="pas.au.spring.demo.ucp.test.TestJDBC">
    <property name="dataSource" ref="ucpDataSource"/>
  </bean>
  
</beans> 

2. Add the required libraries to the project

3. Create the TestJDBC class as follows

package pas.au.spring.demo.ucp.test;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

public class TestJDBC
{
  private JdbcTemplate jdbcTemplate;

  public void setDataSource(DataSource dataSource)
  {
    this.jdbcTemplate = new JdbcTemplate(dataSource);
  }
  
  public void insertDept()
  {
    /**
     * Specify the insert values 
     */
    jdbcTemplate.update("insert into dept values (:1, :2, :3)", 
                        new Object[] 
                        { 
                          Integer.valueOf(89),
                          "Pas", 
                          "Spring"
                        });
  }
}

4. Create Main test class as follows.

package pas.au.spring.demo.ucp.test;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class Main
{
  public static void main(String[] args)
  {
    ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
    TestJDBC testJDBC = (TestJDBC) context.getBean("ucpJDBCTest");
    testJDBC.insertDept();
    System.out.println("all done..");
  }
}

5. Run and verify output as follows

Aug 15, 2011 8:52:42 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh

INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@b753f8: startup date [Mon Aug 15 08:52:42 EST 2011]; root of context hierarchy

Aug 15, 2011 8:52:42 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions

INFO: Loading XML bean definitions from class path resource [beans.xml]

Aug 15, 2011 8:52:43 AM org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons

INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@3411a: defining beans [ucpDataSource,ucpJDBCTest]; root of factory hierarchy

all done..

6. Verify insertion from SQL*Plus

c:\temp>sqlplus scott/tiger@linux11gr2

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 15 08:53:31 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 MARKETING      MELBOURNE
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        89 Pas            Spring

6 rows selected.