Simplifying JDBC with the Spring JDBC Abstraction Framework

Spring’s excellent JDBC abstraction framework saves you from writing all of the error-prone JDBC mucky muck that you have probably written hundreds of times before, such as all of the boilerplate code of try/catch/finally blocks that go into creating, opening, and closing of connections, statements, etc.

Even if you do not use the entire JDBC framework to create your DAOs, I have found using the JdbcTemplate is very nice for getting data from legacy applications. Also, if you do not want to bite the entire ORM bullet (Hibernate, JDO, etc.), using Spring’s JDBC framework is a nice way to keep your code simple, while letting you retain the full power of writing your own SQL.

At the core of the JDBC abstraction is the JdbcTemplate. From the JdbcTemplate JavaDoc:

JdbcTemplate simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results. This class executes SQL queries or updates, initiating iteration over ResultSets and catching JDBC exceptions and translating them to the generic, more informative exception hierarchy defined in the org.springframework.dao package.

A JdbcTemplate can be used within a service implementation via direct instantiation with a DataSource reference, or get prepared in an application context and given to services as bean reference.

In a nutshell the JdbcTemplate offers a plethora of convenience methods for queries, inserts, updates and executions; it let’s you focus on writing the SQL and lets Spring deal with ugly JDBC code.

Writing a WidgetDAO Implementation with the JdbcTemplate

So let’s write an implementation of the WidgetDAO interface presented in the previous section. As a reminder, here is the WidgetDAO interface:

/**
 * DAO interface for Widgets
 * @author rlambert
 */
public interface WidgetDAO
{
  /**
   * Returns a collection of all Widgets in the system.
   */
  public Collection getWidgets();

  /**
   * Get a Widget Object given the id
   */
  public Widget getWidgetById(Long id);

  /**
   * Save a Widget Object, if the given Widget
   * is not in the data store, it should insert it,
   * if it is in the data store, it should update it.
   */
  public Widget saveWidget(Widget widget);
}

Our database table for Widgets looks something like this:

create table WIDGETS
(
  WIDGET_ID bigint not null auto_increment,
  NAME varchar(255),
  SIZE int,
  primary key (WIDGET_ID)
);

For our actual WidgetDAO JdbcTemplate implementation, let’s make a DataSource an external dependency that will be injected into our WidgetDAO implementation. A DataSource can easily be configured in our Spring config using Apache DBCP or another DataSource provider. Given this DataSource, internally we will build a JdbcTemplate to be used for doing the actual work. Here is the start of our class:

/**
 * WidgetDAO JDBC implementation using Spring JdbcTemplate.
 * @author rlambert
 */
public class WidgetDAOJdbcImpl implements WidgetDAO
{
  private DataSource dataSource;
  private JdbcTemplate jdbcTemplate;

  public void setDataSource(DataSource dataSource)
  {
    this.dataSource = dataSource;
    jdbcTemplate = new JdbcTemplate(dataSource);
  }

  ...

}

We can easily wire this up in our application with something like:

<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName">
    <value>org.hsqldb.jdbcDriver</value>
  </property>

  <property name="url">
    <value>jdbc:hsqldb:mem:widgets</value>
  </property>
  <property name="username">
    <value>sa</value>

  </property>
  <property name="password">
    <value></value>
  </property>
</bean>

<bean id="widgetDAO"
 class="com.zabada.springrecipes.dao.jdbc.WidgetDAOJdbcImpl">

  <property name="dataSource">
    <ref local="dataSource"/>
  </property>
</bean>

In reality we will make this a little more complex by wrapping some transaction related stuff around our DAO, but this is the gist.

Now, let’s fill out the required methods for our WidgetDAO. For our DAO, the only two tricky things will be writing a RowMapper to map a ResultSet row to an actual Widget having to deal with properly setting the unique identifiers on our Widgets when we save them to the database.

Implementing the Getters

Let’s start with the methods getWidgets() and getWidgetById(Long id). First, we will need to implement a RowMapper to map a row from a ResultSet to a Widget instance. Here is our WidgetRowMapper:

/**
 * RowMapper for a Widget Record
 *
 * @author rlambert
 */
public class WidgetRowMapper implements RowMapper
{
  /**
   * Creates a Widget from a ResultSet
   */
  public Object mapRow(ResultSet rs, int rowNum)
  {
    Widget widget = new Widget();
    try
    {
      widget.setId(new Long(rs.getLong("WIDGET_ID")));
      widget.setName(rs.getString("NAME"));
      widget.setSize(rs.getInt("SIZE"));
    }
    catch (SQLException e)
    {
      //TODO: something
    }
    return widget;
  }
}

Nothing tricky there. Spring will deal with creating ResultSets and will pass them to this RowMapper as it iterates over the results; it is just our job to take the current row of the ResultSet and turn it into a Widget object.

Now, for the getWidgets() method, we can simply use a SELECT statement along with our WidgetRowMapper to call the query method on our internal JdbcTemplate instance:

...

  public Collection getWidgets()
  {
    return jdbcTemplate.query("select WIDGET_ID, NAME, SIZE + "
                              "from WIDGETS",
                              new WidgetRowMapper());
  }

...

}

That’s it. Spring deals with all of the ugly try/catches around Statement/PreparedStatement/Connection creation/clean-up behind the scenes.

Now, let’s do the method getWidgetById(Long id). It is just as easy as previous method. Once again, we will use our WidgetRowMapper:

  ...

  public Widget getWidgetById(Long id)
  {
    String sql = "select WIDGET_ID, NAME, SIZE " +
                 "from WIDGETS where WIDGET_ID = ?"
    return (Widget) jdbcTemplate.queryForObject(sql ,
                                  new Object[] { id },
                                  new WidgetRowMapper());
  }

  ...

In this case, we call the queryForObject method on the JdbcTemplate, passing it our SQL and then our parameters for our SQL query, followed by a WidgetRowMapper. It is our job to make sure that the parameters passed in match with the parameters in the SQL; in this case we are simply passing in the id for the Widget that we want to retrieve.

Implementing the Save

Let’s proceed to the the saveWidget(Widget widget) method. Remember, the WidgetDAO interface calls for the method saveWidget(Widget widget) to handle both inserts and updates. If the Widget’s id is null, we will assume that it has not been saved and assume that we should insert the object, otherwise, we will update. The update portion is easy. We can just call the JdbcTemplate’s update method:

  ...

  /**
   * Saves the Widget storage to the database.
   */
  public Widget saveWidget(Widget widget)
  {
    if (widget.getId() == null)
    {
      //TODO: insert the Widget
    }
    else
    {
      Object[] params =
        new Object[]
        {
          widget.getName(),
          new Integer(widget.getSize()),
          widget.getId()
        };
      jdbcTemplate.update("update WIDGETS "+
                          "set NAME = ?, SIZE = ? "+
                          "where WIDGET_ID = ?",
                          params);
    }
    return Widget;
  }

  ...

All that is happening here is that we are passing the JdbcTemplate’s update method an update SQL statement and then an Object array to be used as parameters for the update statement (Spring will internally use a PreparedStatement and set the parameters appropriately from the Object[]).

Now onto the the insert, and then we’re done! The insert is a little tricky, because we want to make sure to set the primary key on the Widget immediately after the insert, so the calling application can continue using the Widget in the same thread, and possibly even re-save it (in that case we don’t want the id to be null because then a new record will be inserted).

For the insert, I am going to create an inner class that extends Springs SqlUpdate. This class will call the JdbcTemplate update method to insert the Widget and then call the database’s identity command to get the id and then set it on the current Widget. There will be a single private instance of this inner class on our WidgetDAO. I also added an external dependency on our WidgetDAO for the database’s identity command (that way we can change our DAO for various RDBMS’s in our Spring config). Here the rest of the code including the full saveWidget method and the inner class to do the inserting:

  ...

  private DataSource dataSource;
  private JdbcTemplate jdbcTemplate;
  private WidgetInsert widgetInsert;
  //DEFAULT IDENTITY IS FOR FOR HSQL
  private String identityCall = "call identity()";

  public void setDataSource(DataSource dataSource)
  {
    this.dataSource = dataSource;
    jdbcTemplate = new JdbcTemplate(dataSource);
    widgetInsert = new WidgetInsert(dataSource);
  }

  public void setIdentityCall(String identityCall)
  {
    this.identityCall = identityCall;
  }

  ...

  /**
   * Saves the Widget storage to the database.
   */
  public Widget saveWidget(Widget widget)
  {
    if (widget.getId() == null)
    {
      widgetInsert.insert(widget);
    }
    else
    {
      Object[] params =
        new Object[]
        {
          widget.getName(),
          new Integer(widget.getSize()),
          widget.getId()
        };
      jdbcTemplate.update("update WIDGETS " +
                          "set NAME = ?, SIZE = ? " +
                          "where WIDGET_ID = ?",
                          );
    }
    return Widget
  }

  /**
   * Widget Insert Object.
   */
  protected class WidgetInsert extends SqlUpdate
  {
    /**
     * Create a new instance of WidgetInsert.
     * @param ds the DataSource to use for the insert
     */
    protected WidgetInsert(DataSource ds)
    {
      super(ds, "insert into WIDGETS (NAME, SIZE) values (?, ?)");
      declareParameter(new SqlParameter(Types.VARCHAR));
      declareParameter(new SqlParameter(Types.INTEGER));
      compile();
    }

    /**
     * Method to insert a new Widget.
     * @param Widget to insert
     */
    protected void insert(Widget widget)
    {
      Object[] params =
       new Object[]
       {
         widget.getName(),
         new Integer(widget.getSize())
       };
      super.update(objs);
      Long id = new Long(jdbcTemplate.queryForLong(identityCall));
      widget.setId(id);
    }
  }
}

That’s it!

An Actual Spring Configuration

Here is our final config with a transaction manager wired in. Spring will automatically start a new transaction for each call to our DAO and will commit on success and rollback on error.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC
   "-//SPRING//DTD BEAN//EN"
   "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>

  <bean id="dataSource" class=
 "org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName">
      <value>org.hsqldb.jdbcDriver</value>
    </property>
    <property name="url">

      <value>jdbc:hsqldb:mem:widgets</value>
    </property>
    <property name="username"><value>sa</value></property>
    <property name="password"><value></value></property>

  </bean>

  <bean id="transactionManager" class="org.springframework.
jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource">
      <ref local="dataSource"/>
    </property>
  </bean>

  <!--
    Note use of "inner bean" for target property.
    This ensures that there is only one instance of this
    bean in the factory, allowing us to use autowire
    by type if necessary.
  -->
  <bean id="widgetDAO" class="org.springframework.
transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager">
      <ref local="transactionManager"/>
    </property>
    <property name="target">

      <bean
  class="com.zabada.springrecipes.dao.jdbc.WidgetDAOJdbcImpl">
        <property name="dataSource">
          <ref local="dataSource"/>
        </property>
      </bean>
    </property>

    <property name="transactionAttributes">
      <props>
        <prop key="*">PROPAGATION_REQUIRED</prop>
      </props>
    </property>

  </bean>

</beans>

The downloadable code contains a test that creates a new in memory HSQL database to show this working. I have also included the previous Map-based implementation that has a test that extends from the same base test case.

Summary

The JdbcTemplate simplifies your JDBC code, letting you focus on the SQL and letting Spring deal with the most of the repetative stuff. There is a lot more to the JdbcTemplate and the rest of Spring JDBC abstraction framework, but hopefully this will help you get started!

Further Reading

• Spring Reference Documentation - Data Access using JDBC
• JdbcTemplate JavaDoc
• Spring in Action by Craig Walls and Ryan Breidenbach - Chapter 4: Hitting the Database

Next: Spring and Hibernate Integration