Third Party DataSources

NuoDB’s own DataSource offers connection pooling but several other third-party data sources (such as Hikari, DBCP2, and C3P0) can be used instead. All of these libraries can be used stand-alone, with Spring or with Spring Boot.

NuoDB neither endorses nor recommends any of the data source libraries discussed here.

For each DataSource we recommend:

  • Setting a timeout so that connections will automatically be closed and replaced periodically.

  • Enabling testOnBorrow so that each connection is validated as it is borrowed from the pool to be used.

In each example on this page:

  • Pool size is set from 10 to 20 connections.

  • Connections will expire after 10 mins (600 secs).

  • The application will wait 5 secs for a connection or give up and throw an exception.

These values are examples not recommendations.

Hikari Configuration

Hikari is a widely used third-party DataSource which you can use with NuoDB if you prefer. It provides many similar properties to NuoDB’s DataSource.

To create a data source:

HikariConfig config = new HikariConfig("/path/to/hikari.properties");
HikariDataSource ds = new HikariDataSource(config);

We recommend setting the following properties (driverClassName, jdbcUrl, username and password are mandatory):

Hikari Property Description Recommended Value

driverClassName

Use NuoDB’s Driver class.

com.nuodb.jdbc.Driver

jdbcUrl

Connection URL for database

jdbc:com.nuodb://<ap-host(s)>/<dbname>

username

Database user to connect as.

Application specific

password

Password for database user.

Application specific

maxLifetime

Enables connection aging equivalent to maxAge. Both Hikari and NuoDB strongly recommend setting this property. Time in milliseconds, defaults to 1800000 (30 mins).

Application specific

connectionTimeout

How long to wait for a connection from the pool, equivalent to maxWait. Time in milliseconds, defaults to 30000 (30 secs).

Application specific

idleTimeout

Maximum time a connection will sit idle in the pool before it is closed. Idle connections are only closed if there are more than minimumIdle connections in the pool. Unlike NuoDB, connections in use but not running SQL are not considered idle. Time in microseconds, defaults to 600000 (10 mins)

Application specific

Here is an example of a properties file for Hikari configuration. The values shown are not recommendations. Actual values will depend on your application.

driverClassName=com.nuodb.jdbc.Driver
jdbcUrl=jdbc:com.nuodb://localhost/test?schema=HOCKEY
username=dba
password=dba
maxLifetime=600000
connectionTimeout=5000
minimumIdle=10
maximumPoolSize=20
Hikari defaults to a fixed size pool of 10 connections (minimumIdle and maximumPoolSize both default to 10). It automatically runs isValid() against each connection when borrowing it from the pool to give to the application.
You can safely ignore if Hikari outputs an error like Driver does not support get/set network timeout for connections. (setNetworkTimeout is not supported).

For other configuration properties, see Hikari Configuration.

To use Hikari with Spring Boot, see below.

Apache Commons DBCP2

Apache’s Database Connection Pool (make sure to use version 2) consolidated several connection pools from various Apache projects into a single project.

To create a data source:

Properties props = new Properties();
props.load(new FileReader("/path/to/dbcp2.properties"));
BasicDataSource datasource = BasicDataSourceFactory.createDataSource(props);

We recommend setting the following properties (driverClassName, url, username and password are mandatory):

DBCP Property Description Recommended Value

driverClassName

Use NuoDB’s Driver class.

com.nuodb.jdbc.Driver

url

Connection URL for database

jdbc:com.nuodb://<ap-host(s)>/<dbname>

username

Database user to connect as.

Application specific

password

Password for database user.

Application specific

maxConnLifetimeMillis

Enables connection aging equivalent to maxAge. NuoDB strongly recommend setting this property. Time in milliseconds, defaults to -1 meaning never expire.

Application specific

maxWaitMillis

How long to wait for a connection from the pool, equivalent to maxWait. Time in milliseconds, defaults to -1 meaning never stop waiting.

