We’ll assume that you’ve already installed MySQL and that you have it running and are familiar with the basics. Run the mysql client program from the command line so we can execute some administration commands. You should make sure that you are connected as a user with sufficient privileges (e.g. by specifying the -u root option to run as the MySQL root user).

First create a database called jboss within MySQL for use by JBoss.

mysql> CREATE DATABASE jboss; 
Query OK, 1 row affected (0.05 sec) 

Then check that it has been created.

mysql> SHOW DATABASES; 
+----------+ 
| Database | 
+----------+ 
| jboss    | 
| mysql    | 
| test     | 
+----------+ 
3 rows in set (0.00 sec) 

Next, create a user called jboss with password password to access the database.

mysql> GRANT ALL PRIVILEGES ON jboss.* TO jboss@localhost IDENTIFIED BY 'password'; 
Query OK, 0 rows affected (0.06 sec) 

Again, you can check that everything has gone smoothly.

mysql> select User,Host,Password from mysql.User; 
+-------+-----------+------------------+ 
| User  | Host      | Password         | 
+-------+-----------+------------------+ 
| root  | localhost |                  |
| root  | %         |                  | 
|       | localhost |                  | 
|       | %         |                  | 
| jboss | localhost | 5d2e19393cc5ef67 | 
+-------+-----------+------------------+ 
5 rows in set (0.02 sec) 

To make the JDBC driver classes available to JBoss, copy the file mysql-connector-java-3.0.15-ga-bin.jar from the Connector/J distribution to the lib directory in the default server configuration (assuming that is the configuration you’re running, of course). Then create a file in the deploy directory called mysql-ds.xml with the following datasource configuration. The database user name and password corresponds the MySql user we created in the previous section.

<datasources> 
    <local-tx-datasource> 
        <jndi-name>MySqlDS</jndi-name> 
        <connection-url>jdbc:mysql://localhost:3306/jboss</connection-url> 
        <driver-class>com.mysql.jdbc.Driver</driver-class> 
        <user-name>jboss</user-name> 
        <password>password</password> 
    </local-tx-datasource> 
</datasources> 

Because we have added a new JAR file to the lib directory, you will need to JBoss to make sure that the server is able to find the MySQL driver classes.

We’ll use the CMP roster application from Chapter 7, Container-Managed Persistence to test the new database connection. In order to use MySql in our application, we'll need to set the datasource name a nd type-mapping in the jbosscmp-jdbc.xml file in the dd/team directory of the CMP roster application. Edit the file and add the following datasource and datasource-mapping elements to the defaults element. to mySQL.

<jbosscmp-jdbc>
    <defaults>
        <datasource>java:/MySqlDS</datasource>
        <datasource-mapping>mySQL</datasource-mapping>
    </defaults>

    <enterprise-beans>
...
    </enterprise-beans>
</jbosscmp-jdbc>

After restarting JBoss, you should be able to deploy the application and see the tables being created as we did in Section 7.2, “Deploying and Running the Application”. The tables should be visible from the MySQL client.

mysql> show tables; 
+-----------------------------------+
| Tables_in_jboss                   |
+-----------------------------------+
| LeagueBean                        |
| PlayerBean                        |
| PlayerBean_teams_TeamBean_players |
| TeamBean                          |
+-----------------------------------+
4 rows in set (0.00 sec) 

You can see the JMS persistence tables in there too since we’re using MySQL as the default datasource.

If you look in the jboss-service.xml file in the default/conf directory, you’ll find the following service MBean.

<!-- The configurable Xid factory. For use with Oracle, set pad to true --> 
<mbean code="org.jboss.tm.XidFactory" 
       name="jboss:service=XidFactory"> 
    <!--attribute name="Pad">true</attribute--> 
</mbean> 

The transaction service uses this to create XA transactions identifiers. The comment explains the situation: for use with Oracle you have to include the line which sets the attribute Pad to true. This activates padding the identifiers out to their maximum length of 64 bytes. Remember that you’ll have to restart JBoss for this change to be put into effect, but wait until you’ve installed the JDBC driver classes which we’ll talk about next.

The Oracle JDBC drivers can be found in the directory $ORACLE_HOME/jdbc/lib. Older versions, which may be more familiar to some users, had rather uninformative names like classes12.zip but at the time of writing the latest driver version can be found in the file ojdbc14.jar. There is also a debug version of the classes with _g appended to the name which may be useful if you run into problems. Again, you should copy one of these to the lib directory of the JBoss default configuration. The basic driver class you would use for the non-XA setup is called oracle.jdbc.driver.OracleDriver. The XADataSource class, which we’ll use here, is called oracle.jdbc.xa.client.OracleXADataSource.

For the configuration file, make a copy of the oracle-xa-ds.xml example file and edit it to set the correct URL, username and password.

<datasources> 
    <xa-datasource> 
        <jndi-name>XAOracleDS</jndi-name> 
        <track-connection-by-tx>true</track-connection-by-tx> 
        <isSameRM-override-value>false</isSameRM-override-value> 
        <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class> 
        <xa-datasource-property name="URL">
            jdbc:oracle:thin:@monkeymachine:1521:jboss 
        </xa-datasource-property> 
        <xa-datasource-property name="User">jboss</xa-datasource-property> 
        <xa-datasource-property name="Password">password</xa-datasource-property> 
        <exception-sorter-class-name>
            org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter 
        </exception-sorter-class-name> 
        <no-tx-separate-pools/> 
    </xa-datasource> 
 
    <mbean code="org.jboss.resource.adapter.jdbc.xa.oracle.OracleXAExceptionFormatter" 
name="jboss.jca:service=OracleXAExceptionFormatter"> 
        <depends optional-attribute-name="TransactionManagerService">
            jboss:service=TransactionManager 
        </depends> 
    </mbean> 
</datasources>

We’ve used the oracle thin (pure java) driver here and assumed the database is running on the host monkeymachine and that the database name (or SID in Oracle terminology) is jboss. We’ve also assumed that you’ve created a user jboss with all the sufficient privileges. You can just use dba privileges for this example.

SQL> connect / as sysdba 
Connected. 
SQL> create user jboss identified by password; 
User created. 
SQL> grant dba to jboss; 
Grant succeeded. 

Now copy the file to the deploy directory. You should get the following output.

11:33:45,174 INFO  [WrapperDataSourceService] Bound connection factory for resource adapter
for ConnectionManager 'jboss.jca:name=XAOracleDS,service=DataSourceBinding to JNDI name
'java:XAOracleDS'

If you use the JNDIView service from the JMX console as before, you should see the name java:/XAOracleDS listed.

Again we’ll use the CMP example to test out the new database connection. The jbosscmp-jdbc.xml file should contain the following.

<jbosscmp-jdbc> 
    <defaults> 
        <datasource>java:/XAOracleDS</datasource> 
        <datasource-mapping>Oracle9i</datasource-mapping> 
    </defaults> 
</jbosscmp-jdbc> 

There are other Oracle type-mappings available too. If you’re using an earlier version, have a look in the conf/standardjbosscmp-jdbc.xml file to find the correct name

Deploy the application as before, check the output for errors and then check that the tables have been created using Oracle SQLPlus again from the command line.

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
TEAMBEAN
LEAGUEBEAN
PLAYERBEAN
PLAYERBEAN_TEAMS_TEAM_1OFLZV8