JRuby example using Oracle UCP (Universal Connection Pool)

The Oracle Universal Connection Pool (UCP) for JDBC is a full-featured connection pool for managing database connections not only for Oracle but also for other non Oracle databases. The advantage with using UCP with Oracle is that UCP JDBC connection pools provide a tight integration with various Oracle Real Application Clusters (RAC) Database features . The features include Fast Connection Failover (FCF), Run-Time Connection Load Balancing, and Connection Affinity.

In this example below we show how to use UCP with an Oracle Database from a JRuby client BUT just a single instance database rather then RAC back end.

Note: We are using the HR schema for this demo

1. Download ucp.jar and ojdbc6.jar from the links below.

ojdbc6.jar (11.2.0.2)
ucp.jar (11.2.0.1)

2. Create a pool class as follows in a JRuby script called "ucppool.rb" in a directory called "lib". Client would then create an instance of this pool class and use it to retrieve / return connections from it.

require 'java'
require 'C:/jdev/jdcbdrivers/11.2/ojdbc6.jar'
require 'C:/jdev/jdcbdrivers/11.2/ucp/ucp.jar'

import 'oracle.ucp.jdbc.PoolDataSource'
import 'oracle.ucp.jdbc.PoolDataSourceFactory'

API_VERSION = 1.0

class MyOracleUcpPool

  #pool data source object
  @pds
  
  def initialize(user, passwd, url, minsize=0, maxsize=10, initialsize=2)
    @user = user
    @passwd = passwd
    @url = url
    @minsize = minsize
    @maxsize = maxsize
    @initialsize = initialsize
    
    #create pool for use here
    @pds = PoolDataSourceFactory.getPoolDataSource()
    @pds.setUser(user)
    @pds.setPassword(passwd)
    @pds.setURL(url)
    @pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource")
    @pds.setConnectionPoolName("ruby-ucppool")
    @pds.setInitialPoolSize(initialsize)
    @pds.setMinPoolSize(minsize)
    @pds.setMaxPoolSize(maxsize)
    
  end
  
  #add getters and setters for all attrributes
  attr_reader :user, :passwd, :url, :minsize, :maxsize, :initialsize
  
  def getConnection()
    return @pds.getConnection()
  end
  
  def returnConnection(conn)
    conn.close()
  end
  
  def displayPoolDetails()
    return "\n** UCP Pool Details **\n" + 
            "NumberOfAvailableConnections: ", @pds.getAvailableConnectionsCount(), "\n" +
            "BorrowedConnectionsCount: ", @pds.getBorrowedConnectionsCount(), " \n";
  end
  
  def to_s
    "MyOracleUcpPool [user=#{@user}, passwd=#{@passwd}, " +
    "url=#{@url}, minsize=#{@minsize}, maxsize=#{@maxsize}, " +
    "initialsize=#{@initialsize}]"
  end
  alias_method :to_string, :to_s
  
 end

- Edit the 2 lines below to indicate the PATH to the 2 JAR files downloaded at step #1


require 'C:/jdev/jdcbdrivers/11.2/ojdbc6.jar'
require 'C:/jdev/jdcbdrivers/11.2/ucp/ucp.jar'


3. In your current directory create a test client to verify the UCP called "testucp.rb" as follows to test your UCP Pool.

require 'lib/ucppool'

print "Run at ", Time.now , "\n"

conn = nil
ucppool = nil

begin

  ucppool = MyOracleUcpPool.new("hr", "hr", "jdbc:oracle:thin:@beast.au.oracle.com:1524/linux11gr2", 0, 5, 2)
  print ucppool , "\n"

  #get connection from pool
  conn = ucppool.getConnection()

  #print pool details
  print ucppool.displayPoolDetails()
  puts

  #execute query
  stmt = conn.createStatement()
  rset = stmt.executeQuery("select * from departments")
  while rset.next()
    print "DepartmentId=", rset.getInt(1), 
          ", DepartmentName=" + rset.getString(2), "\n"
  end

  rset.close()
  stmt.close()

  #return connection
  ucppool.returnConnection(conn)
  print "\nConnection returned to pool\n"

  #print pool details
  print ucppool.displayPoolDetails()

