Third-Party Data Sources

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.

Best Practices

For each DataSource, we recommend the following:

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.
Set maximum time for a connection to live so that APs can periodically rebalance connections and also create connections to new TEs (typically after scaling out). The properties are Hikari: maxLifetime, DBCP2: maxConnLifetimeMillis or C3P0: maxConnectionAge.

The following assumptions apply to all the examples on this page.

Pool size is set from 10 to 20 connections.
Connections will expire after 10 mins (600 secs).
The application waits up to 5 seconds to obtain a connection before throwing an exception.

Hikari Configuration

Hikari is a widely used third-party DataSource which can be used with NuoDB. It provides configuration properties similar to NuoDB DataSource.

Create a properties file

Create a properties file that includes the following recommended properties:

Hikari Property Description Recommended Value Mandatory

Hikari Property	Description	Recommended Value	Mandatory
`driverClassName`	Use NuoDB’s Driver class.	`com.nuodb.jdbc.Driver`	Yes
`jdbcUrl`	Connection URL for database	`jdbc:com.nuodb://<ap-host(s)>/<dbname>`	Yes
`username`	Database user to connect as.	Application specific	Yes
`password`	Password for database user.	Application specific	Yes
`maxLifetime`	Enables connection aging equivalent to `maxAge`. Time in milliseconds, defaults to `1800000` (30 minutes). For more information, see Working with Timeouts Both Hikari and NuoDB strongly recommend setting this property.	Application specific	No
`connectionTimeout`	Maximum time an application will wait for a connection from the pool, equivalent to `maxWait`. Time in milliseconds, defaults to `30000` (30 secs).	Application specific	No
`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	No

driverClassName

Use NuoDB’s Driver class.

com.nuodb.jdbc.Driver

Yes

jdbcUrl

Connection URL for database

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

Yes

username

Database user to connect as.

Application specific

Yes

password

Password for database user.

Application specific

Yes

maxLifetime

Enables connection aging equivalent to maxAge.

Time in milliseconds, defaults to 1800000 (30 minutes). For more information, see Working with Timeouts Both Hikari and NuoDB strongly recommend setting this property.

Application specific

connectionTimeout

Maximum time an application will 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

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.
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.

Example hikari.properties file

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

Load the properties file and create a DataSource

To load the file and create a Hikari DataSource, include the following in the Java source file:

Example java file

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

Apache Commons DBCP2 Configuration

Create a properties file

Create a properties file that includes the following recommended properties:

DBCP Property Description Recommended Value Mandatory

DBCP Property	Description	Recommended Value	Mandatory
`driverClassName`	Use NuoDB’s Driver class.	`com.nuodb.jdbc.Driver`	Yes
`url`	Connection URL for database	`jdbc:com.nuodb://<ap-host(s)>/<dbname>`	Yes
`username`	Database user to connect as.	Application specific	Yes
`password`	Password for database user.	Application specific	Yes
`maxConnLifetimeMillis`	Enables connection aging equivalent to `maxAge`. NuoDB strongly recommend setting this property. Time in milliseconds, defaults to `-1` meaning never expire.	xref:/client-development/nuodb-java-jdbc-driver/notes-for-using-java-jdbc-driver/working-with-timeouts.adoc#determine-connection-aging-value[Application specific	No
`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	No
`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	No
`testOnBorrow`	Should each connection be validated as it is taken from the pool - invokes `isValid()` to perform the check. Defaults to `false`.	`true`	No

driverClassName

Use NuoDB’s Driver class.

com.nuodb.jdbc.Driver

Yes

url

Connection URL for database

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

Yes

username

Database user to connect as.

Application specific

Yes

password

Password for database user.

Application specific

Yes

maxConnLifetimeMillis

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

xref:/client-development/nuodb-java-jdbc-driver/notes-for-using-java-jdbc-driver/working-with-timeouts.adoc#determine-connection-aging-value[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