Application specific

minEvictableIdleTimeMillis

Maximum time a connection will sit idle in the pool before it is closed. Time in microseconds, defaults to 1800000 (30 mins).

Application specific

testOnBorrow

Should each connection be validated as it is taken from the pool - invokes isValid() to perform the check. Defaults to false.

true

Here is an example of a properties file for DBCP2 configuration: The values shown are not recommendations. Actual values will depend on your application.

driverClassName=com.nuodb.jdbc.Driver
url=jdbc:com.nuodb://localhost/test?schema=HOCKEY
username=dba
password=dba
maxConnLifetimeMillis=600000
maxWaitMillis=5000
initialSize=10
maxTotal=20
testOnBorrow=true
DBCP defaults to an initially empty pool that can grow up to 8 connections (initialSize=0, maxTotal=8, maxIdle=8, minIdle=0). By default, it does not validate each connection when borrowing it from the pool to give to the application.

For other configuration properties, see https://commons.apache.org/proper/commons-dbcp/configuration.html.

To use DBCP2 with Spring Boot, see below.

c3p0 Configuration

c3p0 is another pooled DataSource library and is the only one integrated into Hibernate (see below).

c3p0 supports several configuration options. Perhaps the simplest is to to create the default c3p0.properties configuration file at the root of your classpath.

Simply creating a data source instance will look for and use the properties file:

ComboPooledDataSource datasource = new ComboPooledDataSource();

The property names are as defined by c3p0 (note the c3p0 prefix). The values shown are not recommendations. Actual values will depend on your application.

c3p0.driverClass=com.nuodb.jdbc.Driver
c3p0.jdbcUrl=jdbc:com.nuodb://localhost/test?schema=HOCKEY
c3p0.user=dba
c3p0.password=goalie
c3p0.maxConnectionAge=600
c3p0.checkoutTimeout=5000
c3p0.minPoolSize=10
c3p0.maxPoolSize=20
c3p0.testConnectionOnCheckout=true
c3p0.preferredTestQuery=SELECT 1 /* Name of Application */ FROM DUAL
If using Spring Boot, the c3p0 driverClass. jdbcUrl, user and password properties in c3p0.properties are ignored. You must specify the equivalent spring.datasource properties (see below).

We recommend setting the following properties (driverClass, jdbcUrl, user and password are mandatory):

c3p0 Property Description Recommended Value

driverClass

Use NuoDB’s Driver class.

com.nuodb.jdbc.Driver

jdbcUrl

Connection URL for database

jdbc:com.nuodb://<ap-host(s)>/<dbname>

user

Database user to connect as.

Application specific

password

Password for database user.

Application specific

maxConnectionAge

Enables connection aging equivalent to maxAge. NuoDB strongly recommend setting this property. Time in seconds (not milliseconds), defaults to 0 meaning never timeout.

Application specific

checkoutTimeout

How long to wait for a connection from the pool, equivalent to maxWait. Time in milliseconds, defaults to 0 meaning never stop waiting.

Application specific

testConnectionOnCheckout

Should each connection be validated as it is taken from the pool - run a query to perform the check. Defaults to false.

true

preferredTestQuery

Validation query used to check a connection when testConnectionOnCheckout is enabled. Note the comment in the recommended query - this allows the query to be identified when viewing logs and System tables.

SELECT 1 /* Name of Application */ FROM DUAL

Idle timeout for c3p0 involves two properties: maxIdleTime and maxIdleTimeExcessConnections. For details, see Managing Pool Size.

c3p0 defaults to an initial pool of 3 connections growing to a maximum of 15 (initialPoolSize=3, minPoolSize=3, maxPoolSize=15). By default, it does not validate each connection when borrowing it from the pool to give to the application.

For other configuration properties, see https://www.mchange.com/projects/c3p0/#configuration_properties.

To use c3p0 with Spring Boot, see below.

c3p0 and Hibernate