rescue 
  print "\n** Error occured **\n"
 print "Failed executing UCP Pool demo from JRuby ", $!, "\n"
  if (!conn.nil?)
    if (!ucppool.nil?)
      ucppool.returnConnection(conn)  
      print "\nConnection returned to pool\n"
    end
  end
  
end

print "\nEnded at ", Time.now , "\n"

4. Your file system would look as follows

C:\jdev\scripting\demos\jruby\jdbc\ucp>dir
 Volume in drive C is OS
 Volume Serial Number is 7C37-0C64

 Directory of C:\jdev\scripting\demos\jruby\jdbc\ucp

06/01/2011  10:21 PM    <DIR>          .
06/01/2011  10:21 PM    <DIR>          ..
06/01/2011  10:30 PM    <DIR>          lib
07/01/2011  08:47 AM             1,112 testucp.rb
               1 File(s)          1,112 bytes
               3 Dir(s)  248,428,380,160 bytes free

5. Edit the following line of code to ensure you provide the correct connect details for your database.

ucppool = MyOracleUcpPool.new("hr", "hr", "jdbc:oracle:thin:@beast.au.oracle.com:1524/linux11gr2", 0, 5, 2)

6. Run the test client using jruby testucp.rb. In this example we do the following.

• create a UCP Pool
• obtain a JDBC Connection from the Pool
• Query a table
• Return the JDBC connection to the pool

OUTPUT

Run at Fri Jan 07 09:25:16 +1100 2011
MyOracleUcpPool [user=hr, passwd=hr, url=jdbc:oracle:thin:@beast.au.oracle.com:1524/linux11gr2, minsize=0, maxsize=5, initialsize=2]


** UCP Pool Details **
NumberOfAvailableConnections: 1
BorrowedConnectionsCount: 1


DepartmentId=10, DepartmentName=Administration
DepartmentId=20, DepartmentName=Marketing
DepartmentId=30, DepartmentName=Purchasing
DepartmentId=40, DepartmentName=Human Resources
DepartmentId=50, DepartmentName=Shipping
DepartmentId=60, DepartmentName=IT
DepartmentId=70, DepartmentName=Public Relations
DepartmentId=80, DepartmentName=Sales
DepartmentId=90, DepartmentName=Executive
DepartmentId=100, DepartmentName=Finance
DepartmentId=110, DepartmentName=Accounting
DepartmentId=120, DepartmentName=Treasury
DepartmentId=130, DepartmentName=Corporate Tax
DepartmentId=140, DepartmentName=Control And Credit
DepartmentId=150, DepartmentName=Shareholder Services
DepartmentId=160, DepartmentName=Benefits
DepartmentId=170, DepartmentName=Manufacturing
DepartmentId=180, DepartmentName=Construction
DepartmentId=190, DepartmentName=Contracting
DepartmentId=200, DepartmentName=Operations
DepartmentId=210, DepartmentName=IT Support
DepartmentId=220, DepartmentName=NOC
DepartmentId=230, DepartmentName=IT Helpdesk
DepartmentId=240, DepartmentName=Government Sales
DepartmentId=250, DepartmentName=Retail Sales
DepartmentId=260, DepartmentName=Recruiting
DepartmentId=270, DepartmentName=Payroll


Connection returned to pool


** UCP Pool Details **
NumberOfAvailableConnections: 2
BorrowedConnectionsCount: 0


Ended at Fri Jan 07 09:25:16 +1100 2011


More Information

Oracle Universal Connection Pool for JDBC Developer's Guide
11g Release 2 (11.2)
http://download.oracle.com/docs/cd/E11882_01/java.112/e12265/toc.htm