Chapter 8. Using other Databases

In the previous chapters, we’ve just been using the JBoss default datasource in our applications. This is provided by the embedded HSQL database instance and is bound to the JNDI name java:/DefaultDS. Having a database included with JBoss is very convenient for running examples and HSQL is adequate for many purposes. However, at some stage you will want to use another database, either to replace the default datasource or to access multiple databases from within the server.

8.1. DataSource Configuration Files

DataSource configuration file names end with the suffix -ds.xml so that they will be recognized correctly by the JCA deployer. The docs/example/jca directory contains sample files for a wide selection of databases and it is a good idea to use one of these as a starting point. For a full description of the configuration format the best place to look is the DTD file docs/dtd/jboss-ds_1_5.dtd. Additional documentation on the files and the JBoss JCA implementation can also be found in the JBoss 4 Application Server Guide.

Local transaction datasources are configured using the local-tx-datasource element and XA-compliant ones using xa-tx-datasource. The example file generic-ds.xml shows how to use both types and also some of the other elements that are available for things like connection pool configuration. Examples of both local and XA configurations are available for Oracle, DB2 and Informix.

If you look at the example files firebird-ds.xml, facets-ds.xml and sap3-ds.xml, you’ll notice that they have a completely different format, with the root element being connection-factories rather than datasources. These use an alternative, more generic JCA configuration syntax used with a pre-packaged JCA resource adapter. The syntax is not specific to datasource configuration and is used, for example, in the jms-ds.xml file to configure the JMS resource adapter.

Next, we’ll work through some step-by-step examples to illustrate what’s involved setting up a datasource for a specific.

8.2. Using MySQL as the Default DataSource

MySQL is a one of the most popular open source databases around and is used by many prominent organizations from Yahoo to NASA. The official JDBC driver for it is called Connector/J. For this example we’ve used MySQL 4.1.7 and Connector/J 3.0.15. You can download them both from http://www.mysql.com .

8.2.1. Creating a Database and User

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) 

8.2.2. Installing the JDBC Driver and Deploying the DataSource

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.

8.2.3. Testing the MySQL DataSource

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.

8.3. Setting up an XADataSource with Oracle 9i

Oracle is one of the main players in the commercial database field and most readers will probably have come across it at some point. You can download it freely for non-commercial purposes from http://www.oracle.com

Installing and configuring Oracle is not for the faint of heart. It isn’t really just a simple database, but it is heavy on extra features and technologies which you may not actually want (another Apache web server, multiple JDKs, Orbs etc.) but which are usually installed anyway. So we’ll assume you already have an Oracle installation available. For this example, we’ve used Oracle 10g.

8.3.1. Padding Xid Values for Oracle Compatibility

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.

8.3.2. Installing the JDBC Driver and Deploying the DataSource

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.

8.3.3. Testing the Oracle DataSource

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