Creating a Datasource | JBoss: A Developers Notebook

With a database running and the drivers loaded, the application could connect directly to the database and issue queries if we wanted it to. But in a J2EE world we don't need to do that. JBoss, like all J2EE application servers, can manage database connections for us, allowing us to write our applications free of the need to configure connections or maintain connection pools ourselves.

Note: Datasources are shared through JNDI.

Even though we haven't specified the datasource for the ToDo application, JBoss has connected us to a default datasource called, conveniently enough, DefaultDS. It connects to our embedded Hypersonic database. Now we want to create a new datasource definition that will connect to the MySQL database we created in the first lab.

How do I do that?

In JBoss, a datasource is a deployable entity just like our application WAR and EAR files. It is represented by a special *-ds.xml file in the deploy directory. The Hypersonic datasource is configured in the hsqldb-ds.xml file. We'll look at that as an example. Here's an abbreviated version of it:

     <?xml version="1.0" encoding="UTF-8"?>     <datasources>        <local-tx-datasource>           <jndi-name>DefaultDS</jndi-name>           <connection-url>               jdbc:hsqldb:${jboss.server.data.dir}${/}hypersonic${/}localDB           </connection-url>           <driver-class>org.hsqldb.jdbcDriver</driver-class>           <user-name>sa</user-name>           <password></password>           <min-pool-size>5</min-pool-size>           <max-pool-size>20</max-pool-size>           <idle-timeout-minutes>0</idle-timeout-minutes>           <track-statements/>           <security-domain>HsqlDbRealm</security-domain>           <metadata>               <type-mapping>Hypersonic SQL</type-mapping>           </metadata>           <depends>jboss:service=Hypersonic,database=localDB</depends>        </local-tx-datasource>     </datasources>

Note: External clients can't look up a datasource and use it.

While there is a bit of noise in the file, it's pretty easy to guess what is going on. This datasource is named DefaultDS. It's bound under the JNDI name java:DefaultDS, meaning that it, like everything in the java: context, is only available inside the JVM. You can also see the connection URL and the database username and password, as well as a few other minor connection pool details.

It's really a simple format, and it would be pretty easy to duplicate this file, adding the connection details for our MySQL database. But it's even easier than that. The docs/examples/jca directory (under the root of your JBoss installation, not the server configuration directory you are working in) contains sample templates for the most popular database servers (and most of the less popular ones too).

The MySQL template is mysql-ds.xml:

     <?xml version="1.0" encoding="UTF-8"?>     <!-- $Id: ch04.xml,v 1.4 2005/07/21 19:40:42 ellie Exp $ -->     <!--  Datasource config for MySQL using 3.0.9 available from:     http://www.mysql.com/downloads/api-jdbc-stable.html     -->     <datasources>       <local-tx-datasource>         <jndi-name>MySqlDS</jndi-name>         <connection-url>jdbc:mysql://mysql-hostname:3306/jbossdb         </connection-url>         <driver-class>com.mysql.jdbc.Driver</driver-class>         <user-name>x</user-name>         <password>y</password>           <!-- corresponding type-mapping in the                standardjbosscmp-jdbc.xml (optional) -->           <metadata>              <type-mapping>mySQL</type-mapping>           </metadata>       </local-tx-datasource>     </datasources>

To get started, you need to copy mysql-ds.xml to the deploy directory and change the items in bold to the correct values for the server. The MySQL JNDI name is fine, so we'll start with connection-url. We'll assume that our database is located on the same machine as our JBoss instance, so we'll use localhost as the machine name.

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

The only other step is to tell JBoss the database user to connect as. We used the user todoapp, so we'll specify that, and the password, here:

     <user-name>todoapp</user-name>     <password>secretpassword</password>

What just happened?

You've now deployed a datasource in JBoss. You can look to the console log to make sure everything went right. If all went well, you'll see a line that looks like the following:

     20:09:23,756 INFO [WrapperDataSourceService] Bound connection factory for resource     adapter for ConnectionManager 'jboss.jca name=MySqlDS,service=DataSourceBinding to     JNDI name 'java:MySqlDS'

Note: You should profile your application to determine the optimal connection pool size.

If you've edited and saved the file a few times, the console should show that you've redeployed it several times. We'll make one more change, explicitly specifying the datasource connection pool size, and watch it redeploy.

Since we don't expect much traffic, we'll ask JBoss to keep just two connections ready in the pool, and to keep a maximum of ten connections open. To do that, add the following min-pool-size and max-pool-size elements to your mysql-ds.xml file:

     <?xml version="1.0" encoding="UTF-8"?>     <!-- $Id: ch04.xml,v 1.4 2005/07/21 19:40:42 ellie Exp $ -->     <!--  Datasource config for MySQL using 3.0.9 available from:     http://www.mysql.com/downloads/api-jdbc-stable.html     -->     <datasources>         <local-tx-datasource>             <jndi-name>MySqlDS</jndi-name>             <connection-url>jdbc:mysql://mysql-hostname:3306/jbossdb             </connection-url>             <driver-class>com.mysql.jdbc.Driver</driver-class>             <user-name>todoapp</user-name>             <password>secretpassword</password>             <min-pool-size>2</min-pool-size>             <max-pool-size>10</max-pool-size>             <!-- corresponding type-mapping in the                  standardjbosscmp-jdbc.xml (optional) -->             <metadata>                 <type-mapping>mySQL</type-mapping>             </metadata>         </local-tx-datasource>     </datasources>

When you save the file you'll see that the datasource successfully redeployed in the console log. This is all the feedback JBoss gives you directly. In the "Monitoring the Connection Pool" lab later in this chapter, we'll see how to verify our pool size and check on other aspects of the connection pool, such as how many connections are in use. For a full set of configuration options, see jboss-ds_1_5.dtd in the docs/dtd directory. Other template datasource descriptors are located in docs/examples/jca.

What about...

...making our datasource be DefaultDS?

That is possible. If we removed hsql-ds.xml and changed our datasource JNDI name to DefaultDS, JBoss would use our MySQL database as the default for all applications. However, keep in mind that internal services such as JMS use DefaultDS. While they can be easily configured to use other databases, and indeed they should be if we are deploying a production server using those services, it's generally better to be explicit about which datasource you are using.

Note: XA datasources are a real pain. Just because your database and driver claim to support XA transactions doesn't mean it really works. If you plan to use XA transactions, make sure to test them thoroughly.

And what about XA datasources?

You might be wondering what the local-tx-datasource element means in the datasource file. It means that the datasource only supports local transactions and can't be combined with other resources in a true distributed (XA) transaction. If your database supports XA (MySQL doesn't), you can change the local-tx-datasource tag to xa-datasource. Examples of XA datasource configurations are available in docs/examples/jca and are named *-xa-ds.xml.