Using a Connection Pool with SQLFire

A Java application can use the JDBC thin driver to access a single member of a SQLFire cluster and execute SQL statements. This makes it a perfect candidate to use a connection pool to maintain connections for clients rather then connect to it individually. Two that spring to mind are Apache DBCP or Oracle's UCP. In this example we show a configuration using Spring and Oracle UCP which creates a pool of SQLFire connections which clients can then use as required.

1. Create a new spring bean application config file with a UCP defined 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="sqlfireUcpDataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
    <property name="URL" value="jdbc:sqlfire://localhost:1527/" />
    <property name="connectionFactoryClassName" value="com.vmware.sqlfire.jdbc.ClientDriver" />
    <property name="connectionPoolName" value="SQLFIRE_UCP_POOL" />
    <property name="minPoolSize" value="5" />
    <property name="maxPoolSize" value="20" />
    <property name="initialPoolSize" value="5" />
  </bean>
  
</beans>

2. Create a test class as follows to verify the pool as shown below.

package vmware.au.sqlfire.pool;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

import oracle.ucp.jdbc.PoolDataSource;

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

public class TestSQLFireJDBCPool 
{
 private Logger log = Logger.getLogger("com.vmware.sqlfire");
 private ApplicationContext context;
 private static final String BEAN_NAME = "sqlfireUcpDataSource";
 private PoolDataSource pds;
 
 public TestSQLFireJDBCPool() 
 {
  context = new ClassPathXmlApplicationContext("application-config-SQLFIRE.xml");
  pds = (PoolDataSource) context.getBean(BEAN_NAME);  
 }

 public void run()
 {
  Connection conn = null;
  List<Connection> connections = new ArrayList<Connection>();
  
  try 
  {
   // ensure pool is started by getting a connection object
   conn = pds.getConnection();
   log.info(conn.toString());
   conn.close();
   
   log.info("Getting 5 connections from pool");
   
   for (int i = 0; i < 5; i++)
   {
    connections.add(pds.getConnection());
   }
   
   log.info(displayPoolDetails());
   log.info("Closing all connections from pool");
   
   for (Connection connection: connections)
   {
     connection.close(); 
   }
   
   log.info(displayPoolDetails());
   
 
  } 
  catch (SQLException e) 
  {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } 
  finally
  {
   connections = null;
  }
 }

    public String displayPoolDetails () throws SQLException
    {
      StringBuffer sb = new StringBuffer();
      
      sb.append("** Pool Details **\n");
      sb.append("NumberOfAvailableConnections: " +
                         pds.getAvailableConnectionsCount());
      sb.append("\nBorrowedConnectionsCount: " +
                         pds.getBorrowedConnectionsCount());
      sb.append("\n");
      
      return sb.toString();
    }
    
 /**
  * @param args
  */
 public static void main(String[] args) 
 {
  TestSQLFireJDBCPool test = new TestSQLFireJDBCPool();
  test.run();

 }

}

3. You will need to use the following JAR files such as Spring, Oracle UCP etc..

Output as follows:

Jan 12, 2012 10:11:54 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@657d5d2a: startup date [Thu Jan 12 22:11:54 EST 2012]; root of context hierarchy
Jan 12, 2012 10:11:54 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [application-config-SQLFIRE.xml]
Jan 12, 2012 10:11:55 PM org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@62da3a1e: defining beans [sqlfireUcpDataSource]; root of factory hierarchy
Jan 12, 2012 10:11:55 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: $Proxy0@1dc18a4c
Jan 12, 2012 10:11:55 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: Getting 5 connections from pool
Jan 12, 2012 10:11:55 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: ** Pool Details **
NumberOfAvailableConnections: 0
BorrowedConnectionsCount: 5

Jan 12, 2012 10:11:55 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: Closing all connections from pool
Jan 12, 2012 10:11:55 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: ** Pool Details **
NumberOfAvailableConnections: 5
BorrowedConnectionsCount: 0