Example dbcp2.properties file

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

For other configuration properties, see BasicDataSource Configuration Parameter. To use DBCP2 with Spring Boot, see DBCP2 Using Spring Boot.

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 before providing it to the application.

Load the properties file and create a data source

To load the file and create a Apache Commons DBCP2 DataSource, include the following in the Java source file:

Example java file

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

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 create the default c3p0.properties configuration file at the root of your classpath.

Create a properties file

Create a properties file called c3p0.properties at the root of your classpath. Include the following recommended properties in the file:

c3p0 Property Description Recommended Value Mandatory

c3p0 Property	Description	Recommended Value	Mandatory
`driverClass`	Use NuoDB’s Driver class.	`com.nuodb.jdbc.Driver`	Yes
`jdbcUrl`	Connection URL for database	`jdbc:com.nuodb://<ap-host(s)>/<dbname>`	Yes
`user`	Database user to connect as.	Application specific	Yes
`password`	Password for database user.	Application specific	Yes
`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	No
`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	No
`testConnectionOnCheckout`	Should each connection be validated as it is taken from the pool - run a query to perform the check. Defaults to `false`.	`true`	No
`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`	No

driverClass

Use NuoDB’s Driver class.

com.nuodb.jdbc.Driver

Yes

jdbcUrl

Connection URL for database

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

Yes

user

Database user to connect as.

Application specific

Yes

password

Password for database user.

Application specific

Yes

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

Example c3p0.properties file

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 driverClass, jdbcUrl, user, and password properties in c3p0.properties are ignored. Specify the equivalent spring.datasource properties (see below).

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 c3p0 Configuration Properties. To use c3p0 with Spring Boot, see c3p0 Using Spring Boot.

Create a data source

Creating a c3p0 DataSource instance will automatically load the properties file, if it exists at the root of the classpath. To create a c3p0 data source, include the following in the Java source file:

Example java file

ComboPooledDataSource datasource = new ComboPooledDataSource();

c3p0 and Hibernate

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

Examples hibernate.cfg.xml file

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

Example hibernate.properties file

hibernate.c3p0.max_connection_age=600
hibernate.c3p0.checkout_timeout=5000

For more on using Hibernate to configure c3p0, see Using c3p0 with Hibernate.

Spring Boot Configuration

When configuring your application using Spring Boot, HikariCP is used as the default connection pool. Both DBCP2 and c3p0 are also supported.

Note the following:

The spring-boot-starter-jdbc JAR must be on the classpath.
The dedicated configuration properties for HikariCP and DBCP2 are specified in application.properties. The properties use the prefix spring.datasource.hikari.* for HikariCP and spring.datasource.dbcp2.* for DBCP2.
The default configuration file to configure c3p0 is c3p0.properties if it is present at the root of the classpath.
To explicitly specify the connection pool, use spring.datasource.type.
Regardless of the connection pool, the following connection properties must be specified using spring.datasource.xxx in application.properties:
- URL
- driver class
- username
- password
Properties are specified using kebab-case. For example, max-size.

Alternatively, Spring Boot can be configured to use a NuoDB DataSource.

Hikari Using Spring Boot

Hikari is the default connection pool when using Spring Boot. It can also be specified using spring.datasource.type=com.zaxxer.hikari.HikariDataSource.

Specify the additional properties in the application.properties file. For more properties, see Hikari Configuration.

Example application.properties file

# 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

DBCP2 must be requested explicitly and the DBCP2 JAR must be on the classpath. For more properties, see Apache Commons DBCP2 Configuration.

Example application.properties file

# 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

Request c3p0 using spring.datasource.type and ensure the c3p0 JAR is on the classpath. 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
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

For more properties, see c3p0 Configuration in the c3p0.properties file.

Values for c3p0.driverClass, c3p0.jdbcUrl, c3p0.user, and c3p0.password in the properties file are ignored.