Loading...

Add Oracle DB datasource to JBoss

This post demonstrates how to establish a data-source configuration for JBoss EAP with the Oracle DB.

Dump the contents of the help command to help.txt

[standalone@localhost:9999 /] data-source --help --properties > help.txt

The contents

 --name                 - Required argument in commands which identifies the
                          instance to execute the command against.
 --share-prepared-statements  - (BOOLEAN,read-write) Whether to share prepared
                                statements, i.e. whether asking for same
                        statement twice without closing uses the same underlying
                        prepared statement
 --driver-class         - (STRING,read-write) The fully qualified name of the
                          JDBC driver class
 --prepared-statements-cache-size  - (LONG,read-write) The number of prepared
                                     statements per connection in an LRU cache
 --spy                  - (BOOLEAN,read-write) Enable spying of SQL statements
 --password             - (STRING,read-write) Specifies the password used when
                          creating a new connection
 --max-pool-size        - (INT,read-write) The max-pool-size element specifies
                          the maximum number of connections for a pool. No more
                        connections will be created in each sub-pool
 --set-tx-query-timeout  - (BOOLEAN,read-write) Whether to set the query timeout
                           based on the time remaining until transaction
                        timeout. Any configured query timeout will be used if
                        there is no transaction
 --jndi-name            - (STRING,read-write) Specifies the JNDI name for the
                          datasource
 --url-delimiter        - (STRING,read-write) Specifies the delimiter for URLs
                          in connection-url for HA datasources
 --enabled              - (BOOLEAN,read-write) Specifies if the datasource
                          should be enabled. Note this attribute will not be
                        supported runtime in next versions.
 --connectable          - (BOOLEAN,read-write) Enable the use of CMR. This
                          feature means that a local resource can reliably
                        participate in an XA transaction.
 --blocking-timeout-wait-millis  - (LONG,read-write) The blocking-timeout-millis
                                   element specifies the maximum time, in
                        milliseconds, to block while waiting for a connection
                        before throwing an exception. Note that this blocks only
                        while waiting for locking a connection, and will never
                        throw an exception if creating a new connection takes an
                        inordinately long time
 --statistics-enabled   - (BOOLEAN,read-write) define if runtime statistics is
                          enabled or not.
 --pool-use-strict-min  - (BOOLEAN,read-write) Specifies if the min-pool-size
                          should be considered strictly
 --validate-on-match    - (BOOLEAN,read-write) The validate-on-match element
                          specifies if connection validation should be done when
                        a connection factory attempts to match a managed
                        connection. This is typically exclusive to the use of
                        background validation
 --transaction-isolation  - (STRING,read-write) Set the java.sql.Connection
                            transaction isolation level. Valid values are:
                        TRANSACTION_READ_UNCOMMITTED,
                        TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ,
                        TRANSACTION_SERIALIZABLE and TRANSACTION_NONE
 --jta                  - (BOOLEAN,read-write) Enable JTA integration
 --valid-connection-checker-class-name  - (STRING,read-write) An
                                          org.jboss.jca.adapters.jdbc.ValidConne
                        ctionChecker that provides an
                        isValidConnection(Connection) method to validate a
                        connection. If an exception is returned that means the
                        connection is invalid. This overrides the
                        check-valid-connection-sql element
 --allocation-retry-wait-millis  - (LONG,read-write) The allocation retry wait
                                   millis element specifies the amount of time,
                        in milliseconds, to wait between retrying to allocate a
                        connection
 --exception-sorter-properties  - (OBJECT,read-write) The exception sorter
                                  properties
 --background-validation-millis  - (LONG,read-write) The
                                   background-validation-millis element
                        specifies the amount of time, in milliseconds, that
                        background validation will run. Changing this value can
                        be done only on disabled datasource,  requires a server
                        restart otherwise
 --track-statements     - (STRING,read-write) Whether to check for unclosed
                          statements when a connection is returned to the pool,
                        result sets are closed, a statement is closed or return
                        to the prepared statement cache. Valid values are:
                        "false" - do not track statements, "true" - track
                        statements and result sets and warn when they are not
                        closed, "nowarn" - track statements but do not warn
                        about them being unclosed
 --use-fast-fail        - (BOOLEAN,read-write) Whether to fail a connection
                          allocation on the first try if it is invalid (true) or
                        keep trying until the pool is exhausted of all potential
                        connections (false)
 --flush-strategy       - (STRING,read-write) Specifies how the pool should be
                          flush in case of an error. Valid values are:
                        FailingConnectionOnly (default), IdleConnections and
                        EntirePool
 --stale-connection-checker-class-name  - (STRING,read-write) An
                                          org.jboss.jca.adapters.jdbc.StaleConne
                        ctionChecker that provides an
                        isStaleConnection(SQLException) method which if it
                        returns true will wrap the exception in an
                        org.jboss.jca.adapters.jdbc.StaleConnectionException
 --exception-sorter-class-name  - (STRING,read-write) An
                                  org.jboss.jca.adapters.jdbc.ExceptionSorter
                        that provides an isExceptionFatal(SQLException) method
                        to validate if an exception should broadcast an error
 --background-validation  - (BOOLEAN,read-write) An element to specify that
                            connections should be validated on a background
                        thread versus being validated prior to use. Changing
                        this value can be done only on disabled datasource,
                        requires a server restart otherwise.
 --check-valid-connection-sql  - (STRING,read-write) Specify an SQL statement to
                                 check validity of a pool connection. This may
                        be called when managed connection is obtained from the
                        pool
 --reauth-plugin-class-name  - (STRING,read-write) The fully qualified class
                               name of the reauthentication plugin
                        implementation
 --allow-multiple-users  - (BOOLEAN,read-write) Specifies if multiple users will
                           access the datasource through the getConnection(user,
                        password) method and hence if the internal pool type
                        should account for that
 --url-selector-strategy-class-name  - (STRING,read-write) A class that
                                       implements
                        org.jboss.jca.adapters.jdbc.URLSelectorStrategy
 --user-name            - (STRING,read-write) Specify the user name used when
                          creating a new connection
 --use-ccm              - (BOOLEAN,read-write) Enable the use of a cached
                          connection manager
 --reauth-plugin-properties  - (OBJECT,read-write) The properties for the
                               reauthentication plugin
 --driver-name          - (STRING,read-write) Defines the JDBC driver the
                          datasource should use. It is a symbolic name matching
                        the the name of installed driver. In case the driver is
                        deployed as jar, the name is the name of deployment unit
 --stale-connection-checker-properties  - (OBJECT,read-write) The stale
                                          connection checker properties
 --datasource-class     - (STRING,read-write) The fully qualified name of the
                          JDBC datasource class
 --idle-timeout-minutes  - (LONG,read-write) The idle-timeout-minutes elements
                           specifies the maximum time, in minutes, a connection
                        may be idle before being closed. The actual maximum time
                        depends also on the IdleRemover scan time, which is half
                        of the smallest idle-timeout-minutes value of any pool.
                        Changing this value can be done only on disabled
                        datasource, requires a server restart otherwise.
 --query-timeout        - (LONG,read-write) Any configured query timeout in
                          seconds. If not provided no timeout will be set
 --use-java-context     - (BOOLEAN,read-write) Setting this to false will bind
                          the datasource into global JNDI
 --valid-connection-checker-properties  - (OBJECT,read-write) The valid
                                          connection checker properties
 --min-pool-size        - (INT,read-write) The min-pool-size element specifies
                          the minimum number of connections for a pool
 --allocation-retry     - (INT,read-write) The allocation retry element
                          indicates the number of times that allocating a
                        connection should be tried before throwing an exception
 --security-domain      - (STRING,read-write) Specifies the security domain
                          which defines the javax.security.auth.Subject that are
                        used to distinguish connections in the pool
 --new-connection-sql   - (STRING,read-write) Specifies an SQL statement to
                          execute whenever a connection is added to the
                        connection pool
 --connection-url       - (STRING,read-write) The JDBC driver connection URL
 --use-try-lock         - (LONG,read-write) Any configured timeout for internal
                          locks on the resource adapter objects in seconds
 --pool-prefill         - (BOOLEAN,read-write) Should the pool be prefilled.
                          Changing this value can be done only on disabled
                        datasource, requires a server restart otherwise.

