1. 2015-12-04 - List installed JDBC drivers in JBoss; Tags: List installed JDBC drivers in JBoss
    Loading...

    List installed JDBC drivers in JBoss

    This post demonstrates how to query at runtime, which JDBC drivers are available in JBoss EAP via the CLI.

    JBoss is shipped with the H2 database.

    [standalone@localhost:9999 /] /subsystem=datasources:installed-drivers-list
    {
        "outcome" => "success",
        "result" => [{
            "driver-name" => "h2",
            "deployment-name" => undefined,
            "driver-module-name" => "com.h2database.h2",
            "module-slot" => "main",
            "driver-datasource-class-name" => "",
            "driver-xa-datasource-class-name" => "org.h2.jdbcx.JdbcDataSource",
            "driver-class-name" => "org.h2.Driver",
            "driver-major-version" => 1,
            "driver-minor-version" => 3,
            "jdbc-compliant" => true
        }]
    }
    
  2. 2015-11-04 - Add Oracle DB datasource to JBoss; Tags: Add Oracle DB datasource to JBoss
    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")
    
  3. 2015-10-29 - Excecute SQL on Connection with H2; Tags: Excecute SQL on Connection with H2
    Loading...

    Excecute SQL on Connection with H2

    The H2 database allows to excecute SQL statement(s) within the connection. This comes handy if you are using the H2 database to test your Java persistence queries.

    String url = "jdbc:h2:mem:test;INIT=runscript from '~/create.sql'\\;runscript from '~/init.sql'";
    

    Please note the double backslash is only required in a Java or properties file. In a GUI, or in an XML file, only one backslash is required:

    <property name="url" value="jdbc:h2:mem:test;INIT=create schema if not exists test\;runscript from '~/sql/init.sql'"/>
    

    One undocumented feature is that you can also take the classpath for the file location. For instance you have in your Maven project in src/test/resources:

    h2.properties
    init.sql
    

    h2.properties contain the JDBC settings

    user=admin
    password=4321
    driver=org.h2.Driver
    url=jdbc:h2:~/demo;DB_CLOSE_DELAY=-1;AUTO_SERVER=TRUE;
    testurl=jdbc:h2:~/test;INIT=RUNSCRIPT FROM 'classpath:init.sql';DB_CLOSE_DELAY=-1;AUTO_SERVER=TRUE;
    

    In the testurl property, the init.sql is taken from the classpath. This can result in following example code:

    static final Connection connect(final String fileName, final boolean test) throws ClassNotFoundException, SQLException, IOException {
        Properties settings = new Properties();
        //establish database connection
        settings.load(new FileInputStream(fileName));
        Class.forName(settings.getProperty("driver"));
        String url = test ? settings.getProperty("testurl") : settings.getProperty("url");
        Connection conn = DriverManager.getConnection(url, settings.getProperty("user"), settings.getProperty("password"));
        return conn;
    }
    
  4. 2015-08-25 - Perform Health-Check on Database Connections with logstash; Tags: Perform Health-Check on Database Connections with logstash
    Loading...

    Perform Health-Check on Database Connections with logstash

    This section describes how to perform health checks on database instances. logstash offers the jdbc input plugin.

    Type Url
    Source Code github
    Reference elastic reference

    Plugin Installation

    At the current logstash version (1.5.3) the jdbc plugin is not included in the default distribution. To install the plugin invoke the ruby gem installation. The plugin name is logstash-input-jdbc. Following example was done on a windows machine.

    bin\plugin.bat install logstash-input-jdbc
    

    Configuration

    This section shows how to perform a health check on the database instance (Oracle DB 11g).

     input {
      jdbc {
        jdbc_driver_library => "C:/Daten/tools/logstash-1.5.3/db/ojdbc6.jar"
        jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
        jdbc_connection_string => "jdbc:oracle:thin:@//localhost:1521/db"
        jdbc_user => "scott"
        jdbc_password => "tiger"
        # will execute on the 0th minute of every hour every day
        # uncomment for testing purpose
        # schedule => "0 * * * *"
        statement => "select 1 from dual"
        jdbc_paging_enabled => "true"
        jdbc_page_size => "1"
        jdbc_validate_connection => "true"
     }
    }
    output {
        stdout { codec => rubydebug }
    }
    

    Pay attention to add the prefix Java:: to the driver class! See also github issue #43

    Testing

    This will give you

    C:\Daten\tools\logstash-1.5.3>bin\logstash.bat -f jdbc.conf
    io/console not supported; tty will not be manipulated
    Logstash startup completed
    {
                 "1" => 1.0,
          "@version" => "1",
        "@timestamp" => "2015-08-18T11:24:37.865Z"
    }
    Logstash shutdown completed
    
  5. 2015-08-18 - Add JDBC driver to your classpath; Tags: Add JDBC driver to your classpath
    Loading...

    Add JDBC driver to your classpath

    If CLASSPATH exists; the driver is appended. For Windows:

    SET ORACLE_JAR=C:\Data\lib\db\ojdbc6-11g.jar
    SET CLASSPATH="%CLASSPATH%;%ORACLE_JAR%"
    ECHO %CLASSPATH%
    "C:\Data\lib\db\ojdbc6-11g.jar"
    

    For Linux:

    export ORACLE_JAR=/opt/lib/db/ojdbc6-11g.jar
    export CLASSPATH="$ORACLE_JAR:$CLASSPATH"
    # check setting
    echo $CLASSPATH