RDBMS : Connection Pools

When you create a PersistenceManagerFactory you define the connection URL, driver name, and the username/password to use. This works perfectly well but does not "pool" the connections so that they are efficiently opened/closed when needed to utilise datastore resources in an optimum way. DataNucleus allows you to utilise a connection pool to efficiently manage the connections to the datastore. We currently provide support for the following



You need to specify the persistence property datanucleus.connectionPoolingType to be whichever of the external pooling libraries you wish to use (or "None" if you explicitly want no pooling). If you are using JDK1.6+ and want to use the builtin DBCP, you can set this to "dbcp-builtin" (or leave it to work it out). DataNucleus provides two sets of connections to the datastore - one for transactional usage, and one for non-transactional usage. If you want to define a different pooling for nontransactional usage then you can also specify the persistence property datanucleus.connectionPoolingType.nontx to whichever is required.

If you have a JDBC driver that supports custom properties, you can still use DataNucleus connection pooling and you need to specify the properties in with your normal persistence properties, but add the prefix datanucleus.connectionPool.driver. to the property name that the driver requires. For example if an Oracle JDBC driver accepts defaultRowPrefetch then you would specify something like

datanucleus.connectionPool.driver.defaultRowPrefetch=50

and it will pass in defaultRowPrefetch as "50" into the driver.



Apache DBCP

DataNucleus allows you to utilise a connection pool using Apache DBCP to efficiently manage the connections to the datastore. DBCP is a third-party library providing connection pooling. This is accessed by specifying the persistence property datanucleus.connectionPoolingType . To utilise DBCP-based connection pooling we do this

// Specify our persistence properties used for creating our PMF/EMF
Properties props = new Properties();
properties.setProperty("datanucleus.ConnectionDriverName","com.mysql.jdbc.Driver");
properties.setProperty("datanucleus.ConnectionURL","jdbc:mysql://localhost/myDB");
properties.setProperty("datanucleus.ConnectionUserName","login");
properties.setProperty("datanucleus.ConnectionPassword","password");
properties.setProperty("datanucleus.connectionPoolingType", "DBCP");

So the PMF / EMF will use connection pooling using DBCP. To do this you will need commons-dbcp , commons-pool and commons-collections JARs to be in the CLASSPATH.

You can also specify persistence properties to control the actual pooling. The currently supported properties for DBCP are shown below

# Pooling of Connections
datanucleus.connectionPool.maxIdle=10
datanucleus.connectionPool.minIdle=3
datanucleus.connectionPool.maxActive=5
datanucleus.connectionPool.maxWait=60

# Pooling of PreparedStatements
datanucleus.connectionPool.maxStatements=20

datanucleus.connectionPool.testSQL=SELECT 1

datanucleus.connectionPool.timeBetweenEvictionRunsMillis=2400000
datanucleus.connectionPool.minEvictableIdleTimeMillis=18000000


C3P0

DataNucleus allows you to utilise a connection pool using C3P0 to efficiently manage the connections to the datastore. C3P0 is a third-party library providing connection pooling. This is accessed by specifying the persistence property datanucleus.connectionPoolingType . To utilise C3P0-based connection pooling we do this

// Specify our persistence properties used for creating our PMF/EMF
Properties props = new Properties();
properties.setProperty("datanucleus.ConnectionDriverName","com.mysql.jdbc.Driver");
properties.setProperty("datanucleus.ConnectionURL","jdbc:mysql://localhost/myDB");
properties.setProperty("datanucleus.ConnectionUserName","login");
properties.setProperty("datanucleus.ConnectionPassword","password");
properties.setProperty("datanucleus.connectionPoolingType", "C3P0");

So the PMF / EMF will use connection pooling using C3P0. To do this you will need the C3P0 JAR to be in the CLASSPATH. If you want to configure C3P0 further you can include a "c3p0.properties" in your CLASSPATH - see the C3P0 documentation for details.

You can also specify persistence properties to control the actual pooling. The currently supported properties for C3P0 are shown below

# Pooling of Connections
datanucleus.connectionPool.maxPoolSize=5
datanucleus.connectionPool.minPoolSize=3
datanucleus.connectionPool.initialPoolSize=3

# Pooling of PreparedStatements
datanucleus.connectionPool.maxStatements=20


Proxool

DataNucleus allows you to utilise a connection pool using Proxool to efficiently manage the connections to the datastore. Proxool is a third-party library providing connection pooling. This is accessed by specifying the persistence property datanucleus.connectionPoolingType . To utilise Proxool-based connection pooling we do this