Add driver modules

#    _     _ _
#   (_) __| | |__   ___
#   | |/ _` | '_ \ / __|
#   | | (_| | |_) | (__
#  _/ |\__,_|_.__/ \___|
# |__/
#
# install jdbc driver with i18n
# module remove --name=com.oracle.jdbc6
# module remove --name=com.oracle.orai18n
module add --name=com.oracle.jdbc6 \
    --resources=${deploy.dir}/deploy/jdbc/ojdbc6-11203.jar \
    --dependencies=javax.api,javax.transaction.api
module add --name=com.oracle.orai18n \
    --resources=${deploy.dir}/deploy/jdbc/orai18n-11203.jar
    --dependencies=com.oracle.jdbc6,javax.api,javax.transaction.api

Add datasource

#      _       _
#   __| | __ _| |_ __ _ ___  ___  _   _ _ __ ___ ___
#  / _` |/ _` | __/ _` / __|/ _ \| | | | '__/ __/ _ \
# | (_| | (_| | || (_| \__ \ (_) | |_| | | | (_|  __/
#  \__,_|\__,_|\__\__,_|___/\___/ \__,_|_|  \___\___|
#
/subsystem=datasources/jdbc-driver=oracle:add( \
    driver-name=oracle, \
    driver-module-name=com.oracle.jdbc6, \
    driver-xa-datasource-class-name=oracle.jdbc.driver.OracleDriver)
# pool-name will be demo
/subsystem=datasources/data-source=demo:add( \
    jta=true, \
    jndi-name=java:jboss/datasources/demo, \
    enabled=true, \
    use-java-context=true, \
    use-ccm=true, \
    connection-url=${jdbc.url}, \
    driver-name=oracle, \
    transaction-isolation=TRANSACTION_READ_COMMITTED, \
    min-pool-size=1, \
    max-pool-size=10, \
    pool-prefill=true, \
    flush-strategy=FailingConnectionOnly, \
    user-name=scott, \
    password=tiger, \
    check-valid-connection-sql="SELECT 1 FROM DUAL")