JavaXT

JavaXT Connection Pool

The javaxt.sql package includes a lightwieght, 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.

Background

The standard Java library (JDK 1.5) does not provide a connection pool manager for JDBC database connections. There are open source connection pool managers like:

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.

Basic Usage

The basic flow 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. See more examples below.
  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 = new javaxt.sql.Recordset();
  rs.open("SELECT * FROM EMPLOYEE", conn);  
  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();