| = 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] |