docs/datasource-config.adoc

= DataSource Configuration
:index-group: Datasource
:jbake-date: 2018-12-05
:jbake-type: page
:jbake-status: published


A DataSource can be declared via xml in the
`<tomee-home>/conf/tomee.xml` file or in a `WEB-INF/resources.xml` file
using a declaration like the following. All properties in the element
body are optional.

[source,xml]
----
<Resource id="myDataSource" type="javax.sql.DataSource">
    accessToUnderlyingConnectionAllowed = false
    alternateUsernameAllowed = false
    connectionProperties = 
    defaultAutoCommit = true
    defaultReadOnly = 
    definition = 
    ignoreDefaultValues = false
    initialSize = 0
    jdbcDriver = org.hsqldb.jdbcDriver
    jdbcUrl = jdbc:hsqldb:mem:hsqldb
    jtaManaged = true
    maxActive = 20
    maxIdle = 20
    maxOpenPreparedStatements = 0
    maxWaitTime = -1 millisecond
    minEvictableIdleTime = 30 minutes
    minIdle = 0
    numTestsPerEvictionRun = 3
    password = 
    passwordCipher = PlainText
    poolPreparedStatements = false
    serviceId = 
    testOnBorrow = true
    testOnReturn = false
    testWhileIdle = false
    timeBetweenEvictionRuns = -1 millisecond
    userName = sa
    validationQuery = 
</Resource>
----

Alternatively, a DataSource can be declared via properties in the
`<tomee-home>/conf/system.properties` file or via Java VirtualMachine
`-D` properties. The properties can also be used when embedding TomEE
via the `javax.ejb.embeddable.EJBContainer` API or `InitialContext`

[source,properties]
----
myDataSource = new://Resource?type=javax.sql.DataSource
myDataSource.accessToUnderlyingConnectionAllowed = false
myDataSource.alternateUsernameAllowed = false
myDataSource.connectionProperties = 
myDataSource.defaultAutoCommit = true
myDataSource.defaultReadOnly = 
myDataSource.definition = 
myDataSource.ignoreDefaultValues = false
myDataSource.initialSize = 0
myDataSource.jdbcDriver = org.hsqldb.jdbcDriver
myDataSource.jdbcUrl = jdbc:hsqldb:mem:hsqldb
myDataSource.jtaManaged = true
myDataSource.maxActive = 20
myDataSource.maxIdle = 20
myDataSource.maxOpenPreparedStatements = 0
myDataSource.maxWaitTime = -1 millisecond
myDataSource.minEvictableIdleTime = 30 minutes
myDataSource.minIdle = 0
myDataSource.numTestsPerEvictionRun = 3
myDataSource.password = 
myDataSource.passwordCipher = PlainText
myDataSource.poolPreparedStatements = false
myDataSource.serviceId = 
myDataSource.testOnBorrow = true
myDataSource.testOnReturn = false
myDataSource.testWhileIdle = false
myDataSource.timeBetweenEvictionRuns = -1 millisecond
myDataSource.userName = sa
myDataSource.validationQuery = 
----

Properties and xml can be mixed. Properties will override the xml
allowing for easy configuration change without the need for $\{} style
variable substitution. Properties are not case sensitive. If a property
is specified that is not supported by the declared DataSource a warning
will be logged. If a DataSource is needed by the application and one is
not declared, TomEE will create one dynamically using default settings.
Multiple DataSource declarations are allowed.

See the link:common-datasource-configurations.html[Common DataSource
Configurations] page for examples of configuring datasources for Derby,
MySQL, Oracle and other common databases.

== Supported Properties

Property

Type

Default

Description

accessToUnderlyingConnectionAllowed

boolean

false

If true the raw physical connection to the database can be accessed

alternateUsernameAllowed

boolean

false

If true allow an alternate username and password to be specified on the
connection, rather than those specified in the DataSource definition..

connectionProperties

String

