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 three ways (see Database Operations Using Java JDBC Driver)
-
Using setter methods, each corresponding to the properties listed below. For example to set the
maxIdle
property, invokeDataSource.setMaxIdle(50);
-
Set each property in a Java Properties object and pass it to the
DataSource
constructor. Each property name is defined by a correspondingPROP_XXX
constant on theDataSource
class:Properties properties = new Properties(); ... properties.put(DataSource.PROP_MAX_IDLE, 50); DataSource dataSource = new DataSource(properties);
-
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
andpassword
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 theDataSource
propertydefaultSchema
. Either is perfectly valid.
Most of the properties supported by NuoDB’s DataSource allow the internal connection pool to be configured.
Property | Description | Default | ||
---|---|---|---|---|
username |
The connection username to be passed to the NuoDB JDBC driver to establish a connection, unless |
- |
||
password |
The connection password to be passed to the JDBC driver to establish a connection, unless |
- |
||
defaultSchema |
The default schema of connections created by this pool. |
- |
||
defaultReadOnly |
The default read-only state of connections created by this pool.
If not set, then readOnly is determined by the |
- |
||
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 |
- |
||
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 |
||
maxIdle |
The maximum number of connections that should be kept in the pool at all times. |
maxActive |
||
minIdle |
The minimum number of established connections (either active or idle) that should be kept in the pool at all times. |
initialSize |
||
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 |
||
maxAge |
Time in milliseconds to keep this 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 |
0 |
||
testOnReturn |
The indication of whether objects are validated before being returned to the pool.
If set to true, the |
false |
||
testOnBorrow |
The indication of 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 |
false |
||
testWhileIdle |
The indication of whether objects are validated by the idle object evictor.
If an object fails to validate, it is dropped from the pool.
If |
false |
||
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 |
null |
||
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 |
30000 |
||
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 |
||
url |
The URL of the NuoDB database.
Takes the format: "jdbc:com.nuodb://<hosts>/<database-name>?<params>" - for example
|
|||
url-delimiter |
The delimiter used to separate the URLs of the APs in the value of the For example, using the default delimiter (a comma) a URL containing two hosts might be
|
, |
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 logging or viewing System tables.
Properties p = new Properties();
p.setProperty(DataSource.PROP_URL, "jdbc:com.nuodb://localhost/test");
p.setProperty(DataSource.PROP_USERNAME, user);
p.setProperty(DataSource.PROP_PASSWORD, password);
p.setProperty(DataSource.PROP_TEST_ON_BORROW, "true");
p.setProperty(DataSource.PROP_VALIDATION_QUERY, "SELECT 1 /* My App Name */ FROM DUAL");
DataSource ds = new DataSource(p);
con = ds.getConnection();
....
Validating connections reduces the likelihood of the client recieving 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.The price of validation is an extra query per connection. You must decide if this overhead is worth paying. |
Working with JNDI
The following code shows how to use a DataSource
from within an application server via a JNDI lookup.
DataSource ds = (DataSource)initContext.lookup("jdbc/nuoDB");
try (Connection con = ds.getConnection()) { ... use connection ... }
See Defining a JNDI Resource for more details.