Configuring a NuoDB DataSource

NuoDB includes a DataSource class for providing connections. The advantages of using this class are:

  • It hides many complexities associated with managing NuoDB connections, such as dealing with NuoDB AP redundancy and reconnecting to the NuoDB database if a transaction process terminates (see Validating Connections below).

  • It supports connection pooling (which is enabled by default).

For these reasons the DataSource is preferred over the old DriverManager approach.

Internally, the DataSource class maintains a pool of connections for you and ensures that each thread in a multi-threaded application gets its own connection. This is important due to the distributed nature of the NuoDB architecture.

The DataSource API is documented at Java JDBC Driver API Reference.

The NuoDB com.nuodb.jdbc.DataSource class can be configured in three ways (for examples, see Database Operations Using Java JDBC Driver).

  1. Using setter methods, each corresponding to the properties listed below. For example to set the maxIdle property, invoke DataSource.setMaxIdle(50);

  2. Set each property in a Java Properties object and pass it to the DataSource constructor. Each property name is defined by a corresponding PROP_XXX constant on the DataSource class:

    Properties properties = new Properties();
...
properties.put(DataSource.PROP_MAX_IDLE, 50);
DataSource dataSource = new DataSource(properties);

  3. As (2) but load the Java Properties object from a properties file. This allows the properties to be externalized, they can be changed without the need to recompile the code, and is recommended.

    url=jdbc:com.nuodb://localhost/test
...
maxIdle=50

DataSource Properties

A small number of connection properties overlap DataSource properties. Specifically:

  • Login credentials: username and password can be defined either as connection properties in the URL or as DataSource properties. For security reasons defining these in the URL is not recommended.

  • Default schema: You can either specify schema=…​ in the connection URL or use the DataSource property defaultSchema. Either is perfectly valid.

Most of the remaining properties supported by NuoDB’s DataSource allow the internal connection pool to be configured.

url, username and password are mandatory but have no default as they are application dependent. All other properties are optional.
Property Description Default

defaultAutoCommit

The default auto-commit state of connections created by this pool. If not set, the default is the NuoDB JDBC driver default. (If not set, the setAutoCommit() method is not called.)

No default

defaultReadOnly

The default read-only state of connections created by this pool. If not set, then read-only state is determined by the Protocol.IsReadOnly which retrieves the default value of false from the server.

No default

defaultSchema

The default schema of connections created by this pool. If not specified, the database will default to the USER schema.

No default

initialSize

The initial number of connections that are created when the pool is started.

10

maxActive

The maximum number of connections (either active or idle) that can be allocated from this pool at the same time.

100

maxAge

Time in milliseconds to keep a connection. When a connection is returned to the pool, the pool checks to see if the current time less the time when the connection was created has reached maxAge. If so, it closes the connection rather than returning it to the pool. A value of 0 means that connections are left open and no age check is performed.

0 (disabled)

maxIdle

The maximum number of connections that should be kept in the pool at all times.

maxActive

maxWait

The maximum number of milliseconds that the pool waits (when there are no available connections) for a connection to be returned before throwing an exception.

0 (wait forever)

minIdle

The minimum number of established connections (either active or idle) that should be kept in the pool at all times.

initialSize

password

The connection password to be passed to the JDBC driver to establish a connection, unless DataSource#getConnection(username,password) is used.

Mandatory, no default

testOnBorrow

Indicates whether objects are validated before being borrowed the pool. If the object fails to validate, it is dropped from the pool and an attempt to borrow another is made. If testOnBorrow is set to true, the validationQuery property must be set to a non-null string or no test is performed. For information on more efficient validation, see validationInterval property.

false

testOnReturn

Indicates whether objects are validated before being returned to the pool. If set to true, the validationQuery property must be set to a non-null string or no test is performed. For information on more efficient validation, see validationInterval property.

false

testWhileIdle

Indicates whether objects are validated by the idle object evictor thread. If an object fails to validate, it is dropped from the pool. If testWhileIdle is set to true, you must set the validationQuery property to a non-null string or no test is performed. For information on more efficient validation, see validationInterval property.

false

timeBetweenEvictionRunsMillis

The number of milliseconds to sleep between runs of the idle connection validation/cleaner thread. This value should not be set under 1 second. It dictates how often we check for idle, abandoned connections, and how often we validate idle connections.

5000

validationInterval

To avoid excess validation, only run validation at most at this frequency, given in milliseconds. If a connection is due for validation, but has been validated previously within this interval, it is not validated again. See testOnReturn, testOnBorrow and testWhileIdle.

30000

validationQuery

The SQL query used to validate connections from this pool.

  • If specified, this query does not have to return any data, it just can’t throw a SQLException. See testOnReturn, testOnBorrow and testWhileIdle.

  • A commonly used query is SELECT 1 /* My app name */ FROM DUAL - putting the application name in the query helps to identify the queries when SQL logging.

null

url

The URL of the NuoDB database. Takes the format:
"jdbc:com.nuodb://<hosts>/<database-name>?<params>"
For example: jdbc:com.nuodb://localhost/test?schema=Hockey.

  • <hosts> defines one or more APs to use to request connections to TEs. Hosts must be separated by the character defined by the url-delimiter property (by default, a comma).

  • See NuoDB Connection URL for more information.

Mandatory, no default

url-delimiter

