AD248 - ch06s05

Bookmark this page

Configuring Datasources

Objectives

Configure a data source.
Configure connection validity for data sources.

Creating a Data Source

In Red Hat JBoss Enterprise Application Platform (JBoss EAP), you can find datasoure configurations within the datasource subsystem, in the domain.xml or standalone.xml files.

The data source subsystem has the following structure:

<subsystem xmlns="urn:jboss:domain:datasources:6.0">
<datasources>
    <datasource jndi-name="java:jboss/datasourcse/ExampleDS" 
      jta="true"
      pool-name="ExampleDS" 
      enabled="true" 
      use-java-context="true"> 
        <connection-url>jdbc:h2:mem:test</connection-url> 
        <driver>h2</driver>
        <pool>
          <min-pool-size>5</min-pool-size>
          <max-pool-size>20</max-pool-size>
        </pool>
        <security>
          <user-name>sa</user-name>
          <password>sa</password
        </security>
        <validation>
          <valid-connection-checker class-name="..."/>
          <validate-on-match>true</validate-on-match>
          <exception-sorter class-name="..."/>
        </validation>
        <timeout>
          <blocking-timeout-millis>1000</blocking-timeout-millis>
          <idle-timeout-minutes>3</idle-timeout-minutes>
        </timeout>
        <statement>...</statement>
    </datasource>
    <drivers> 
        <driver name="h2" module="com.h2database.h2">...</driver>
        <driver name="mariadb" module="org.mariadb">...</driver>
    </drivers>
</datasources>
</subsystem>

	The JNDI name used to look up the data source.
	The name of the management pool for the datasource.
	Dictates whether the data source is enabled or disabled.
	Dictates whether to bind the data source to global JNDI.
	The JDBC driver connection URL.
	The connection pool settings.
	Security credentials for connecting to the database.
	The validation strategies for the data source connections.
	Contains child elements which are timeout settings.
	Describes the configuration for the behavior of prepared statements.
	The available drivers definitions.

Any number of <datasource> entries within the <datasources> subsystem can be defined. Each data source requires a unique JNDI name, assigned using the jndi-name attribute. Components that need to obtain a database connection from a pool use the jndi-name value to look up the pool in the JNDI naming service. A valid JNDI name for a data source must start with java: or java:/jboss, for example, java:/jboss/datasources/bookstore.

Some pool configurations in the <pool> section include <min-pool-size>, <max-pool-size>, and <prefill>. The <prefill> configuration attempts to populate the connection pool when the pool is initially created.

The <validation> section allows for various settings for validating database connections in the pool. Connection validation is discussed later in this section.

The <statement> section configures the behavior of prepared statements. The available options are, <prepared-statement-cache-size>, for specifying the number of prepared statements to cache per connection, and <share-prepared-statements> for specifying if non-closed prepared statements can be reused.

Note

Another <statement> configuration is <track-statements>, which can be set to true, false or nowarn. This is a useful option because, when set to true or nowarn, it closes database connections and result sets that were not explicitly closed by the application when the connection is returned to the pool.

The <drivers> section is for defining JDBC drivers that are deployed as modules. The name attribute for the driver corresponds to the value used in the <datasource> definition. The module attribute must be assigned to a deployed module on the server.

Note

The XSD schema file for the datasources subsystem section is in the docs/schema directory of the EAP installation in a file called wildfly-datasources_6_0.xsd.

Database Connection Validation

JBoss EAP can manage outages, network problems, database maintenance, or any other event that can cause the server to lose connection to the database. Using one of several validation methods, users can configure and customize when and how to validate the database connection and how to handle the loss of connection.

To configure when to validate connections JBoss EAP has the validate-on-match and the background-validation options.

To configure how to validate connections JBoss EAP has the valid-connection-checker-class-name and the check-valid-connection-sql options.

You can configure validations by editing the server configuration file as explained in the following sections.

Validate on Match

If the validate-on-match value is set to true, then the connection validates every time it is pulled from the connection.

The following is an example of using the validate-on-match method:

<datasources>
  <datasource ...>
    <validation>
      <validate-on-match>true</validate-on-match> 
      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
    </validation>
  </datasource>
</datasources>

	Can be `true` or `false` to enable or disable the validate on match.
	The class name for the exception sorter, specific to the database vendor.

Background Validation

If the background-validation value is set to true, then the connection validates based on the value set in the <background-validation-millis>.

The following is an example of using the background-validation method:

<datasources>
  <datasource ...>
    <validation>
      <background-validation>true</background-validation>
      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
    </validation>
  </datasource>
</datasources>

	Can be `true` or `false` to enable or disable the background validation.
	The class name for the exception sorter, specific to the database vendor.

Note

Both validation methods can be defined, however only one can be enabled at a time.

Valid Connection Checker

JBoss EAP provides several classes that can be used to validate a database connection that is specific to the relational database management system in use. For example, for a MySQL validation class checker, displays as follows:

<valid-connection-checker
class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>

Check Valid Connection SQL

This validation mechanism requires the user to provide as SQL statement to validate the connection. The following is an example for a MySQL database connection:

<check-valid-connection-sql>select 1</check-valid-connection-sql>

