Chapter 4. Databases and JBoss

Up to this point, this book has focused on the web tier. Now let’s look at the persistence tier. This is where the application data is stored for the long term—for example, between server restarts.

Why use the phrase “persistence tier” instead of simply calling it the “database tier”? We certainly recognize that the probability of information ending up in a database approaches is somewhere close to 100%. J2EE pundits love pointing out that data could be stored in any number of manners—as flat files, XML, and even web services to remote servers. These types of storage are mentioned as alternatives, but we have yet to work on an application where they completely usurp the trusty database.

Instead, most modern persistence technologies deal with transforming relational database information into Java objects. These Object/Relational Mappers (ORMs) come in many flavors—commercial and open source—but make the same promise: to free the Java developer from the perils of converting ResultSets to ArrayLists of DTOs.

We continue to use the phrase “persistence tier” to remind us that many supporting services surround the inevitable database.

Persistence Options

You should acknowledge one simple fact up front: if you deal with a relational database, all roads in one form or another lead to JDBC. Whether you write the code yourself or let an ORM write it for you, SQL INSERTs, UPDATEs, and DELETEs are the lingua franca of any database-driven application.

While Sun maintains that JDBC is not an acronym, it looks suspiciously like “Java DataBase Connectivity” to many seasoned programmers. It is the API that allows us to load up database Drivers, make Connections, and create Statements that yield ResultSets upon execution.

While nothing is intrinsically wrong with ResultSets, OO purists bristle at the thought of dealing with a semi-structured collection of strings and primitives. Java programmers are taught from a tender young age that JavaBeans and DTOs are the one true way to represent business objects. So to get from ResultSets to DTOs, we must use hand-code methods that do the transformation for us, one car.setName(resultSet.getString("name")) at a time.

While this isn’t terribly difficult, it does get tedious as the number of business objects and database tables grow. Maintaining two separate data schemas, one in Java and the other in SQL, strikes many as a flagrant violation of the DRY principle. The phrase “impedance mismatch” often comes up in JDBC discussions.

One potential way to avoid the problem of marshalling and unmarshalling JavaBeans is to remove the root cause—why not just create a database that deals natively with objects? On paper, object-oriented databases (OODBMS) seem to be the ideal solution to this problem. Sadly, OODBMSes have never gained any serious market share.

If you can’t change the root data source—and relational databases are deeply entrenched in most long-term persistence strategies—your only other option is to come up with an API that manages the impedance mismatch: something that allows you to deal with native JavaBeans, and not only hides the JDBC complexity from you, but ideally entirely creates and manages the infrastructure.

One of the earliest attempts at this was the now infamous Entity Bean offering in the EJB specification. Entity beans came in two basic variations: Bean-Managed Persistence (BMPs) and Container-Managed Persistence (CMPs).

BMPs were really nothing more that a fancy way of saying, “I’m going to keep on doing the JDBC wrangling that I’ve already been doing.” Since the Bean was responsible for its own persistence implementation, many programmers fell back on what they knew best—car.setName(resultSet.getString("name")).

CMPs were closer to what we were hoping to achieve—“let me define the business object and then have the container worry about how to persist it.” The problem with CMPs ended up being twofold:

  • Rather than dealing with a simple POJO, you were forced to create and maintain a complicated variety of interdependent classes and interfaces—Remotes, RemoteHomes, Locals, LocalHomes, and abstract bean classes.

  • The resulting tangle of code was tightly coupled to the container and very intrusive—you were forced to inherit from EJBObject and implement specific interfaces rather than following an inheritance tree that more closely modeled your business domain.

While Entity Beans still exist in the EJB specification today, they have largely fallen out of favor in the developer community.

Sun’s next attempt at a JavaBean-centric persistence API was Java Data Objects(JDO). The 1.0 specification has been out for several years, but it hasn’t captured a lot of mindshare. Some point to a differently but equally complicated API as its main problem. Traditional RDBMS vendors have been slow to support it, although OODBMS vendors have enthusiastically touted it as the Next Big Thing. Regardless, JDO is not an official part of the J2EE specification, so it has gone largely unnoticed by the server-side crowd.