To use with Hibernate, set the same recommended properties either in the hibernate.cfg.xml file:

<property name="hibernate.c3p0.max_connection_age">600</property>
<property name="hibernate.c3p0.checkout_timeout">5000</property>

or the hibernate.properties file:

hibernate.c3p0.max_connection_age=600
hibernate.c3p0.checkout_timeout=5000
Property names are specified using underscores (such as max_connection_age instead of maxConnectionAge). These values are examples not recommendations.

For more on using Hibernate to configure c3p0, see here.

Using Spring Boot

If configuring your application using Spring Boot, it uses Hikari by default but both DBCP2 and C3P0 are supported. Note the following:

  • The spring-boot-starter-jdbc JAR must be on the classpath.

  • Spring Boot offers dedicated properties (in application.properties) for Hikari and DBCP2 but not for c3p0.

    • Properties are of the form spring.datasource.hikari.xxx and spring.datasource.dbcp2.xxx respectively.

  • Spring Boot uses the default configuration file, c3p0.properties to configure c3p0, provided it exists at the root of the classpath.

  • Regardless of the pool, the database URL, driver class, user name and password must be specified using spring.datasource.xxx properties in application.properties (see examples below).

  • Properties are specified using snake-case rather than camel-case (such as max-size instead of maxSize).

Alternatively, you can configure Spring Boot to use a NuoDB DataSource.

To explicitly specify the pool you want, use spring.datasource.type.

Hikari Using Spring Boot

This is the Spring Boot default, but you can specify spring.datasource.type=com.zaxxer.hikari.HikariDataSource if you wish. All that is required are any additional Hikari properties. Here is an example application.properties file specifying the properties recommended above.

# NuoDB's driver class
spring.datasource.driver-class-name=com.nuodb.jdbc.Driver

# Database URL
spring.datasource.url=jdbc:com.nuodb://localhost/test?schema=HOCKEY

# Username and password
spring.datasource.username=dba
spring.datasource.password=goalie

# Hikari specific properties
spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.max-lifetime=600000
spring.datasource.hikari.connection-timeout=5000

For a full list of Spring Boot’s Hikari properties, see Spring Boot’s Common Application Properties page.

DBCP2 Using Spring Boot

This must be requested explicitly and the DBCP2 JAR must be on the classpath. Here is an example application.properties file specifying the properties recommended above.

# Request DBCP2
spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource

# NuoDB's driver class
spring.datasource.driver-class-name=com.nuodb.jdbc.Driver

# Database URL
spring.datasource.url=jdbc:com.nuodb://localhost/test?schema=HOCKEY

# Username and password
spring.datasource.username=dba
spring.datasource.password=goalie

# DBCP specific properties
spring.datasource.dbcp2.initial-size=10
spring.datasource.dbcp2.max-total=20
spring.datasource.dbcp2.max-conn-lifetime-millis=600000
spring.datasource.dbcp2.max-wait-millis=5000

For a full list of Spring Boot’s DBCP2 properties, see Spring Boot’s Common Application Properties page.

c3p0 Using Spring Boot

There are no Spring Boot properties specifically for c3p0, but you must still specify spring.datasource.driver-class-name, spring.datasource.url, spring.datasource.username, and spring.datasource.password. Request c3p0 using spring.datasource.type and ensure the c3p0 JAR is on the classpath.

# Request c3p0
spring.datasource.type=com.mchange.v2.c3p0.ComboPooledDataSource

# NuoDB's driver class
spring.datasource.driver-class-name=com.nuodb.jdbc.Driver

# Database URL
spring.datasource.url=jdbc:com.nuodb://localhost/test?schema=HOCKEY

# Username and password
spring.datasource.username=dba
spring.datasource.password=goalie

All other configuration is provided as above in the c3p0.properties file.

Any values for c3p0.driverClass, c3p0.jdbcUrl, c3p0.user, and c3p0.password in that file are ignored.