The connection properties that will be sent to the JDBC driver when
establishing new connections

defaultAutoCommit

boolean

true

The default auto-commit state of new connections

defaultReadOnly

String

The default read-only state of new connections If not set then the
setReadOnly method will not be called. (Some drivers don't support read
only mode, ex: Informix)

definition

String

ignoreDefaultValues

boolean

false

use only all set values in this config will need a lot of properties but
allow to not set some values

initialSize

int

0

The size to reach when creating the datasource.

jdbcDriver

String

org.hsqldb.jdbcDriver

Driver class name

jdbcUrl

java.net.URI

jdbc:hsqldb:mem:hsqldb

Url for creating connections

jtaManaged

boolean

true

Determines wether or not this data source should be JTA managed or user
managed.

maxActive

int

20

The maximum number of active connections that can be allocated from this
pool at the same time, or a negative number for no limit. N.B. When
using dbcp2 with TomEE 7 ("DataSourceCreator dbcp"), "MaxTotal" should
be used as opposed to "MaxActive".

maxIdle

int

20

The maximum number of connections that can remain idle in the pool,
without extra ones being released, or a negative number for no limit.

maxOpenPreparedStatements

int

0

The maximum number of open statements that can be allocated from the
statement pool at the same time, or zero for no limit.

maxWaitTime

time

-1 millisecond

The maximum number of time that the pool will wait (when there are no
available connections) for a connection to be returned before throwing
an exception, or -1 to wait indefinitely.

minEvictableIdleTime

time

30 minutes

The minimum amount of time a connection may sit idle in the pool before
it is eligable for eviction by the idle connection evictor (if any).

minIdle

int

0

The minimum number of connections that can remain idle in the pool,
without extra ones being created, or zero to create none.

numTestsPerEvictionRun

int

3

The number of connectionss to examine during each run of the idle
connection evictor thread (if any).

password

String

Default password

passwordCipher

String

PlainText

poolPreparedStatements

boolean

false

If true, a statement pool is created for each Connection and
PreparedStatements created by one of the following methods are pooled:

serviceId

String

testOnBorrow

boolean

true

If true connections will be validated before being returned from the
pool. If the validation fails, the connection is destroyed, and a new
conection will be retrieved from the pool (and validated).

testOnReturn

boolean

false

If true connections will be validated before being returned to the pool.
If the validation fails, the connection is destroyed instead of being
returned to the pool.

testWhileIdle

boolean

false

If true connections will be validated by the idle connection evictor (if
any). If the validation fails, the connection is destroyed and removed
from the pool

timeBetweenEvictionRuns

time

-1 millisecond

The number of milliseconds to sleep between runs of the idle connection
evictor thread. When set to a negative number, no idle connection
evictor thread will be run.

userName

String

sa

Default user name

validationQuery

String

The SQL query that will be used to validate connections from this pool
before returning them to the caller. If specified, this query MUST be an
SQL SELECT statement that returns at least one row.

LogSql

boolean

false

Wether SQL queries should be logged or not

== accessToUnderlyingConnectionAllowed

If true the raw physical connection to the database can be accessed
using the following construct:

[source,java]
----
Connection conn = ds.getConnection();
Connection rawConn = ((DelegatingConnection) conn).getInnermostDelegate();
...
conn.close()
----

Default is false, because misbehaving programs can do harmfull things to
the raw connection shuch as closing the raw connection or continuing to
use the raw connection after it has been assigned to another logical
connection. Be careful and only use when you need direct access to
driver specific extensions.

NOTE: Do NOT close the underlying connection, only the original logical
connection wrapper.

== connectionProperties

The connection properties that will be sent to the JDBC driver when
establishing new connections

Format of the string must be [propertyName=property;]*

NOTE - The "user" and "password" properties will be passed explicitly,
so they do not need to be included here.

== TransactionIsolation

The default TransactionIsolation state of new connections.