The delimiter used to separate the URLs of the APs in the value of the url property.

  • For example, using the default delimiter (a comma) a URL containing two hosts might be jdbc:com.nuodb://ap-host1,ap-host2/testdb.

  • Note that ap-host1 and ap-host2 identify the location of admin processes.

, (comma)

username

The connection username to be passed to the NuoDB JDBC driver to establish a connection, unless DataSource#getConnection(username,password) is used.

Mandatory, no default

Examples

Validating Connections

The following code shows how to enable a validation query to check each connection as it is taken (borrowed) from the pool. Note the comment /* My App Name */ in the query, this allows validation queries from different clients to be identified when viewing logs or System tables.

The NuoDB Driver supports isValid() so using a validation query is not the only option. Third-party data sources will typically call isValid().
However if testOnReturn, testOnBorrow and/or testWhileIdle are enabled, a validation query must be provided. A query of the form SELECT 1 FROM DUAL is only marginally slower than invoking isValid(). 
public static final String DATABASE_URL = "jdbc:com.nuodb://localhost/test";

Properties p = new Properties();
p.setProperty(DataSource.PROP_URL, DATABASE_URL);
p.setProperty(DataSource.PROP_USERNAME, user);
p.setProperty(DataSource.PROP_PASSWORD, password);
p.setProperty(DataSource.PROP_TESTONBORROW, "true");
p.setProperty(DataSource.PROP_VALIDATIONQUERY, "SELECT 1 /* My App Name */ FROM DUAL");

DataSource ds = new DataSource(p);
con = ds.getConnection();
....
Validating connections reduces the likelihood of the client receiving a connection to a TE that is no longer available. However it does not guarantee the connection is valid. Your code should always catch SQLTransientException and retry by getting a new connection to a different TE (similar to retrying after deadlock).
The price of validation is an extra query per connection. You must decide if this overhead is worth paying.

Sample Java Application HelloDB

Here is the DataSource configuration from the NuoDB sample JDBC application in samples/doc/java which can be found inside your NuoDB installation. ($NUODB_HOME defaults to /opt/nuodb on Linux and %NUODB_HOME% defaults to C:\Program Files\nuodb on MS Windows)

public void initDataSource() {

    ...

    /* - - - - - - - - DataSource configuration properties. - - - - - - - - */

    /* You might prefer to put these in a Java properties file so they can  */
    /* be changed without rebuilding the code.                              */

    Properties properties = new Properties();

    /* The mandatory properties: Database URL, user name and password. */
    properties.put(DataSource.PROP_URL, dbUrl);
    properties.put(DataSource.PROP_USER, user);
    properties.put(DataSource.PROP_PASSWORD, password);

    /* Probably the most common other property - the default schema to use. */
    /* The property name is actually "defaultSchema".                       */
    properties.put(DataSource.PROP_SCHEMA, DEMO_SCHEMA_NAME);

    /* Connection Pool Setup */
    /* --------------------- */

    /* Overkill for demo, but shows typical & best-practice configuration.  */

    /* Example of a fixed size pool where initial-size is the minimum, and  */
    /* max-active is the maximum.  A typical pool would not be fixed size   */
    /* and have initial-size < max-active.                                  */
    properties.put(DataSource.PROP_INITIALSIZE, 4);
    properties.put(DataSource.PROP_MAXACTIVE, 4);

    /* ALWAYS set a maximum-age (in milliseconds) - here 10 mins.           */
    /* When connection expires, the pool automatically goes to an AP for a  */
    /* new one. This allows APs to constantly rebalance connections across  */
    /* available TEs.                                                       */
    properties.put(DataSource.PROP_MAXAGE, 600000);

    /* Check the connection is valid before giving it to the application.   */
    /* Will fail if it has expired, forcing a new connection and avoiding a */
    /* bad connection exception.                                            */
    properties.put(DataSource.PROP_TESTONBORROW, "true");

    /* Quick query to check the connection is valid. Required when          */
    /* test-on-borrow is enabled. Note the application name as a comment in */
    /* the query string. This can be useful when logging and debugging.     */
    properties.put(DataSource.PROP_VALIDATIONQUERY,
                             "SELECT 1 /** HelloDB **/ FROM DUAL");

    /* Finally, create the data-source (preferred over a Driver Manager as  */
    /* it provides built-in connection pooling).                            */
    dataSource = new com.nuodb.jdbc.DataSource(properties);
}

Fixed Size Pool

During testing and development a small, fixed-size pool containing a limited number of connections can be very useful. Tracking connections in the logs is much easier when there are only a few of them.

For a fixed pool, initialSize and maxActive (the lower and upper bounds of connections in the pool) are both set to the same value. By default, minIdle and maxIdle are set internally to this value also (minIdle defaults to initialSize and maxIdle defaults to maxActive). Also by default, NuoDB will allocate these connections across all available TEs, spreading the load.

    /* A fixed size pool of 5 connections */
    properties.put(DataSource.PROP_INITIALSIZE, 5);
    properties.put(DataSource.PROP_MAXACTIVE, 5);

Working with JNDI

The following code shows how to use a DataSource from within an application server via a JNDI lookup.

The name to lookup (in this example, jdbc/nuoDB) is not predefined. Any name may be used, according to how your server is setup. 
DataSource ds = (DataSource)initialContext.lookup("jdbc/nuoDB");

try (Connection con = ds.getConnection()) { ... use connection ... }

See Defining a JNDI Resource for more details.