JavaXT Connection Pool

Establishing a connection to a database can be very expensive and time consuming. An application can easily spend several seconds every time it needs to establish a connection to perform a database transaction that might take milliseconds. JDBC Connection Pools can alleviate this overhead by sharing a "pool" of open connections to the database. Applications can simply peal off an open connection from the pool and return it back when it is done. This approach significantly improves the speed and scalability of your database-driven applications.

The javaxt.sql package includes a lightweight, standalone Java JDBC connection pool manager. The javaxt.sql.ConnectionPool class is used to create a connection pool and can be used with the javaxt.sql.Connection and javaxt.sql.Recordset classes.

Typical Usage

The javaxt.sql.ConnectionPool can be instantiated directly from the javaxt.sql.Database class like this:

  //Set general connection info to the database server
    javaxt.sql.Database database = new javaxt.sql.Database();
    database.setDriver(javaxt.sql.Driver.PostgreSQL);
    database.setHost("localhost:5432");
    database.setUserName("postgres");
    database.setPassword("password");
    database.setName("test");

  //Set connection pool size
    database.setConnectionPoolSize(100);

  //Initialize the connection pool
    database.initConnectionPool();

Once the connection pool is initalized you can get a connection from the pool directly from the javaxt.sql.Database class like this:

try (javaxt.sql.Connection conn = database.getConnection()){

   //Do something!

}
catch(java.sql.SQLException e){
}

Note that the getRecord(), getRecords(), and getRecordset() methods in the javaxt.sql.Database class will use connections from the connection pool once initialized.

Specifying a javax.sql.ConnectionPoolDataSource

The javaxt.sql.ConnectionPool contains a list of known/predefined javax.sql.ConnectionPoolDataSource for various databases. However, in some cases, you may want to define your own data source.

Defining your own ConnectionPoolDataSource is quite simple. In this example, we will create a SQLite connection pool using the SQLiteConnectionPoolDataSource. Once a connection pool is created we will grab a connection from the pool and execute a simple query.

//Instantiate an implementation of a javax.sql.ConnectionPoolDataSource.
  SQLiteConnectionPoolDataSource dataSource = new SQLiteConnectionPoolDataSource();
  dataSource.setUrl("jdbc:sqlite:c:/temp/test.db");

//Create a jdbc connection pool
  javaxt.sql.ConnectionPool connectionPool = new javaxt.sql.ConnectionPool(dataSource, maxConnections);

...


//Remove a jdbc connection from the connection pool and pass it to a javaxt connection
  javaxt.sql.Connection conn = connectionPool.getConnection();

//Do something with the connection. In this case, we will open a Recordset and execute a query
  javaxt.sql.Recordset rs = conn.getRecordset("SELECT * FROM EMPLOYEE");
  rs.close(); 
 
//Close connection so it can be released back to the pool!
  conn.close();

...


//Eventually, when you're ready to close down your app, be sure to close the connection pool
  connectionPool.close();

Other Examples

Microsoft SQL Server

Here's an example of how to create a JDBC connection for Microsoft SQL Server. Note that the sqljdbc 1.1 documentation, chapter "Using Connection Pooling", recommends to use SQLServerXADataSource instead of SQLServerConnectionPoolDataSource.

com.microsoft.sqlserver.jdbc.SQLServerXADataSource dataSource = new com.microsoft.sqlserver.jdbc.SQLServerXADataSource();
dataSource.setDatabaseName ("Northwind");
dataSource.setServerName ("localhost");
dataSource.setUser ("sa");
dataSource.setPassword ("sesame");
javaxt.sql.ConnectionPool connectionPool = new javaxt.sql.ConnectionPool(dataSource, maxConnections);
...
javaxt.sql.Connection conn = connectionPool.getConnection();
conn.close();
...
connectionPool.close();

Oracle

Here's an example of how to create a connection pool using Oracle's Thin driver:

oracle.jdbc.pool.OracleConnectionPoolDataSource dataSource = new oracle.jdbc.pool.OracleConnectionPoolDataSource();
dataSource.setDriverType ("thin");
dataSource.setServerName ("server1.yourdomain.com");
dataSource.setPortNumber (1521);
dataSource.setServiceName ("db1.yourdomain.com");
dataSource.setUser ("system");
dataSource.setPassword ("sesame");
javaxt.sql.ConnectionPool connectionPool = new javaxt.sql.ConnectionPool(dataSource, maxConnections);
...
javaxt.sql.Connection conn = connectionPool.getConnection();  
conn.close();
...
connectionPool.close();

H2 (embedded mode)

org.h2.jdbcx.JdbcDataSource dataSource = new org.h2.jdbcx.JdbcDataSource();
dataSource.setURL ("jdbc:h2:file:c:/temp/testDB");
javaxt.sql.ConnectionPool connectionPool = new javaxt.sql.ConnectionPool(dataSource, maxConnections);
...
javaxt.sql.Connection conn = connectionPool.getConnection();  
conn.close();
...
connectionPool.close();

Apache Derby (embedded mode)

org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource dataSource = new org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource();
dataSource.setDatabaseName ("c:/temp/testDB");
dataSource.setCreateDatabase ("create");
javaxt.sql.ConnectionPool connectionPool = new javaxt.sql.ConnectionPool(dataSource, maxConnections);
...
javaxt.sql.Connection conn = connectionPool.getConnection();  
conn.close();
...
connectionPool.close();

JTDS

