Monday, 15 August 2011

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="" 
    <bean id="ucpDataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
      <property name="URL" value="" />
      <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 id="ucpJDBCTest" class="">
    <property name="dataSource" ref="ucpDataSource"/>

2. Add the required libraries to the project

3. Create the TestJDBC class as follows

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[] 

4. Create Main test class as follows.

import org.springframework.context.ApplicationContext;

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

5. Run and verify output as follows

Aug 15, 2011 8:52:42 AM prepareRefresh
INFO: Refreshing 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 preInstantiateSingletons
INFO: Pre-instantiating singletons in 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 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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from dept;

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

6 rows selected.

1 comment:

Dragasevic said...

Thanks for the info, very useful, and now I'm thinking I used to work with you for Oracle in Melbourne.