Exception Sorter

By using an exception sorter, users can provide a class to properly detect and clean up after fatal connection exceptions. JBoss EAP provides several exception sorter classes based on the relational database management system in use. The following is an example for a MySQL exception sorter:

<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>

Creating Data Sources with the Management Console

The EAP management console provides a way to create a data source by using templates that are preconfigured for different database vendors. The following steps can be used to create a non-XA data source with the management console in a standalone server.

Click Configuration from the top of the management console and aviagate to Subsystems>Datasources & Drivers>Datasources to access the non-XA data source page. Then click Add Datasource in the + drop-down menu.
Select a database template and click Next. If none of them are valid, then select the Custom option and see the JDBC driver documentation to complete the following steps.
Enter the datasource attributes and click Next.
Select a database driver in the Driver Name drop-down menu and click Next.
Enter the connection URL, username, and password for the database and click Next.
Click Test Connection to verify that the datasource can connect to the database. Note that if you test the connection at this step, then the management console creates the datasource. You can click Next and test the connection later.
On the final step, notice that the connection URL is formatted for the correct MySQL syntax. Click Done to finish creating the data source.
Finally, a window with a configuration summary shows and you can click Finish.

Testing the Data Source

The management console provides a Test Connection button to verify that the connections from a connection pool can access the database.

Figure 6.7: Test connection button

Likewise, the management CLI can be used to test if the data source was correctly configured. To validate it, use the following command:

[standalone@localhost /] /subsystem=datasources/\
data-source=datasource_name:test-connection-in-pool

Creating Dataources with the EAP Management CLI

You can create data sources by using the management CLI. For example, the following command creates a MySQL datasource, called MySqlDS, with connection validation configured:

[standalone@localhost] data-source add --name=MySqlDS --jndi-name=java:jboss/MySqlDS \
--driver-name=mysql \
--connection-url=jdbc:mysql://localhost:3306/jbossdb \
--user-name=admin --password=admin \
--validate-on-match=true --background-validation=false \
--valid-connection-checker-class-name=\
org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker \
--exception-sorter-class-name=\
org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter

In this case, the data source validates each connection as it is pulled from the connection pool by using the MySQLValidConnectionChecker validation class.

The previous command generates the following datasource configuration in the datasource subsystem:

<datasources>
  <datasource jndi-name="java:jboss/MySqlDS" pool-name="MySqlDS"> 
    <connection-url>jdbc:mysql://localhost:3306/jbossdb</connection-url>
    <driver>mysql</driver> 
    <security> 
      <user-name>admin</user-name>
      <password>admin</password>
    </security>
    <validation>
      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
      <validate-on-match>true</validate-on-match> 
      <background-validation>false</background-validation>
      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
    </validation>
  </datasource>
</datasources>

	The JNDI name that would be used to look up the data source by a component on the EAP server.
	The name that refers to the driver that is defined in the `<drivers>` section below the data source.
	The credentials used to access the MySQL database.
	Both validate on match and background validation are defined, but only one of them can be enabled.

You can use a similar command in domain mode to add a data source to a specific profile:

[domain@localhost] data-source add --name=MySqlDS --profile=full-ha \
--jndi-name=java:jboss/MySqlDS --driver-name=mysql \
--connection-url=jdbc:mysql://localhost:3306/jbossdb \
--user-name=admin --password=admin \
--validate-on-match=true --background-validation=false \
--valid-connection-checker-class-name=\
org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker \
--exception-sorter-class-name=\
org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter

References

For more information, refer to the Creating Datasources section in the Datasource Management chapter in the Red Hat Red Hat Jboss Enterprise Application Platform 7.4 Configuration Guide documentation at https://access.redhat.com/documentation/en-us/red_hat_jboss_enterprise_application_platform/7.4/html-single/configuration_guide/index#adding_datasources

For more information, refer to the Modifying Datasources section in the Datasource Management chapter in the Red Hat Red Hat Jboss Enterprise Application Platform 7.4 Configuration Guide documentation at https://access.redhat.com/documentation/en-us/red_hat_jboss_enterprise_application_platform/7.4/html-single/configuration_guide/index#modifying_datasources

For more information, refer to the Removing Datasources section in the Datasource Management chapter in the Red Hat Red Hat Jboss Enterprise Application Platform 7.4 Configuration Guide documentation at https://access.redhat.com/documentation/en-us/red_hat_jboss_enterprise_application_platform/7.4/html-single/configuration_guide/index#removing_datasources

Discuss Red Hat JBoss Enterprise Application Platform Administration I

Go to community

Welcome to the Red Hat JBoss Application Administration 1 (AD248) group!

Deanna

26 wrz 2023

We are excited to launch a space dedicated to the Red Hat Training course Red Hat JBoss Application Administration I! To gain the most value from this group - click the "Join Group" button in the upper right hand corner of the group home page.We encourage group members to collaborate in this group to discuss topics, ask questions, share best practices and tips, provide course feedback, and share their accomplishments as it relates to AD248.Read more about Red Hat JBoss Application Administration I here.

270

Revision: ad248-7.4-18a9db2