net.sourceforge.jtds.jdbcx.JtdsDataSource dataSource = new net.sourceforge.jtds.jdbcx.JtdsDataSource();
dataSource.setDatabaseName ("Northwind");
dataSource.setServerName ("localhost");
dataSource.setUser ("sa");
dataSource.setPassword ("sesame");
javaxt.sql.ConnectionPool connectionPool = new javaxt.sql.ConnectionPool(dataSource, maxConnections);
...
javaxt.sql.Connection conn = connectionPool.getConnection();  
conn.close();
...
connectionPool.close();

MaxDB

com.sap.dbtech.jdbcext.ConnectionPoolDataSourceSapDB dataSource = new com.sap.dbtech.jdbcext.ConnectionPoolDataSourceSapDB();
dataSource.setDatabaseName ("dbname");
dataSource.setServerName ("dbhost");
dataSource.setUser ("user");
dataSource.setPassword ("password");
javaxt.sql.ConnectionPool connectionPool = new javaxt.sql.ConnectionPool(dataSource, maxConnections);
...
javaxt.sql.Connection conn = connectionPool.getConnection();  
conn.close();
...
connectionPool.close();

Using Other Connection Pool Libraries

It's possible to expose 3rd party connection pool libraries as a javaxt.sql.ConnectionPool by extending the javaxt.sql.ConnectionPool class and invoking the initConnectionPool() with your implementation like this:

  //Set general connection info to the database server
    javaxt.sql.Database database = new javaxt.sql.Database();
    database.setDriver(javaxt.sql.Driver.PostgreSQL);
    database.setHost("localhost:5432");
    database.setUserName("postgres");
    database.setPassword("password");
    database.setName("test");

  //Set connection pool size
    database.setConnectionPoolSize(100);

  //Instantiate a custom ConnectionPool
    test.hikari.ConnectionPool pool = new test.hikari.ConnectionPool(database);

  //Initialize the connection pool
    database.initConnectionPool(pool);

The implementation for the test.hikari.ConnectionPool is below. In this example, we will expose Hikari as a javaxt.sql.ConnectionPool.

package test.hikari;

import java.sql.SQLException;
import javaxt.sql.Connection;
import javaxt.sql.Database;

import com.zaxxer.hikari.HikariDataSource;


//******************************************************************************
//**  ConnectionPool
//******************************************************************************
/**
 *   JDBC connection pool using Hikari
 *
 ******************************************************************************/

public class ConnectionPool extends javaxt.sql.ConnectionPool {

    private static HikariDataSource ds;


  //**************************************************************************
  //** Constructor
  //**************************************************************************
    public ConnectionPool(Database database) throws SQLException {
        this(database, database.getConnectionPoolSize());
    }


  //**************************************************************************
  //** Constructor
  //**************************************************************************
    public ConnectionPool(Database database, int maxConnections) throws SQLException {
        super(new FakeCP(), maxConnections);

        String connectionString = database.getConnectionString();
        int idx = connectionString.indexOf(";user=");
        if (idx>-1) connectionString = connectionString.substring(0, idx);
        idx = connectionString.indexOf(";password=");
        if (idx>-1) connectionString = connectionString.substring(0, idx);
        //System.out.println(connectionString);


        ds = new HikariDataSource();
        ds.setPoolName(database.getName());
        ds.setDriverClassName(database.getDriver().getClassName());
        ds.setJdbcUrl(connectionString);
        ds.setUsername(database.getUserName());
        ds.setPassword(database.getPassword());
        ds.setMaximumPoolSize(maxConnections);
        ds.setConnectionTimeout(60000);
    }


  //**************************************************************************
  //** getConnection
  //**************************************************************************
  /** Returns a connection from the connection pool. If maxConnections
   *  connections are already in use, the method waits until a connection
   *  becomes available or timeout seconds elapsed. When the application is
   *  finished using the connection, it must close it in order to return it to
   *  the pool.
   *
   * @return A Connection object.
   * @throws SQLException when no connection becomes available within timeout seconds.
   */
    public Connection getConnection() throws SQLException {
        return new Connection(ds.getConnection());
    }


  //**************************************************************************
  //** close
  //**************************************************************************
    public synchronized void close() throws SQLException {
        ds.close();
    }


  //**************************************************************************
  //** FakeCP Class
  //**************************************************************************
  /** Used to instantiate a javaxt.sql.ConnectionPool
   */
    private static class FakeCP implements javax.sql.ConnectionPoolDataSource {
        public javax.sql.PooledConnection getPooledConnection()
            throws SQLException{ return null; }
        public javax.sql.PooledConnection getPooledConnection(String user, String password)
            throws SQLException{ return null; }
        public java.io.PrintWriter getLogWriter() throws SQLException{ return null; }
        public void setLogWriter(java.io.PrintWriter out) throws SQLException{}
        public void setLoginTimeout(int seconds) throws SQLException{}
        public int getLoginTimeout() throws SQLException{ return 0; }
        public java.util.logging.Logger getParentLogger()
            throws java.sql.SQLFeatureNotSupportedException{ return null; }
    }
}

Background

When I started writing javaxt-core, the standard Java library (JDK 1.5) did not provide a connection pool manager for JDBC database connections. There are open source connection pool managers like:

• DBCP
• c3p0
• Proxool
• BoneCP
• Tomcat JDBC Connection Pool

But these are huge complex packages. I needed a lightwieght connection pool manager for my standalone Java applications. I stumbled across Christian d'Heureuse MiniConnectionPoolManager and was hooked. In April 2011, I rolled the MiniConnectionPoolManager into the javaxt.sql package and renamed it ConnectionPool.java.