// Specify our persistence properties used for creating our PMF/EMF
Properties props = new Properties();
properties.setProperty("datanucleus.ConnectionDriverName","com.mysql.jdbc.Driver");
properties.setProperty("datanucleus.ConnectionURL","jdbc:mysql://localhost/myDB");
properties.setProperty("datanucleus.ConnectionUserName","login");
properties.setProperty("datanucleus.ConnectionPassword","password");
properties.setProperty("datanucleus.connectionPoolingType", "Proxool");

So the PMF / EMF will use connection pooling using Proxool. To do this you will need the proxool and commons-logging JARs to be in the CLASSPATH.

You can also specify persistence properties to control the actual pooling. The currently supported properties for Proxool are shown below

datanucleus.connectionPool.maxConnections=10

datanucleus.connectionPool.testSQL=SELECT 1


BoneCP

DataNucleus allows you to utilise a connection pool using BoneCP to efficiently manage the connections to the datastore. BoneCP is a third-party library providing connection pooling. This is accessed by specifying the persistence property datanucleus.connectionPoolingType . To utilise BoneCP-based connection pooling we do this

// Specify our persistence properties used for creating our PMF/EMF
Properties props = new Properties();
properties.setProperty("datanucleus.ConnectionDriverName","com.mysql.jdbc.Driver");
properties.setProperty("datanucleus.ConnectionURL","jdbc:mysql://localhost/myDB");
properties.setProperty("datanucleus.ConnectionUserName","login");
properties.setProperty("datanucleus.ConnectionPassword","password");
properties.setProperty("datanucleus.connectionPoolingType", "BoneCP");

So the PMF / EMF will use connection pooling using BoneCP. To do this you will need the BoneCP JAR (and SLF4J, google-collections) to be in the CLASSPATH.

You can also specify persistence properties to control the actual pooling. The currently supported properties for BoneCP are shown below

# Pooling of Connections
datanucleus.connectionPool.maxPoolSize=5
datanucleus.connectionPool.minPoolSize=3

# Pooling of PreparedStatements
datanucleus.connectionPool.maxStatements=20


Lookup a DataSource using JNDI

DataNucleus allows you to use connection pools (java.sql.DataSource) bound to a javax.naming.InitialContext with a JNDI name. You first need to create the DataSource in the container (application server/web server), and secondly you define the datanucleus.ConnectionFactoryName property with the DataSource JDNI name.

The following example uses a properties file that is loaded before creating the PersistenceManagerFactory. The PersistenceManagerFactory is created using the JDOHelper.

datanucleus.ConnectionFactoryName=YOUR_DATASOURCE_JNDI_NAME
Properties properties = new Properties();

// the properties file is in your classpath
PersistenceManagerFactory pmf = JDOHelper.getPersistenceManagerFactory("/yourpath/yourfile.properties");

Please read more about this in the Data Source Guide.



Manually create a DataSource ConnectionFactory

We could have used the DataNucleus plugin which internally creates a DataSource ConnectionFactory, however we can also do this manually if we so wish. Let's demonstrate how to do this with one of the most used pools Jakarta DBCP

With DBCP you need to generate a javax.sql.DataSource , which you will then pass to DataNucleus. You do this as follows

// Load the JDBC driver
Class.forName(dbDriver);

// Create the actual pool of connections 
ObjectPool connectionPool = new GenericObjectPool(null);

// Create the factory to be used by the pool to create the connections
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(dbURL, dbUser, dbPassword);

// Create a factory for caching the PreparedStatements
KeyedObjectPoolFactory kpf = new StackKeyedObjectPoolFactory(null, 20);

// Wrap the connections with pooled variants
PoolableConnectionFactory pcf = 
    new PoolableConnectionFactory(connectionFactory, connectionPool, kpf, null, false, true);

// Create the datasource
DataSource ds = new PoolingDataSource(connectionPool);

// Create our PMF
Map properties = new HashMap();
properties.put("javax.jdo.option.ConnectionDriverName","com.mysql.jdbc.Driver");
properties.put("javax.jdo.option.ConnectionFactory", ds);
PersistenceManagerFactory pmf = JDOHelper.getPersistenceManagerFactory(properties);

Note that we haven't passed the dbUser and dbPassword to the PMF since we no longer need to specify them - they are defined for the pool so we let it do the work. As you also see, we set the data source for the PMF. Thereafter we can sit back and enjoy the performance benefits. Please refer to the documentation for DBCP for details of its configurability (you will need commons-dbcp , commons-pool , and commons-collections in your CLASSPATH to use this above example).