Configure mysql datasource in jboss 7

Once the JDBC driver is installed, you need to configure the datasource in the application server. In JBoss AS 7, you can configure two kind of datasources, local datasources and xa-datasources.

 

Here’s a sample to configure MySQL datasource in jboss 7 :

<datasources>

<datasource jndi-name="java:/MySqlDS" pool-name="MySqlDS_Pool" enabled="true" jta="true" use-java-context="true" use-ccm="true">

<connection-url>jdbc:mysql://localhost:3306/MyDB</connection-url>

<driver>mysql</driver>

<pool />

<security>

<user-name>jboss</user-name>

<password>jboss</password>

</security>

<statement/>

<timeout>

<idle-timeout-minutes>0</idle-timeout-minutes>

<query-timeout>600</query-timeout>

</timeout>

</datasource>

<drivers>

<driver name="mysql" module="com.mysql"/>

</drivers>

</datasources>

The new configuration file borrows the same XML schema definition from the earlier -*.ds.xml file, so it should not be difficult to migrate to the new configuration. Basically, you would define the connection path to the database using the connection-url and the JDBC driver class with the driver section.

The pool section can be used to define the JDBC Connection pool properties, leaving in this case to the default values. Then the security section lets you configure the connection credentials.

The statement section as well is added just as place holder for statement caching options.

The optional timeout section contains a set elements, such as the query-timeout, which is a static configuration of the maximum of seconds before a query times out. Also the included idle-timeout-minutes element indicates the maximum time a connection may be idle before being closed. Setting to 0 disables it. Default is 15 minutes.

Configuring the connection pool

In order to use connection pooling, no configuration is required, because without any configuration JBoss AS will choose some default settings. However, if you want to customize how pooling is done, such as to control the size of the pools and which types of connections are pooled, you would be better learning about its available attributes.

Here’s an example of pool configuration, which can be added to your datasource configuration:

<pool>

<min-pool-size>5</min-pool-size>

<max-pool-size>10</max-pool-size>

<prefill>true</prefill>

<use-strict-min>true</use-strict-min>

</pool>

The attributes included in the pool configuration are actually borrowed from earlier releases, so we include them here for your reference:

min-pool-size - The minimum number of connections in the pool (default 0 - zero)
 max-pool-size - The maximum number of connections in the pool (default 20) prefill Attempt to pre-fill the connection pool to the minimum number of connections
 use-strict-min - Whether idle connections below the min-pool-size should be closed

Configuring the statement cache

For each connection in a connection pool in your system, JBoss AS Server is able to create a statement cache. When a prepared statement or callable statement is used on a connection, JBoss AS caches the statement so that it can be reused. In order to activate the statement cache, you have to specify a value of prepared-statementcache-size greater than 0:

<statement>

<track-statements>true</track-statements>

<prepared-statement-cache-size>10</prepared-statement-cache-size>

<share-prepared-statements/>

</statement>

Notice, we have also included the track-statements to true in the statement section, which enable automatic closing of statements and ResultSets. This is important if you want to use prepared statement caching and/or don’t want to leak cursors in your database. The last element, share-prepared-statements, can be used only with prepared statement cache enabled and determine whether the two requests in the same transaction should return the same statement (default false).

Adding an xa-datasource

Adding an xa-datasource requires some tweaks in the datasource configuration. As a matter of fact, the connection information is now acquired as xa-datasource properties. Also the xa-datasource class needs to be specified in the driver section In the following code, we are adding the equivalent configuration for our MySQL JDBC driver, which now is used to set up an xa-datasource:

<datasources>

<xa-datasource jndi-name="java:/XAMySqlDS" pool-name="MySqlDS_Pool" enabled="true" use-java-context="true" use-ccm="true">

<xa-datasource-property name="URL">jdbc:mysql://localhost:3306/MyDB</xa-datasource-property>

<xa-datasource-property name="User">jboss</xa-datasource-property>

<xa-datasource-property name="Password">jboss</xa-datasource-property>

<driver>mysql-xa</driver>
</xa-datasource>

<drivers>

<driver name="mysql-xa" module="com.mysql">

<xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>

</driver>

</drivers>

</datasources>

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.