Which leads us to the wild west of independent ORMs. Many solutions—both commercial and open source—have popped up in the absence of an official specification from Sun. All allow you to traffic in unencumbered POJOs—you don’t have to inherit from a specific object or implement a specific interface. Some use runtime reflection, and others rely on post-compilation bytecode manipulation to achieve their unobtrusive persistence goals.

JBoss Hibernate is one of the most popular of the bunch, although there are at least half a dozen viable candidates in this category. After we outline a JDBC strategy in this chapter, we’ll walk through a simple Hibernate refactoring in the next chapter.

The existence of so many competing persistence solutions demonstrates that this is a complex problem with no one right answer. Any solution you pick will certainly outshine the others in certain circumstances and leave you wanting in others.

Apart from the obvious JBoss tie-in, there is one compelling reason why we chose Hibernate as our second persistence strategy, over any of the others we mentioned. Quite simply, it seems to best represent what next generation persistence APIs will look like.

In 2005, Sun announced the merger of the EJB 3.0 and JDO 2.0 specification teams. Both were working toward—you guessed it—JavaBean-centric persistence APIs. Sun also invited the lead architects from the Hibernate project to sit on the team. Whatever the final name of the specification turns out to be, one thing is certain—it will look and feel like Hibernate or any of the many other ORMs on the market today. By investing a little time in learning an ORM today, you will be that much closer to understanding the official Sun specification when it is released in the future.

But before you can really appreciate what an ORM brings to the table, let’s look at a how to solve the persistence problem using nothing but JDBC.


JDBC has been around nearly as long as Java itself. The JDBC 1.0 API was released with JDK 1.1. This is the java.sql package. JDBC 2.0 was released with JDK 1.2. It included both the Core package and what was called the Optional Package (javax.sql). The optional package brought with it better enterprise support for database connections, including connection pools and distributed transactions. JDBC 3.0 is the latest release, included with JDK 1.4.

If you’ve written JDBC code since the good old days, you’re probably familiar with using the DriverManager to get a database connection, as in Example 4-1.

Example 4-1. Example of the JDBC DriverManager
    static final String DB_DRIVER_CLASS = "com.mysql.jdbc.Driver";
    static final String DB_URL =

    Connection connection = null;

    try {
        // Load the Driver.

        // Connect to the database.
        connection = DriverManager.getConnection(DB_URL);

    } catch (SQLException se) {
    } catch (...) {

While this code certainly works, it has several shortcomings:

  • Every time you connect and disconnect from the database, you incur the overhead of creating and destroying a physical database connection.

  • You have to manage the database transaction yourself.

  • You have a local transaction that’s concerned only with database activity. What if you deal with other resources such as JMS Destinations (Queues and Topics)? If there’s a problem and you need to roll back database updates, there’s no automated way to roll back the work done with these other resources.

One of the main benefits of living in an application server is having the server take care of these sorts of plumbing issues. JBoss, like all other J2EE application servers, deals with the issues listed above on your behalf. However, to facilitate this, we need to slightly change the way you obtain your database connections.

Rather than using a java.sql.DriverManager, we need to use a javax.sql.DataSource to allow JBoss to manage the details in Example 4-2.

Example 4-2. Example of the JDBC DataSource
        static final String DATA_SOURCE=

 dataSource = null;
        Connection conn = null;

        try {
            // Load the Driver.
            dataSource = ServiceLocator.getDataSource(DATA_SOURCE);

            // Connect to the database.
            conn = dataSource.getConnection();

        } catch (SQLException se) {
        } catch (ServiceLocatorException sle) {

A DataSource provides the following advantages:

  • When you obtain a database connection using a DataSource, you’re not creating a new connection. At startup, JBoss creates a database Connection Pool managed by a DataSource. When you get a database connection from a DataSource, you access an already existing connection from the pool. When you “close” the connection, you just return it to the pool so someone else can use it.

  • When you use a Container-Managed DataSource, all database access for a particular Transaction Context commits or rolls back automatically. You don’t have to manage the transaction yourself anymore.

  • If you use Container-Managed Transactions (CMT) and your DBMS supports two-phase commit (the XA protocol), then your database transaction can participate in a global transaction. Suppose you have a unit of work that requires database activity and sends JMS messages: if something goes wrong, the JBoss Transaction Manager rolls back everything.

OK, we admit it. We pulled a bit of a fast one on you. Using DataSources brings great power to the table, but it also brings along some added complexity. We should look at a few more moving parts in greater detail.


Let’s take a moment to parse the DataSource name java:comp/env/jdbc/JBossAtWorkDS, which is a Java Naming and Directory Interface (JNDI) name. JNDI provides access to a variety of back-end resources in a unified way.

JNDI is to Java Enterprise applications what Domain Name Service (DNS) is to Internet applications. Without DNS, you would be forced to memorize and type IP addresses like into your web browser instead of friendly names like In addition to resolving host names to IP addresses, DNS facilitates sending email between domains, load-balancing web servers, and other things. Similarly, JNDI maps high-level names to resources like database connections, JavaMail sessions, and pools of EJB objects.

DNS has a naming convention that makes it easy to figure out the organizational structure of a Fully Qualified Domain Name (FQDN). Domain names are dot-delimited and move from the general to the specific as you read them from right-to-left. “com” is a Top-Level Domain (TLD) reserved for commercial businesses. There are a number of other TLDs, including “edu” for educational institutions, “gov” for government entities, and “org” for non-profit organizations.

The domain name reserved for your business or organization is called a Mid-Level Domain (MLD).,, and are all MLDs. You can create any number of subdomains under a MLD, but the left-most element will always be a HostName like “www” or “mail.”

Now looking at a domain name like or for a listing of state parks in Colorado or Nebraska begins to make a little more sense. The country/state/department hierarchy in the domain name mirrors the real-life organizational hierarchy.

JNDI organizes its namespace using a naming convention called Environmental Naming Context (ENC). You are not required to use this naming convention, but it is highly recommended. ENC JNDI names always begin with java:comp/env. (Notice that JNDI names are forward slash-delimited instead of dot-delimited and read left-to-right.)

A number of TLD-like top-level names are in the ENC. Each JNDI “TLD” corresponds to a specific resource type, shown in Table 4-1.

Table 4-1. J2EE-style JNDI ENC naming conventions

Resource type

JNDI prefix

Environment Variables




JavaMail Sessions


JMS Connection Factories and Destinations


EJB Homes


JDBC DataSources


I’m obviously mixing my JNDI and DNS nomenclature, but the JNDI “TLD” for DataSources always should be java:/comp/env/jdbc. In the example DataSource name—java:comp/env/jdbc/JBossAtWorkDS—the “TLD” and “MLD” should be more self-evident now. JBossAtWorkDS is the JNDI “MLD.”

DNS names protect us from the perils of hardcoded IP addresses. A change of server or ISP (and the corresponding change in IP address) should remain transparent to the casual end user since their handle to your site is unchanged. Similarly, JNDI gives J2EE components a handle to back-end resources. Since the component uses an alias instead of an actual value (for the database driver, for example) we now have the flexibility to swap out back-end resources without changing the source code.

These JNDI names are local to the EAR. If you deploy multiple EARs to the same JBoss instance, each EAR will get its own JNDI local context. This ensures that your JNDI names are available only to the EAR in which they are set.

In the spirit of encapsulation, we wrap all of the JNDI lookups in class called ServiceLocator. It allows us to constrain all of the JNDI semantics to a single class. Here’s what our ServiceLocator class looks like in Example 4-3.

Example 4-3.
package com.jbossatwork.util;

import javax.naming.*;
import javax.sql.*;

public class ServiceLocator {
    private ServiceLocator() {  }

    public static DataSource getDataSource(String dataSourceJndiName)
                  throws ServiceLocatorException {

        DataSource dataSource = null;
        try {
            Context ctx = new InitialContext();
            dataSource = (DataSource) ctx.lookup(dataSourceJndiName);

        } catch (ClassCastException cce) {
            throw new ServiceLocatorException(cce);
        } catch (NamingException ne) {
            throw new ServiceLocatorException(ne);
        return dataSource;

All JNDI variables are stored in the InitialContext. When you call the lookup() method, it returns an Object that must be cast to the appropriate type. If you think about it, this is really no different than calling HashMap.get("JBossAtWorkDS").

Now we can see how to get a DataSource by doing a JNDI lookup. But this probably brings up the next obvious question: how did our DataSource get into the InitialContext in the first place? To find out, we need to revisit your favorite deployment descriptor, web.xml.

JNDI References in web.xml

In previous chapters, we used the web.xml file to describe and deploy servlets. This same file describes and deploys JNDI resources. The new web.xml looks like Example 4-4.

Example 4-4. web.xml
<?xml version="1.0" encoding="UTF-8"?>

        "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"






Let’s examine each new element:

  • <res-ref-name> is the JNDI resource name. Notice that you don’t have to specify “java:comp/env/”—it is assumed, just like "http://" is commonly left out of web URLs.

  • <res-type> in our case is a DataSource. This must be the fully qualified classname.

  • <res-auth> can be either Container or Servlet. Since we use JBoss’ DataSource pooling, Container is the appropriate choice here.

OK, so here’s where it gets interesting. At first glance, it appears that JBoss doesn’t adhere to the ENC naming style when it comes to DataSources. Instead of java:comp/env/jdbc/JBossAtWorkDS, its DataSources are referenced as simply java:/JBossAtWorkDS. So we need a way to map the JBoss name to the ENC name.

The real reason for the mismatch is that JBoss creates a global binding for the DataSource, and we need to create a local reference to it. We mentioned earlier in the chapter that all JNDI references are local to the EAR. Out of courtesy, JBoss doesn’t automatically expose global references to us. We need to map the global name to a local name so that we can work with it.

Luckily, a straightforward way to do the cross mapping is available. You can include a JBoss specific deployment descriptor in your WAR named jboss-web.xml. Example 4-5 shows what ours should look like.

Example 4-5. jboss-web.xml
<?xml version="1.0" encoding="UTF-8"?>
        <jndi-name>java:/JBossAtWorkDS </jndi-name>

Since we’re already using XDoclet to generate our web.xml file, there is no reason not to continue letting it do its thing. Example 4-6 shows the new XDoclet code in ControllerServlet.

Example 4-6.
 * @web.servlet
 *    name="Controller"
 * @web.servlet-mapping
 *    url-pattern="/controller/*"
 * @web.resource-ref
 *    name="jdbc/JBossAtWorkDS"
 *    type="javax.sql.DataSource"
 *    auth="Container"
 * @jboss.resource-ref
 *    res-ref-name="jdbc/JBossAtWorkDS"
 *    jndi-name="java:/JBossAtWorkDS"

public class ControllerServlet extends HttpServlet

Chances are good that more than one servlet will end up using the same JNDI resource. While the servlet tags need to be defined in each servlet, the JNDI tags should be specified only once. It doesn’t matter which servlet you define them in, but you should come up with a strategy early in the development process for managing it. If you have a central Controller servlet like we do, it is usually a pretty logical candidate for this. (You can also have XDoclet include an XML fragment stored in a file instead of using JavaDoc comments. The choice is yours.)

To generate the jboss-web.xml file, we need to add a new XDoclet directive to our build process in Example 4-7—aptly named <jbosswebxml>.

Example 4-7. Adding the <jbosswebxml> directive
<!-- ====================================== -->
    <target name="generate-web" description="Generate web.xml">
        <taskdef name="webdoclet"
                 classpathref="xdoclet.lib.path" />

        <mkdir dir="${gen.source.dir}" />

        <webdoclet destdir="${gen.source.dir}">
            <fileset dir="${source.dir}">
                <include name="**/*" />

            <deploymentdescriptor destdir="${gen.source.dir}"
                                  servletspec="2.4" />

            <jbosswebxml destdir="${gen.source.dir}" />


We’ll also need to change your <war> task in Example 4-8 to include the newly generated JBoss-specific deployment descriptor.

Example 4-8. Including jboss-web.xml in the WAR
<!-- ====================================== -->
    <target name="war" depends="generate-web,compile"
             description="Packages the Web files into a WAR file">
        <mkdir dir="${distribution.dir}" />

        <war destFile="${distribution.dir}/${}"
            <!-- files to be included in / -->
            <fileset dir="${web.dir}"  exclude="WEB-INF/web.xml" />

            <!-- files to be included in /WEB-INF/classes -->
            <classes dir="${classes.dir}" />

            <!-- files to be included in /WEB-INF/lib -->
            <lib dir="${lib.dir}" />

            <!-- files to be included in /WEB-INF -->
            <webinf dir="${web.inf.dir}" excludes="web.xml" />

            <webinf dir="${gen.source.dir}" >
                    <include name="jboss-web.xml" />


OK, so now we know that the JNDI resources are defined in the deployment descriptors . But where do we configure the DataSource itself?

JBoss DataSource Descriptors

Remember in Chapter 1 we dynamically deployed and undeployed a service? We used the Hypersonic database in the example. You can access any database as an MBean by simply including the appropriate *-ds.xml file in the deploy directory.

Hypersonic is completely implemented in Java and ships standard with JBoss. It is great for playing around with JDBC and not having to worry about installing and configuring an external database. We generally rely on a full-fledged external database for production applications, but we’d be lying if we told you that we didn’t use Hypersonic all the time for rapid testing and prototyping.

Three types of Hypersonic instances include:

  • The default Hypersonic configuration, which gives you a local database whose modifications are saved to disk (and therefore survive between JBoss restarts). We can access this configuration only through a DataSource—it is not accessible to out-of-container clients like Ant or third-party standalone GUIs. It is called an “In-Process Persistent DB”.

  • As a slight variation, we can configure the “In-Process Persistent DB” to run purely in memory. No files are written to disk, and therefore the database lives only as long as the container is running. This is called an “In-Memory DB.”

  • If you need to access the database from either a DataSource or an external client, you can configure Hypersonic to listen on a TCP port (1701 by default). This is called a “TCP DB.”

The Hypersonic deployment descriptor is $JBOSS_HOME/server/default/deploy/hsqldb-ds.xml. Examples of deployment descriptors for all major databases (commercial or open source) are at $JBOSS_HOME/docs/examples/jca. The J2EE Connector Architecture (JCA) is a standard way for a J2EE container to connect to external datastores. These example files generally are very well commented. Take a moment to browse the examples/jca directory and look through some of the deployment descriptors.

We provide two customized Hypersonic database descriptors in the ch04/sql directory. Jaw-ds.xml strips out all the comments included in the original hsqldb-ds.xml file—sometimes it can be hard to see the forest for the trees. We also included a version that retains the original comments. You might like to compare this version to the default Hypersonic version to see how we’ve tweaked it.

Let’s step through jaw-ds.xml line by line.



This is the global/JBoss JNDI name of your DataSource. Since this DataSource is accessible to all EARs, it only makes sense to bind its name in the global context. (The local ENC name goes with the local EAR in web.xml.)


These values should look familiar to you. They are the standard JDBC parameters that tell you how to connect to the database, which driver to use, and what credentials to supply when connecting.


These next settings allow you to optimize the start and peak number of connections in the pool. According to the comments in the default Hypersonic descriptor, <idle-timeout-minutes> should be left at 0 as a bug work-around.

         <type-mapping>Hypersonic SQL</type-mapping>

The <metadata> element is boilerplate for all Hypersonic instances, but the <depends> clause should be customized per instance. This is the unique identifier of the MBean defined in the last section of the file.

   <mbean code="org.jboss.jdbc.HypersonicDatabase"
     <attribute name="Port">1701</attribute>
     <attribute name="Silent">true</attribute>
     <attribute name="Database">jawdb</attribute>
     <attribute name="Trace">false</attribute>
     <attribute name="No_system_exit">true</attribute>


The <local-tx-datasource> section defines the DataSource. The <mbean> section defines the actual database instance. The Database attribute is especially interesting—it tells Hypersonic what to name the physical files stored on disk. (These files are stored in $JBOSS_HOME/server/default/data/hypersonic, but they won’t show up until you deploy the database. We’ll see them in just a moment.)

Now we are ready to deploy the customized JAW datasource. Copy jaw-ds.xml to $JBOSS_HOME/server/default/deploy. You should see the following code in your server console window:

23:06:52,077 INFO  [STDOUT] [Server@d27151]: [Thread[hypersonic-jawdb,5,jboss]]:
 checkRunning(false) entered
23:06:52,079 INFO  [STDOUT] [Server@d27151]: [Thread[hypersonic-jawdb,5,jboss]]:
 checkRunning(false) exited
23:06:52,080 INFO  [STDOUT] [Server@d27151]: Startup sequence initiated from main()
23:06:52,119 INFO  [STDOUT] [Server@d27151]: Loaded properties from
23:06:52,155 INFO  [STDOUT] [Server@d27151]: Initiating startup sequence...
23:06:52,158 INFO  [STDOUT] [Server@d27151]: Server socket opened successfully in
 0 ms.
23:06:52,179 INFO  [STDOUT] [Server@d27151]: Database [index=0, id=2,
 db=file:/Library/jboss-4.0.1/server/default/data/hypersonic/jawdb, alias=]
 opened sucessfully in 18 ms.
23:06:52,181 INFO  [STDOUT] [Server@d27151]: Startup sequence completed in 23 ms.
23:06:52,263 INFO  [STDOUT] [Server@d27151]: 2005-04-28 23:06:52.263 HSQLDB server
 1.7.2 is online
23:06:52,288 INFO  [STDOUT] [Server@d27151]: To close normally, connect and execute
23:06:52,309 INFO  [STDOUT] [Server@d27151]: From command line, use [Ctrl]+[C] to
 abort abruptly
23:06:52,569 INFO  [WrapperDataSourceService] Bound connection factory for resource
 adapter for ConnectionManager 'jboss.jca:name=JBossAtWorkDS,
 service=DataSourceBinding to JNDI name 'java:JBossAtWorkDS'

We can glean a couple of interesting nuggets from the console output:

  • First, it tells us that the database is now listening on a TCP port (or server socket).

  • It also tells us where to look for the physical database files: $JBOSS_HOME/server/default/data/hypersonic. After we add some data to this database, we’ll nose around this directory to see the resulting changes.

  • Finally, it tells us that our DataSource has been successfully bound to a JNDI name.

We now know that our database has been successfully deployed. We can use a couple of other tricks to confirm this if you’d like.

You can verify the ports that are open on your server by using the netstat command. Type netstat -an on a Windows PC or Mac; or netstat -anp on a Linux box. All platforms should give you a report similar to this:

Active Internet connections (including servers)
Proto Recv-Q Send-Q  Local Address          Foreign Address        (state)
tcp46      0      0  *.1701                 *.*                    LISTEN
tcp4       0      0          CLOSE_WAIT
tcp4       0      0          CLOSE_WAIT
tcp46      0      0  *.8008                 *.*                    LISTEN
tcp46      0      0  *.8093                 *.*                    LISTEN
tcp46      0      0  *.8088                 *.*                    LISTEN
tcp46      0      0  *.4445                 *.*                    LISTEN
tcp46      0      0  *.4444                 *.*                    LISTEN
tcp46      0      0  *.8083                 *.*                    LISTEN
tcp46      0      0  *.1099                 *.*                    LISTEN
tcp46      0      0  *.1098                 *.*                    LISTEN
tcp4       0      0       CLOSE_WAIT
tcp4       0      0         *.*                    LISTEN
tcp46      0      0  *.8009                 *.*                    LISTEN
tcp46      0      0  *.8080                 *.*                    LISTEN

In addition to our Hypersonic instance on port 1701, we can also see our embedded Tomcat instance listening on port 8080.

Since Hypersonic is an MBean, you can also use JBoss’ JMX-Console webapp to verify that it is active. Visit http://localhost:8080/jmx-console (Figure 4-1). The Hypersonic-JAW MBean should be one of the first links in the list.

Click on the link to our database instance. From here, you can do basic things like start and stop the instance, or modify the port it is listening on. This is nothing you can’t also do by hand-editing the configuration files, but some people prefer a GUI like that in Figure 4-2.

Now our database is configured and ready to be tested. To hit it, we’ll need to make sure that your application can find the appropriate JDBC driver.

JDBC Driver JARs

A DataSource is a container-managed resource. The JBoss documentation recommends storing the JAR outside of your EAR and in $JBOSS_HOME/server/default/lib. (One big reason for this is that JDBC drivers cannot be hot deployed.) For example, the $JBOSS_HOME/server/default/lib directory is where you’ll find hsqldb.jar—the JDBC driver for the Hypersonic database. As an added benefit, if you store the drivers here, you can share them across multiple EARs. With less duplication, there is less of a chance for mismatched drivers and database versions.

The JBoss JMX-Console
Figure 4-1. The JBoss JMX-Console
MBean configuration
Figure 4-2. MBean configuration

Of course, if you are not going to be hot deploying your EARs you can include your JDBC drivers in your EAR. This gives you the added benefit of allowing each EAR to use potentially different or conflicting versions of the same JDBC driver.

Database Checklist

OK, so here’s the checklist of things we’ve accomplished so far:

  • Stored the JDBC driver in $JBOSS_HOME/server/default/lib (hsqldb.jar)

  • Configured the database deployment descriptor in $JBOSS_HOME/server/default/deploy (hsqldb-ds.xml) Among other things, this is where we set up the JBoss JNDI name (java:/JBossAtWorkDS).

  • We created a global JNDI reference to the DataSource in jboss-web.xml. This name matches the name in the database deployment descriptor. We also provided a setting that maps the global JNDI name to a local JNDI name using ENC-style naming (java:comp/env/jdbc/JBossAtWorkDS).

  • We created a local JNDI reference to the DataStore in web.xml..

  • We created a ServiceLocator class that encapsulates our JNDI lookup and returns the DataSource.

Because of the way we’ve set things up, switching databases at this point is relatively easy. For example, if you’d prefer to work against an instance of MySQL, we only need to copy the JDBC drivers to the $JBOSS_HOME/server/default/lib directory and copy a new database deployment descriptor into the deploy directory. If you use the same JNDI name that we already used, your job is done—all the code upstream will be configured and ready to hit the new database.

We’ve said it many times before, but it’s worth saying again: Hypersonic is a great database for our immediate purposes because it doesn’t require configuring an external resource. However, in a production environment, we’d most likely use a more robust database.

We are now ready to create a Car table and insert some sample data.

Accessing the Database Using Ant

Now that we’ve created the JBossAtWorkDB database instance, we need to create and populate the Car table. Ant has a <sql> task that is ideal for this sort of thing. Keeping these commands in a script allows you to rebuild your database easily and often during the development phase.

The same rules for scripting the deployment of your EAR to a production server apply here as well: Just Say NO! If you create a script that points to a production database, you are only asking for it to be run inadvertently with disastrous results. With great power comes great responsibility—use it wisely.

That said, let’s look at the build.xml file in the new SQL subproject, shown in Example 4-9. This project doesn’t contain any compiled code. It is just a convenient storage location for these SQL scripts.

Example 4-9. SQL subproject build.xml
<?xml version="1.0"?>

<project name="sql" default="init" basedir=".">

    <!-- Initialization variables -->
    <property name="database.driver.dir"
    <property name="database.driver.jar" value="hsqldb.jar"/>

    <path id="sql.classpath">
        <fileset dir="${database.driver.dir}">
            <include name="${database.driver.jar}"/>

    <!-- ====================================== -->
    <target name="init"
            description="Creates test data in the database.">

        <sql driver="org.hsqldb.jdbcDriver"


            CREATE TABLE CAR (
                ID BIGINT identity,
                MAKE VARCHAR(50),
                MODEL VARCHAR(50),
                MODEL_YEAR VARCHAR(50)

             VALUES (99, 'Toyota', 'Camry', '2005');

             VALUES (100, 'Toyota', 'Corolla', '1999');

             VALUES (101, 'Ford', 'Explorer', '2005');

             SELECT * FROM CAR;


We provide a classpath to your database driver and set up the connection string. From there, it is straight SQL. Run the script: you should see something like Example 4-10 for console output.

Example 4-10. Ant SQL output
Buildfile: build.xml

      [sql] Executing commands
      [sql] 0 rows affected
      [sql] 0 rows affected
      [sql] 1 rows affected
      [sql] 1 rows affected
      [sql] 1 rows affected
      [sql] 99,Toyota,Camry,2005
      [sql] 100,Toyota,Corolla,1999
      [sql] 101,Ford,Explorer,2005

      [sql] 0 rows affected
      [sql] 6 of 6 SQL statements executed successfully

Total time: 3 seconds

If you really want to prove to yourself that this worked, look in $JBOSS_HOME/server/default/data/hypersonic one more time. If you open the jaw-db.log file in a text editor, you should see the following:

         MODEL_YEAR VARCHAR(50) )
INSERT INTO CAR VALUES(99,'Toyota','Camry','2005')
INSERT INTO CAR VALUES(100,'Toyota','Corolla','1999')
INSERT INTO CAR VALUES(101,'Ford','Explorer','2005')

At this point, the stage is set. We have a database table with data in it. All we need to do now is create a new DAO object that will read the information out of the table.

Creating JDBCCarDAO

Our first CarDAO was fine to get the project kick-started, but ArrayLists aren’t the best long-term persistence strategy. Let’s create a second DAO that takes advantage of the infrastructure we’ve just put in place.

Since two classes provide different implementations of the same functionality, we should create a common Interface. In addition to making it trivial to switch back and forth between the two concrete implementations, it will also pave the way for us to add a third DAO implementation for Hibernate in the next chapter.

In the ch04/common source tree, notice that we renamed our old DAO class to InMemoryCarDAO. We didn’t touch any of the methods, just the name of the class and the corresponding constructor names, as in Example 4-11.

Example 4-11.
package com.jbossatwork.dao;

import java.util.*;
import com.jbossatwork.dto.CarDTO;

public class InMemoryCarDAO implements CarDAO
    private List carList;

    public InMemoryCarDAO()
        carList = new ArrayList();

        carList.add(new CarDTO("Toyota", "Camry", "2005"));
        carList.add(new CarDTO("Toyota", "Corolla", "1999"));
        carList.add(new CarDTO("Ford", "Explorer", "2005"));

    public List findAll()
        return carList;

The CarDAO Interface simply defines the method signature for findAll():

package com.jbossatwork.dao;

import java.util.*;

public interface CarDAO
    public List findAll();

The new JDBCCarDAO uses the new DataSource and ServiceLocator class to build the ArrayList of CarDTOs in Example 4-12.

Example 4-12.
package com.jbossatwork.dao;

import java.util.*;
import java.sql.*;
import javax.sql.*;
import com.jbossatwork.dto.CarDTO;
import com.jbossatwork.util.*;

public class JDBCCarDAO implements CarDAO
    private List carList;
    private static final String DATA_SOURCE="java:comp/env/jdbc/JBossAtWorkDS";

    public JDBCCarDAO()
    {  }

    public List findAll()
        List carList = new ArrayList();
        DataSource dataSource = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

            dataSource = ServiceLocator.getDataSource(DATA_SOURCE);
            conn = dataSource.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from CAR");

                CarDTO car = new CarDTO();

        catch (Exception e)
                if(rs != null){rs.close();}
                if(stmt != null){stmt.close();}
                if(conn != null){conn.close();}
            catch(Exception e)

        return carList;

Finally let’s change ControllerServlet to instantiate the correct DAO. Notice that courtesy of the new interface we created, switching between implementations is as simple as changing the “new” side of the equation, as shown in Example 4-13.

Example 4-13.
// perform action
            CarDAO carDAO = new JDBCCarDAO();
            request.setAttribute("carList", carDAO.findAll());

            destinationPage = "/carList.jsp";

Now that everything is in place, let’s compile and deploy the EAR. Change to ch04/04a-datasource and type ant. Copy the jaw.ear file to $JBOSS_HOME/server/default/deploy and visit http://localhost:8080/jaw. (Alternately, you can use the deploy or colddeploy Ant targets.)

Looking Ahead...

Once again, we added hundreds of lines of new code with no visible difference to the application. Hopefully you can appreciate what is transparent to the end user—by layering your application correctly, you can make massive changes to the persistence tier while leaving your presentation tier virtually untouched.

We have one more iteration of the CarList example to get through before we move on to more exciting stuff. In the next chapter, we’ll create a Hibernate DAO that drastically simplifies the object-relational mapping that we have to do by hand in the JDBC DAO. After that, we’ll start adding some new functionality to the application, such as logging in and buying cars.

Get JBoss at Work: A Practical Guide now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.