If not set then the `setTransactionIsolation` method will not be called.
The allowed values for this property are:

* `NONE`
* `READ_COMMITTED`
* `READ_UNCOMMITTED`
* `REPEATABLE_READ`
* `SERIALIZABLE`

Note: Most JDBC drivers do not support all isolation levels
DefaultTransactionIsolation

== initialSize

The initial size to initialize the pool of connections.

== jtaManaged

Determines wether or not this data source should be JTA managed or user
managed.

If set to 'true' it will automatically be enrolled in any ongoing
transactions. Calling begin/commit/rollback or setAutoCommit on the
datasource or connection will not be allowed. If you need to perform
these functions yourself, set `JtaManaged` to `false`

In terms of JPA persistence.xml:

* `JtaManaged=true` can be used as a 'jta-data-source'
* `JtaManaged=false` can be used as a 'non-jta-data-source'

== maxOpenPreparedStatements

The maximum number of open statements that can be allocated from the
statement pool at the same time, or zero for no limit.

NOTE - Some drivers have limits on the number of open statements, so
make sure there are some resources left for the other (non-prepared)
statements.

== poolPreparedStatements

If true, a statement pool is created for each Connection and
PreparedStatements created by one of the following methods are pooled:

[source,java]
----
public PreparedStatement prepareStatement(String sql);
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
----

== testOnBorrow

If true connections will be validated before being returned from the
pool. If the validation fails, the connection is destroyed, and a new
conection will be retrieved from the pool (and validated).

NOTE - for a true value to have any effect, the ValidationQuery
parameter must be set.

== testOnReturn

If true connections will be validated before being returned to the pool.
If the validation fails, the connection is destroyed instead of being
returned to the pool.

NOTE - for a true value to have any effect, the ValidationQuery
parameter must be set.

== testWhileIdle

If true connections will be validated by the idle connection evictor (if
any). If the validation fails, the connection is destroyed and removed
from the pool

NOTE - for a true value to have any effect, the
timeBetweenEvictionRunsMillis property must be a positive number and the
ValidationQuery parameter must be set.

== XADataSource

There are several ways to configure a XADataSource. Depending the
underlying datasource (Oracle, MySQL one or the other solution can be
more adapted.

This part deals with `JtaManaged` XaDataSource since a not managed
XaDataSource can be defined as a standard resource using `class-name`.

=== Single definition

First solution is to define as `JdbcDriver` an XADataSource:

[source,xml]
----
<Resource id="myXaDs" type="DataSource">
    JdbcDriver = org.foo.MyXaDataSource

    myXaProperty = value

    myPoolProperty = 10
</Resource>
----

This solution merges properties for the XaDataSource and the pool
(tomcat-jdbc for TomEE, dbcp for OpenEJB by default but still
configurable with DataSourceCreator).

Note: in this case for Oracle for instance you'll define UserName for
the pool and User for the datasource which can look weird if you don't
know properties are used for 2 instances (pool and datasource).

Note: this solution uses the same logic than `@DataSourceDefinition`
factory mecanism.

=== Two resources definition

An alternative is to define a resource for the XaDataSource:

[source,xml]
----
<Resource id="myXa" class-name="org.foo.MyXaDataSource">
    myXaProperty = value
</Resource>
----

And then wrap it in the pool:

[source,xml]
----
<Resource id="myXaDs" type="DataSource">
    DataSourceCreator = [dbcp|dbcp-alternative]
    myPoolProperty = 10
</Resource>
----

Note: `dbcp` is more adapted than `dbcp-alternative` in most of the case
because it is reusing direct dbcp JTA management.

=== Known issues

For TomEE 1.7.0/1.7.1 you can need to add the property:

[source,properties]
----
 openejb.datasource.pool = true
----

in resource properties to ensure the resource is pooled.

=== Details about DataSource and their factories (advanced configuration)

link:datasource-configuration-by-creator.html[Configuration by creator]