Thursday, 12 January 2012

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=""

  <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" />

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

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;

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>();
   // ensure pool is started by getting a connection object
   conn = pds.getConnection();;
   conn.close();"Getting 5 connections from pool");
   for (int i = 0; i < 5; i++)
   };"Closing all connections from pool");
   for (Connection connection: connections)
  catch (SQLException e) 
   // TODO Auto-generated catch block
   connections = null;

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



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 prepareRefresh
INFO: Refreshing 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 preInstantiateSingletons
INFO: Pre-instantiating singletons in defining beans [sqlfireUcpDataSource]; root of factory hierarchy
Jan 12, 2012 10:11:55 PM run
INFO: $Proxy0@1dc18a4c
Jan 12, 2012 10:11:55 PM run
INFO: Getting 5 connections from pool
Jan 12, 2012 10:11:55 PM run
INFO: ** Pool Details **
NumberOfAvailableConnections: 0
BorrowedConnectionsCount: 5

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

No comments: