“Spring in Action. Second Edition (Part 2. Enterprise Spring)”

In part 1, you learned about Spring’s core container and its support for
dependency injection (DI) and aspect-oriented programming (AOP). With
that foundation set, part 2 will show you how to apply DI and AOP to imple-
ment business layer functionality for your application.

Most applications ultimately persist business information in a relational
database. Chapter 5, “Hitting the database,” will guide you in using Spring’s
support for data persistence. You’ll be introduced to Spring’s JDBC support,
which helps you remove much of the boilerplate code associated with JDBC.
You’ll also see how Spring integrates with several popular object-relational
mapping frameworks, such as Hibernate, JPA, and iBATIS.

Once you are persisting your data, you’ll want to ensure that its integrity
is preserved. In chapter 6, “Managing transactions,” you’ll learn how Spring
AOP can be used to declaratively apply transactional policies to your applica-
tion objects using AOP. You’ll see that Spring affords EJB-like transaction sup-
port to POJOs.

As security is an important aspect of many applications, chapter 7, “Secur-
ing Spring,” will show you how to use the Spring Security (formerly known as
Acegi Security) to protect the information your application contains.

In chapter 8, “Spring and POJO-based remote services,” you’ll learn how
to expose your application objects as remote services. You’ll also learn how
to transparently access remote services as though they are any other object
in your application. Remoting technologies explored will include RMI, Hes-
sian/Burlap, web services, and Spring’s own HTTP invoker.

Chapter 9, “Building contract-first web services in Spring,” approaches web ser-
vices from a different angle by showing how to use the Spring Web Services frame-
work to build contract-driven, document-centric web services.

Chapter 10, “Spring messaging,” explores a different approach to application
integration by showing how Spring can be used with JMS to asynchronously send
and receive messages between applications. You’ll also see how to develop mes-
sage-driven POJOs and build asynchronous remote services using Lingo.

Chapter 11, “Spring and Enterprise JavaBeans,” covers the connection
between Spring and EJBs. This includes how to wire EJBs in a Spring application
and how to Spring-enable EJB session beans. We’ll also take a quick look at how to
develop EJB 3-style beans in Spring.

Chapter 12, “Accessing enterprise services,” will wrap up the discussion of
Spring in the business layer by showcasing some of Spring’s support for common
enterprise services. In this chapter, you’ll learn how to use Spring to access objects
in JNDI, send emails, and schedule tasks.

5. Hitting the database

This chapter covers

Defining Spring’s data access support

Configuring database resources

Using Spring’s JDBC framework

Integrating with Hibernate, JPA, and iBATIS

Hitting the database

With the core of the Spring container now under your belt, it’s time to put it to
work in real applications. A perfect place to start is with a requirement of nearly
any enterprise application: persisting data. Every one of us has probably dealt
with database access in an application in the past. In doing so, you know that
data access has many pitfalls. We have to initialize our data access framework,
open connections, handle various exceptions, and close connections. If we get
any of this wrong, we could potentially corrupt or delete valuable company data.
In case you haven’t experienced the consequences of mishandled data access, it
is a Bad Thing.

Since we strive for Good Things, we turn to Spring. Spring comes with a family
of data access frameworks that integrate with a variety of data access technologies.
Whether you are persisting your data via direct JDBC, iBATIS, or an object-
relational mapping (ORM) framework like Hibernate, Spring removes the tedium
of data access from your persistence code. Instead, you can lean on Spring to han-
dle the low-level data access work for you so that you can turn your attention to
managing your application’s data.

In this chapter, we’re going to build the persistence layer of the RoadRantz
application (see figure 5.1). In this layer, we are faced with some choices. We
could use JDBC, Hibernate, the Java Persistence API (JPA), iBATIS, or any one of a
number of persistence frameworks. Fortunately for us, Spring supports all of
those persistence mechanisms.

Figure 5.1 Like most applications, RoadRantz persists and restores data from a relational database.
The persistence layer of the application is where all data access takes place.

As we build the persistence layer of the RoadRantz application, we’ll see how
Spring abstracts common data access functions, thus simplifying persistence code.
You’ll see how Spring makes working with JDBC, Hibernate, JPA, and iBATIS even
easier. And before we wrap up our discussion of data access, we’ll touch on how to
use Spring support for declarative caching to beef up the performance of your
application.

Regardless of which persistence technology you choose, simple JDBC or sophis-
ticated JPA, there’s a lot of common ground among all of Spring’s data access
frameworks. So, before we jump into Spring’s support for data access, let’s talk
about the basics of Spring’s DAO support.

5.1 Learning Spring’s data access philosophy

From the previous chapters, you know that one of Spring’s goals is to allow you
to develop applications following the sound object-oriented (OO) principle of
coding to interfaces. Spring’s data access support is no exception.

DAO stands for data access object, which perfectly describes a DAO’s role in an
application. DAOs exist to provide a means to read and write data to the database.
They should expose this functionality through an interface by which the rest of
the application will access them. Figure 5.2 shows the proper approach to design-
ing your data access tier.

As you can see, the service objects are accessing the DAOs through interfaces.
This has a couple of advantages. First, it makes your service objects easily testable
since they are not coupled to a specific data access implementation. In fact,
you could create mock implementations of these data access interfaces. That would
allow you to test your service object without ever having to connect to the data-
base, which would significantly speed up your unit tests and rule out the chance of
a test failure due to inconsistent data.

Figure 5.2
Service objects do not handle their own data access. Instead, they
delegate data access to DAOs. The DAO’s interface keeps it loosely
coupled to the service object.

In addition, the data access tier is accessed in a persistence technology–agnos-
tic manner. That is, the chosen persistence approach is isolated to the DAO while
only the relevant data access methods are exposed through the interface. This
makes for a flexible application design and allows the chosen persistence frame-
work to be swapped out with minimal impact to the rest of the application. If the
implementation details of the data access tier were to leak into other parts of the
application, the entire application would become coupled with the data access
tier, leading to a rigid application design.

NOTE
If after reading the last couple of paragraphs, you feel that I have a strong
bias toward hiding the persistence layer behind interfaces, then I’m
happy that I was able to get that point across. The fact is, I believe that
interfaces are key to writing loosely coupled code and that they should be
used at all layers of an application, not just at the data access layer. That
said, it’s also important to note that while Spring encourages the use of
interfaces, Spring does not require them—you’re welcome to use Spring
to wire a bean (DAO or otherwise) directly into a property of another
bean without an interface between them.

One way Spring helps you insulate your data access tier from the rest of your
application is by providing you with a consistent exception hierarchy that is used
across all of its DAO frameworks.

5.1.1 Getting to know Spring’s data access exception hierarchy

If you’ve ever written JDBC code (without Spring), you’re probably keenly familiar
with the fact that you can’t do anything with JDBC without being forced to catch
java.sql.SQLException. Some common problems that might cause an SQLEx-
ception to be thrown include:

The application is unable to connect to the database.

The query being performed has errors in its syntax.

The tables and/or columns referred to in the query do not exist.

An attempt is made to insert or update values that violate a database con-
straint.

The big question surrounding SQLException is how it should be handled when
it’s caught. As it turns out, many of the problems that trigger an SQLException
can’t be remedied within a catch block.

Most SQLExceptions that are thrown
indicate a fatal condition. If the application can’t connect to the database, that
usually means that the application will be unable to continue. Likewise, if there
are errors in the query, little can be done about it at runtime.

If there’s nothing that can be done to recover from an
SQLException, why are
we forced to catch it?

Even if you have a plan for dealing with some
SQLExceptions, you’ll have to
catch the SQLException and dig around in its properties for more information on
the nature of the problem. That’s because
SQLException is treated as a “one size
fits all” exception for problems related to data access. Rather than have a different
exception type for each possible problem,
SQLException is the exception that’s
thrown for all data access problems.

Some persistence frameworks offer a richer hierarchy of exceptions. Hiber-
nate, for example, offers almost two dozen different exceptions, each targeting a
specific data access problem. This makes it possible to write
catch blocks for the
exceptions that you want to deal with.

Even so, Hibernate’s exceptions are specific to Hibernate. As stated before,
we’d like to isolate the specifics of the persistence mechanism to the data access
layer. If Hibernate-specific exceptions are being thrown then the fact that we’re
dealing with Hibernate will leak into the rest of the application. Either that, or
you’ll be forced to catch persistence platform exceptions and rethrow them as
platform-agnostic exceptions.

On one hand, JDBC’s exception hierarchy is too generic—in fact, it’s not much
of a hierarchy at all. On the other hand, Hibernate’s exception hierarchy is pro-
prietary to Hibernate. What we need is a hierarchy of data access exceptions that
are descriptive but not directly associated with a specific persistence framework.
Spring’s persistence platform agnostic exceptions

Spring JDBC provides a hierarchy of data access exceptions that solve both prob-
lems. In contrast to JDBC, Spring provides several data access exceptions, each
descriptive of the problem that they’re thrown for. Table 5.1 shows some of
Spring’s data access exceptions lined up against the exceptions offered by JDBC.

As you can see, Spring has an exception for virtually anything that could go
wrong when reading or writing to a database. And the list of Spring’s data access
exceptions is vaster than shown in table 5.1. (I would’ve listed them all, but I
didn’t want JDBC to get an inferiority complex.)

Table 5.1 JDBC’s exception hierarchy versus Spring’s data access exceptions.

Even though Spring’s exception hierarchy is far more rich than JDBC’s simple
SQLException, it isn’t associated with any particular persistence solution. This
means that you can count on Spring to throw a consistent set of exceptions,
regardless of which persistence provider you choose. This helps to keep your per-
sistence choice confined to the data access layer.

Look, Ma! No catch blocks!

What isn’t evident from table 5.1 is that all of those exceptions are rooted with
DataAccessException. What makes DataAccessException special is that it is an
unchecked exception. In other words, you don’t have to catch any of the data
access exceptions thrown from Spring (although you’re perfectly welcome to if
you’d like).

DataAccessException is just one example of Spring’s across-the-board philos-
ophy of checked versus unchecked exceptions. Spring takes the stance that many
exceptions are the result of problems that can’t be addressed in a catch block.
Instead of forcing developers to write catch blocks (which are often left empty),
Spring promotes the use of unchecked exceptions. This leaves the decision of
whether to catch an exception in the developer’s hands.

To take advantage of Spring’s data access exceptions, you must use one of
Spring’s supported data access templates. Let’s look at how Spring templates can
greatly simplify data access.

5.1.2 Templating data access

You have probably traveled by plane before. If so, you will surely agree that one of
the most important parts of traveling is getting your luggage from point A to
point B. There are many steps to this process. When you arrive at the terminal,
your first stop will be at the counter to check your luggage. Next, security will scan
it to ensure the safety of the flight. Then it takes a ride on the “luggage train” on
its way to being placed on the plane. If you need to catch a connecting flight, your
luggage needs to be moved as well. When you arrive at your final destination, the
luggage has to be removed from the plane and placed on the carousel. Finally,
you go down to the baggage claim area and pick it up.

Even though there are many steps to this process, you are only actively
involved in a couple of those steps. The carrier itself is responsible for driving the
process. You are only involved when you need to be; the rest is just “taken care of.”
This mirrors a powerful design pattern: the Template Method pattern.

A template method defines the skeleton of a process. In our example, the pro-
cess is moving luggage from departure city to arrival city. The process itself is
fixed; it never changes. The overall sequence of events for handling luggage
occurs the same way every time: luggage is checked in, luggage is loaded on the
plane, and so forth. Some steps of the process are fixed as well—that is, some
steps happen the same every time. When the plane arrives at its destination, every
piece of luggage is unloaded one at a time and placed on a carousel to be taken to
baggage claim.

At certain points, however, the process delegates its work to a subclass to fill in
some implementation-specific details. This is the variable part of the process. For
example, the handling of luggage starts with a passenger checking in the luggage
at the counter. This part of the process always has to happen at the beginning, so
its sequence in the process is fixed. Because each passenger’s luggage check-in is
different, the implementation of this part of the process is determined by the pas-
senger. In software terms, a template method delegates the implementation-
specific portions of the process to an interface. Different implementations of this
interface define specific implementations of this portion of the process.

This is the same pattern that Spring applies to data access. No matter what
technology we are using, certain data access steps are required. For example, we
always need to obtain a connection to our data store and clean up resources
when we are done. These are the fixed steps in a data access process. But each
data access method we write is slightly different. We query for different objects
and update the data in different ways. These are the variable steps in the data
access process.

Spring separates the fixed and variable parts of the data access process into two
distinct classes: templates and callbacks. Templates manage the fixed part of the
process while your custom data access code is handled in the callbacks. Figure 5.3
shows the responsibilities of both of these classes.

As you can see in figure 5.3, Spring’s template classes handle the fixed parts of
data access—controlling transactions, managing resources, and handling excep-
tions. Meanwhile, the specifics of data access as they pertain to your application—
creating statements, binding parameters, and marshaling result sets—are handled
in the callback implementation. In practice, this makes for an elegant framework
because all you have to worry about is your data access logic.

Spring comes with several templates to choose from, depending on your per-
sistence platform choice. If you’re using straight JDBC then you’ll want to use
JdbcTemplate. But if you favor one of the object-relational mapping frameworks
then perhaps
HibernateTemplate or
JpaTemplate is more suitable. Table 5.2 lists
all of Spring’s data access templates and their purpose.

As you’ll see, using a data access template simply involves configuring it as a
bean in the Spring context and then wiring it into your application DAO. Or you
can take advantage of Spring’s DAO support classes to further simplify configura-
tion of your application DAOs. Direct wiring of the templates is fine, but Spring
also provides a set of convenient DAO base classes that can manage the template
for you. Let’s see how these template-based DAO classes work.

Figure 5.3 Spring’s DAO template classes take responsibility for the common
data access duties. For the application-specific tasks, it calls back into a
custom DAO callback object.

Table 5.2 Spring comes with several data access templates, each suitable for a different persistence
mechanism.

5.1.3 Using DAO support classes

The data access templates are not all there is to Spring’s data access framework.
Each template also provides convenience methods that simplify data access with-
out the need to create an explicit callback implementation. Furthermore, on top
of the template-callback design, Spring provides DAO support classes that are
meant to be subclassed by your own DAO classes. Figure 5.4 illustrates the relation-
ship between a template class, a DAO support class, and your own custom DAO
implementation.

Later, as we examine Spring’s individual data access support options, we’ll see
how the DAO support classes provide convenient access to the template class that
they support. When writing your application DAO implementation, you can sub-
class a DAO support class and call a template retrieval method to have direct
access to the underlying data access template. For example, if your application
DAO subclasses JdbcDaoSupport then you only need to call getJdbcTemplate()
to get a JdbcTemplate to work with.

Figure 5.4
The relationship between
an application DAO and
Spring’s DAO support and
template classes

DAO subclasses JdbcDaoSupport then you only need to call getJdbcTemplate()
to get a
JdbcTemplate to work with.

Plus, if you need access to the underlying persistence platform, each of the
DAO support classes provide access to whatever class it uses to communicate with
the database. For instance, the JdbcDaoSupport class contains a getConnection()
method for dealing directly with the JDBC connection.

Just as Spring provides several data access template implementations, it also
provides several DAO support classes—one for each template. Table 5.3 lists the
DAO support classes that come with Spring.

Table 5.3 Spring’s DAO support classes provide convenient access to their corresponding data access
template.

Even though Spring provides support for several persistence frameworks, there
simply isn’t enough space to cover them all in this chapter. Therefore, I’m going
to focus on what I believe are the most beneficial persistence options and the ones
that you’ll most likely be using.

We’ll start with basic JDBC access (section 5.3), as it is the most basic way to
read and write data from a database. Then we’ll look at Hibernate and JPA (sec-
tions 5.4 and 5.5), two of the most popular POJO-based ORM solutions. Finally, I’ll
dig into Spring’s support for iBATIS (in section 5.6), which is a persistence frame-
work that provides the mapping support of an ORM solution with the complete
query control of JDBC.

But first things first—most of Spring’s persistence support options will
depend on a data source. So, before we can get started with creating templates
and DAOs, we need to configure Spring with a data source for the DAOs to access
the database.

5.2 Configuring a data source

Regardless of which form of Spring DAO support you use, you’ll likely need to
configure a reference to a data source. Spring offers several options for configur-
ing data source beans in your Spring application, including:

Data sources that are defined by a JDBC driver

Data sources that are looked up by JNDI

Data sources that pool connections

For production-ready applications, I recommend using a data source that draws
its connections from a connection pool. When possible, I prefer to retrieve the
pooled data source from an application server via JNDI. With that preference in
mind, let’s start by looking at how to configure Spring to retrieve a data source
from JNDI.

5.2.1 Using JNDI data sources

Spring applications will quite often be deployed to run within a JEE application
server such as WebSphere, JBoss, or even a web container like Tomcat. These serv-
ers allow you to configure data sources to be retrieved via JNDI. The benefit of
configuring data sources in this way is that they can be managed completely exter-
nal to the application, leaving the application to simply ask for a data source when
it’s ready to access the database. Moreover, data sources managed in an
application server are often pooled for greater performance and can be hot-
swapped by system administrators.

With Spring, we can configure a reference to a data source that is kept in JNDI
and wire it into the classes that need it as if it were just another Spring bean.
Spring’s
JndiObjectFactoryBean makes it possible to retrieve any object, includ-
ing data sources, from JNDI and make it available as a Spring bean.

We’ll discussJndiObjectFactoryBean a bit more when we get to chapter 11.
For now here’s a JndiObjectFactoryBean that retrieves a data source from JNDI:

<bean id="dataSource"
class="org.springframework.jndi.JndiObjectFactoryBean"
scope="singleton">
<property name="jndiName" value="/jdbc/RantzDatasource" />
<property name="resourceRef" value="true" />
</bean>

The jndiName attribute is used to specify the name of the resource in JNDI. If only
the jndiName property is set then the data source will be looked up as is. But if the
application is running within a Java application server then you’ll want to set the
resourceRef property to true.

When resourceRef is true, the value of
jndiName will be prepended with
java:comp/env/ to retrieve the data source as a Java resource from the applica-
tion server’s JNDI directory. Consequently, the actual name used will be
java:comp/env/jdbc/RantzDatasource.
JNDI data sources in Spring 2.0

If you’re using Spring 2.0, the XML required for retrieving a data source from
JNDI is greatly simplified using the
jee namespace. You can use the configuration
elements from the jee namespace by declaring your <beans> element as follows:
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:jee="http://www.springframework.org/schema/jee"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/
➥ spring-beans-2.0.xsd
http://www.springframework.org/schema/jee
http://www.springframework.org/schema/jee/
➥ spring-jee-2.0.xsd">

The jee namespace offers the
<jee:jndi-lookup> element for retrieving objects
from JNDI. The following XML is equivalent to the explicit declaration of JndiOb-
jectFactoryBean shown earlier:

<jee:jndi-lookup id="dataSource"
jndi-name="/jdbc/RantzDatasource"
resource-ref="true" />

The jndi-name and resource-ref attributes map directly to the
jndiName and resourceRef properties of JndiObjectFactoryBean.

5.2.2 Using a pooled data source

If you’re unable to retrieve a data source from JNDI, the next best thing is to con-
figure a pooled data source directly in Spring. Although Spring doesn’t provide a
pooled data source, there’s a suitable one available in the Jakarta Commons Data-
base Connection Pools (DBCP) project (http://jakarta.apache.org/commons/
dbcp). To add DBCP to your application, either download it and place the JAR file
in your Ant’s build classpath or add the following <dependency> to the Maven 2

Project Object Model (POM):
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.2.1</version>
</dependency>

DBCP includes several data sources that provide pooling, but the BasicData-
Source is one that’s often used because it’s quite simple to configure in Spring
and because it resembles Spring’s own DriverManagerDataSource (which we’ll
talk about next).

For the RoadRantz application, we’ll configure a BasicDataSource bean as fol-
lows:

<bean id="dataSource"
class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="org.hsqldb.jdbcDriver" />
<property name="url"
value="jdbc:hsqldb:hsql://localhost/roadrantz/roadrantz" />
<property name="username" value="sa" />
<property name="password" value="" />
<property name="initialSize" value="5" />
<property name="maxActive" value="10" />
</bean>

The first four properties are elemental to configuring a BasicDataSource. The
driverClassName property specifies the fully qualified name of the JDBC driver
class. Here we’ve configured it with the JDBC driver for the Hypersonic database.

The url property is where we set the complete JDBC URL for the database. Finally,
the username and password properties are used to authenticate when you’re con-
necting to the database.

Those four basic properties define connection information for
BasicData-Source. In addition, several properties can be used to configure the data source
pool itself. Table 5.4 lists a few of the most useful pool-configuration properties of
BasicDataSource.

For our purposes, we’ve configured the pool to start with five connections.
Should more connections be needed, BasicDataSource is allowed to create them,
up to a maximum of 10 active connections.

Table 5.4 BasicDataSource’s pool-configuration properties.

5.2.3 JDBC driver-based data source

The simplest data source you can configure in Spring is one that is defined
through a JDBC driver. Spring offers two such data source classes to choose from
(both in the org.springframework.jdbc.datasource package):

DriverManagerDataSource—Returns a new connection every time that a
connection is requested. Unlike DBCP’s BasicDataSource, the connections
provided by DriverManagerDataSource are not pooled.

SingleConnectionDataSource—Returns the same connection every time
that a connection is requested. Although SingleConnectionDataSource
isn’t exactly a pooled data source, you can think of it as a data source with a
pool of exactly one connection.

Configuring either of these data sources is similar to how we configured DBCP’s
BasicDataSource:

<bean id="dataSource"
class="org.springframework.jdbc.datasource.
➥ DriverManagerDataSource">
<property name="driverClassName"
value="org.hsqldb.jdbcDriver" />
<property name="url"
value="jdbc:hsqldb:hsql://localhost/roadrantz/roadrantz" />
<property name="username" value="sa" />
<property name="password" value="" />
</bean>

The only difference is that since neither DriverManagerDataSource nor Single-
ConnectionDataSource provides a connection pool, there are no pool configura-
tion properties to set.

Although SingleConnectionDataSource and DriverManagerDataSource are
great for small applications and running in development, you should seriously
consider the implications of using either in a production application. Because
SingleConnectionDataSource has one and only one database connection to
work with, it doesn’t work well in a multithreaded application. At the same time,
even though DriverManagerDataSource is capable of supporting multiple
threads, it incurs a performance cost for creating a new connection each time a
connection is requested. Because of these limitations, I strongly recommend
using pooled data sources.

Now that we have established a connection to the database through a data
source, we’re ready to actually access the database. The most basic way to access
a database is by using JDBC. So, let’s begin our exploration of Spring’s data
access abstractions by looking at how Spring makes working with simple JDBC
even simpler.

5.3 Using JDBC with Spring

There are many persistence technologies out there. Hibernate, iBATIS, and JPA
are just a few. Despite this, a wealth of applications are writing Java objects to a
database the old-fashioned way: they earn it. No, wait—that’s how people make
money. The tried-and-true method for persisting data is with good old JDBC.

And why not? JDBC does not require mastering another framework’s query lan-
guage. It is built on top of SQL, which is the data access language. Plus, you can
more finely tune the performance of your data access when you use JDBC than
practically any other technology. And JDBC allows you to take advantage of your
database’s proprietary features where other frameworks may discourage or flat-
out prohibit this.

What’s more, JDBC lets you work with data at a much lower level than the per-
sistence frameworks, allowing you to access and manipulate individual columns in
a database. This fine-grained approach to data access comes in handy in applica-
tions, such as reporting applications, where it doesn’t make sense to organize the
data into objects, just to then unwind it back into raw data.

But all is not sunny in the world of JDBC. With its power, flexibility, and other
niceties also comes well, some not-so-niceties.

5.3.1 Tackling runaway JDBC code

While JDBC gives you an API that works closely with your database, you are respon-
sible for handling everything related to accessing the database. This includes man-
aging database resources and handling exceptions.

If you have ever written JDBC that inserts data into the database, the code in

listing 5.1 shouldn’t be all too alien to you.

private static final String MOTORIST_INSERT =
"insert into motorist (id, email, password, " +
"firstName, lastName) " +
"values (null, ?,?,?,?)";

public void saveMotorist(Motorist motorist) {
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = dataSource.getConnection();
stmt = conn.prepareStatement(MOTORIST_INSERT);
stmt.setString(1, motorist.getEmail());
stmt.setString(2, motorist.getPassword());

Listing 5.1 Using JDBC to insert a row into a database

stmt.setString(3, motorist.getFirstName());
stmt.setString(4, motorist.getLastName());
stmt.execute();
} catch (SQLException e) {
…
} finally {
try {
if(stmt != null) { stmt.close(); }
if(conn != null) { conn.close(); }
} catch (SQLException e) {}
}
}

Holy runaway code, Batman! That’s over 20 lines of code to insert a simple object
into a database. As far as JDBC operations go, this is about as simple as it gets. So
why does it take this many lines to do something so simple? Actually, it doesn’t.
Only a handful of lines actually do the insert. But JDBC requires that you properly
manage connections and statements and somehow handle the
SQLException that
may be thrown.

Now have a look at listing 5.2, where we use traditional JDBC to update a row in
the motorist table in the database.

private static final String MOTORIST_UPDATE =
"update motorist " +
"set email=?, password=?, firstName=?, lastName=? " +
"where id=?";

public void updateMotorist(Motorist motorist) {
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = dataSource.getConnection();
stmt = conn.prepareStatement(MOTORIST_UPDATE);
stmt.setString(1, motorist.getEmail());
stmt.setString(2, motorist.getPassword());
stmt.setString(3, motorist.getFirstName());
stmt.setString(4, motorist.getLastName());
stmt.setInt(5, motorist.getId());
stmt.execute();
} catch (SQLException e) {
…
} finally {
try {
if(stmt != null) { stmt.close(); }
if(conn != null) { conn.close(); }
} catch (SQLException e) {}
}
}

Listing 5.2 Using JDBC to update a row in a database

At first glance, listing 5.2 may appear to be identical to listing 5.1. In fact, disre-
garding the SQL String and the line where the statement is created, they are iden-
tical. Again, that’s a lot of code to do something as simple as update a single row in
a database. What’s more, that’s a lot of repeated code. Ideally, we’d only have to
write the lines that are specific to the task at hand. After all, those are the only
lines that distinguish listing 5.2 from listing 5.1. The rest is just boilerplate code.

To round out our tour of traditional JDBC, let’s see how we might retrieve data
out of the database. As you can see in listing 5.3, that’s not too pretty, either.

private static final String MOTORIST_QUERY =
"select id, email, password, firstName, lastName " +
" from motorist where id=?";

public Motorist getMotoristById(Integer id) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = dataSource.getConnection();
stmt = conn.prepareStatement(MOTORIST_QUERY);
stmt.setInt(1, id);
rs = stmt.executeQuery();
Motorist motorist = null;
if(rs.next()) {
motorist = new Motorist();
motorist.setId(rs.getInt("id"));
motorist.setEmail(rs.getString("email"));
motorist.setPassword(rs.getString("password"));
motorist.setFirstName(rs.getString("firstName"));
motorist.setLastName(rs.getString("lastName"));
}
return motorist;
} catch (SQLException e) {
…
} finally {
try {
if(rs != null) { rs.close(); }
if(stmt != null) { stmt.close(); }
if(conn != null) { conn.close(); }
} catch (SQLException e) {}
}
return null;
}