Easy JDBC using Spring


Spring has a well-written module for JDBC. We’ll use it to produce clean code for our application in 10 minutes.

The model

Our application handles information about persons. For every person there is interest about his name, age and a unique number (key).

In Java it’s a plain bean.

public class Person {
    private String name;
    private int age;
    private int id;
	
    // getters and setters
}

In the database it’s a simple table.

The persons table

To create it in MySQL you may refer to MySQL in 3 minutes.

Libraries

Download Apache Commons Logging from apache.org → 1.1.1.zip.

  • commons-logging-1.1.1.jar

Download Spring from springsource.com → Latest GA release → spring-framework-3.x.x.zip. The dist directory of this file contains the Spring 3 modules. We need the following jars:

  • jdbc-3
  • asm-3
  • core-3
  • beans-3
  • context-3
  • expression-3
  • transaction-3

Download MySQL JDBC driver from mysql.com → No thanks, just take me to the downloads → mysql-connector-java-5.1.8.zip.

  • mysql-connector-java-5.1.8-bin.jar

These files should be included in the project’s classpath.

For instance, here’s how I configured the libraries using NetBeans.

The libraries in the Projects tab of the NetBeans IDE

Design

We decide to create a parent class that handles all the connection details.

The basic hierarchy of our design

This parent class (Data Access Object) should provide connection and query capabilities to its children in a transparent way.

public class ParentDAO extends JdbcDaoSupport {

}

We could also define the minimum functionality that the children should provide.

public abstract class ParentDAO<T> extends JdbcDaoSupport {

   public abstract void insert(T object) throws Exception;

   public abstract void delete(int id) throws Exception;

   public abstract void update(T object) throws Exception;

   public abstract T select(int id) throws Exception;
}

Implementation

By using the handy getJdbcTemplate method, the actual implementation becomes clean.

In order to insert, delete or update data, the update method is used.

public void insert(Person person) throws Exception {
   String sql = "INSERT INTO persons(name, age) VALUES (?, ?)";
   Object[] parameters = new Object[] { person.getName(), person.getAge() };
   getJdbcTemplate().update(sql, parameters);
}

public void delete(int id) throws Exception {
   String sql = "DELETE FROM persons WHERE id = ?";
   Object[] parameters = new Object[] { id };
   getJdbcTemplate().update(sql, parameters);
}

public void update(Person person) throws Exception {
   String sql = "UPDATE persons SET name = ?, age = ? WHERE id = ?";
   Object[] parameters = new Object[] {
      person.getName(), person.getAge(), person.getId()
   };
   getJdbcTemplate().update(sql, parameters);
}

Also, in order to select data the queryForObject method is used.

public Person select(int id) throws Exception {
   String sql = "SELECT id, name, age FROM persons WHERE id = ?";
   Object[] parameters = new Object[] { id };
   RowMapper set = new RowMapper() {
      public Object mapRow(ResultSet set, int row) throws SQLException {
         Person person = new Person();
         person.setId(set.getInt("id"));
         person.setAge(set.getInt("age"));
         person.setName(set.getString("name"));
         return person;
     }
   };
   Person person = (Person)getJdbcTemplate().queryForObject(sql, parameters, set);
   return person;
}

Of course there many overloaded methods for update and query for every taste.

Connection properties

It’s time to specify the connection properties.

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
   <property name="driverClassName" value="com.mysql.jdbc.Driver" />
   <property name="url" value="jdbc:mysql://127.0.0.1/hello" />
   <property name="username" value="nikos" />
   <property name="password" value="passwd1" />
</bean>

So Spring will create a data source and inject it to the parent object.

<bean id="parentDao" class="dao.ParentDAO" abstract="true">
   <property name="dataSource" ref="dataSource" />
</bean>

Now, every child will have available the data source.

<bean id="personDao" class="dao.PersonDAO" parent="parentDao" />

Complete code

The parent DAO.

public abstract class ParentDAO<T> extends JdbcDaoSupport {
   /**
    * Inserts a new object in the database.
    */
   public abstract void insert(T object) throws Exception;
   /**
    * Deletes a specific object from the database.
    */
   public abstract void delete(int id) throws Exception;
   /**
    * Updates the given object details.
    */
   public abstract void update(T object) throws Exception;
   /**
    * Selects a specific object from the database.
    */
   public abstract T select(int id) throws Exception;
}

API: JdbcDaoSupport

The DAO that handles persons.

public class PersonDAO extends ParentDAO<Person> {

   @Override
   public void insert(Person person) throws Exception {
      String sql = "INSERT INTO persons(name, age) VALUES (?, ?)";
      Object[] parameters = new Object[] {person.getName(), person.getAge()};
      getJdbcTemplate().update(sql, parameters);
   }

   @Override
   public void delete(int id) throws Exception {
      String sql = "DELETE FROM persons WHERE id = ?";
      Object[] parameters = new Object[] {id};
      getJdbcTemplate().update(sql, parameters);
   }

   @Override
   public void update(Person person) throws Exception {
      String sql = "UPDATE persons SET name = ?, age = ? WHERE id = ?";
      Object[] parameters = new Object[] {
         person.getName(), person.getAge(), person.getId()
      };
      getJdbcTemplate().update(sql, parameters);
   }

   @Override
   public Person select(int id) throws Exception {
      String sql = "SELECT id, name, age FROM persons WHERE id = ?";
      Object[] parameters = new Object[] { id };
      RowMapper set = new RowMapper() {
         public Object mapRow(ResultSet set, int row) throws SQLException {
            Person person = new Person();
            person.setId(set.getInt("id"));
            person.setAge(set.getInt("age"));
            person.setName(set.getString("name"));
            return person;
         }
      };
      Person person = (Person)getJdbcTemplate().queryForObject(sql, parameters, set);
      return person;
   }

}

API: JdbcTemplate, PreparedStatementSetter, RowMapper

The bean that represents a person.

public class Person {
   /**
    * The name.
    */
   private String name;
   /**
    * The age.
    */
   private int age;
   /**
    * The unique identifier.
    */
   private int id;

   public int getAge() {
      return age;
   }

   public void setAge(int age) {
      this.age = age;
   }

   public int getId() {
      return id;
   }

   public void setId(int id) {
      this.id = id;
   }

   public String getName() {
      return name;
   }

   public void setName(String name) {
      this.name = name;
   }

}

The Spring configuration file.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

   <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName" value="com.mysql.jdbc.Driver" />
      <property name="url" value="jdbc:mysql://127.0.0.1/hello" />
      <property name="username" value="nikos" />
      <property name="password" value="passwd1" />
   </bean>

   <bean id="parentDao" class="dao.ParentDAO" abstract="true">
      <property name="dataSource" ref="dataSource" />
   </bean>

   <bean id="personDao" class="dao.PersonDAO" parent="parentDao" />

</beans>

Summary

Using Spring, it’s easy to write JDBC code.

Moreover, there is a well defined API for database exceptions, that is really developer-friendly: CannotAcquireLockException, ConcurrencyFailureException etc.

At this point, a new requirement is added. The application should also handle objects of type Address. Well, we simply define:

public class AddressDAO extends ParentDAO<Address> {
   // implementation here
}

and add a declaration to the context file.

<bean id="addressDao" class="dao.AddressDAO" parent="parentDao" />

As easy as it gets.

11 Responses to Easy JDBC using Spring

  1. […] Easy JDBC using Spring « Nikos’ Java blog nikojava.wordpress.com/2009/08/29/easy-jdbc-using-spring – view page – cached #Nikos' Java blog RSS Feed Nikos’ Java blog Atom Feed Nikos' Java blog » Easy JDBC using Spring Comments Feed Nikos' Java blog Hibernate σε 10 λεπτά Free SCWCD Mock Exam for EL — From the page […]

  2. Niko,

    With respect to showing the basic usage model of JdbcTemplate, your sample is fine. However, you really should not be managing the DataSource yourself in this fashion, but rather injecting it into your DAO class. Then it can be a Spring managed object, and the user of the DAO can plug in whatever connection pool DataSource variant (e.g. Apache Commons DBCP) makes sense…

    Colin

  3. Nommi says:

    hmmmmm Colin Sampaleanu is right there is a clean way that you use DataSource by spring and inject it via DI
    well remaning is geart thanx for writing

  4. Nikos says:

    Colin, I hope great success for SpringSource in its new prosperous era under VMWare.

    Nommi, thanks a lot for your comments, I’ve already updated this post.

  5. elli says:

    very useful!

  6. maruf says:

    Can’t see full code … Plz fixed it…Coz it is very helpful tutorial …

  7. Emre says:

    @maruf
    just copy the code and paste it on your text editor.

  8. renato says:

    hello
    first of all
    i am learning spring 3 jdbc
    could you help me please
    if so, could you send me a complete example in netbeans 7

    thanks a lot

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: