Introducing Spring JDBC – frequently the best introduction of Spring in an organization

Spring consists of a number of modules, such as the IoC Container, AOP, MVC, Persistence, DAO and Remoting. These modules are relatively losely coupled: some can be used without using the others. There is not really such a thing as “a Spring application” – you can opt to use one, some or many Spring components.

The easiest way to get going with Spring in many instances is through Spring JDBC. Any application that makes direct use of JDBC (not through some O/R Mapping framework that has JDBC inside) can easily benefit from Spring. Spring makes it possible for developers to concentrate on the application or business specific pices of code, instead of having to deal with a lot of plumbing and infrastructure, such as endless try-catch-finally-try-catch constructs and handling Checked Exceptions etc. Writing JDBC code is arguably not a fun job because of all this repetitive overhead. In fact, most JDBC developers probably will have developed something like Spring JDBC – though probably not as thoroughly worked.

Spring JDBC implements the Template Design Pattern. The repetitive, plumbing parts of the code are implemented in template classes. These are subclassed by application specific classes that plug in custom logic – primarily SQL queries and bind-parameters.

A simple example of a piece of JDBC code can be used to illustrate this pattern. Using straight JDBC, you could write this code to retrieve Employee records from a database:

package nl.amis.spring.jdbc;
import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import nl.amis.spring.hrm.EmpDAO;
import nl.amis.spring.hrm.Employee;
public class JDBCWithoutSpring {
 public JDBCWithoutSpring() {
  }
    public List getAllEmployees() {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List emps = new ArrayList();
        try {
            con = getConnection();
            pstmt = con.prepareStatement
                        ("select ename, job from emp");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Employee e = new Employee();
                e.setLastName(rs.getString(1));
                e.setJobTitle(rs.getString(2));
                emps.add(e);
            }
        } catch (SQLException e) {
            // handle exception
        } finally {
            try {
                rs.close();
                pstmt.close();
                con.close();
            } catch (SQLException e1) {
                // no action needed
            }
        }
        return emps;
    }
  private Connection getConnection() throws SQLException  {
    try {
        DriverManager.registerDriver
            (new oracle.jdbc.driver.OracleDriver());
        return DriverManager.getConnection
            ("jdbc:oracle:thin:@localhost:1521:orcl"
             ,"scott", "tiger");
    } catch (SQLException sqle) {
        // handle exception
        return null;
    }
  }// getConnection
  public static void main(String[] args)  throws Exception {
    JDBCWithoutSpring empDAO = new JDBCWithoutSpring();
    List emps = empDAO.getAllEmployees();
    Iterator empIter = emps.iterator();
    while (empIter.hasNext()) {
      Employee emp = (Employee)empIter.next();
      System.out.println(emp.getLastName()+" ("+emp.getJobTitle()+")");
    }//while
  }//main
}

What should be evident from this example – and of course there can be a lot said about the way the Connection is retrieved – using a DataSource from a J2EE Container or JNDI is much neater although it raises other issues and dependencies etc. – is that there are relatively few lines of code that actually relate to the business at hand. The getConnection() method has nothing to do with our employees and even the getAllEmployees() method contains just five lines of code that are Employee specific. The rest is generic, plumbing code.

Implementing the same business logic with Spring JDBC basically means removing the generic code to the generic Spring JDBC Templates – superclasses that our DAO-Implementation classes inherit from.

package nl.amis.spring.jdbc;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.Iterator;
import java.util.List;

import javax.sql.DataSource;

import nl.amis.spring.hrm.EmpDAO;
import nl.amis.spring.hrm.Employee;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultReader;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class EmployeeJdbcDAO extends JdbcDaoSupport implements EmpDAO  {

   public long getSalarySum() {
        JdbcTemplate jt = getJdbcTemplate();
        return jt.queryForLong ( "select sum(sal) from emp");
    }//getSalarySum

    public List getAllEmployees() {
        JdbcTemplate jt = getJdbcTemplate();
        return jt.query ( "select ename, job from emp"
                        , new RowMapperResultReader(new EmployeeRowMapper())
                        );
    }//getAllEmployees

    // this inner class is invoked for each Row om the ResultSet. It implements
    // the Spring RowMapper interface, that is used to convert ResultSet records
    // to Domain Objects. RowMappers are used when the results are not simple,
    // like in the getSalarySum example above
    class EmployeeRowMapper implements RowMapper {
      public Object mapRow(ResultSet rs, int index) throws SQLException {
        Employee emp = new Employee();
        emp.setLastName(rs.getString(1));
        emp.setJobTitle(rs.getString(2));
        return emp;
      }
    }//class EmployeeRowMapper


public static void main(String[] args)  throws Exception {

    ApplicationContext ctx = new ClassPathXmlApplicationContext("SpringConfig.xml");
    DataSource ds = (DataSource)ctx.getBean("dataSourceDBDirect");
    EmployeeJdbcDAO empDao = new EmployeeJdbcDAO();
    // invoke the setDataSource() method on the superclass
    empDao.setDataSource(ds);

    Iterator empIter = empDao.getAllEmployees().iterator();
    while (empIter.hasNext()) {
      Employee emp = (Employee)empIter.next();
      System.out.println(emp.getLastName()+" ("+emp.getJobTitle()+")");
    }//while
    System.out.println("The Salary Sum = "+empDao.getSalarySum());
}// main

}// EmployeeJdbcDao

In this case, we not only use Spring JDBC, we also make use of the IoC Container to provide a DataSource that we can inject into the EmployeeJdbcDAO object. The DataSource has to be injected into this Class (or rather its superclass), via the setDataSource() method. If that is the way you have it done, all configuration details stay out of the business service or client code – which increases the decoupling of your application and thereby the testability and maintainability. Alternatively, we can setup a DataSource in the JNDI or Servlet Container, retrieve it programmatically and inject it into the DAO object.

The Spring Bean Configuration File you could use:

<?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="dataSourceDBDirect"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource"
        destroy-method="close">
    <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
    <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"/>
    <property name="username" value="scott"/>
    <property name="password" value="tiger"/>
  </bean>
</beans>

This file instructs the Spring Bean Container to instantiate a dataSourceDBDirect bean that is created based on the org.springframework.jdbc.datasource.DriverManagerDataSource class.

Implementing a Business Service on top of Spring JDBC

Before, you saw a very simple example of using Spring JDBC and in this case a little help from the Spring BeanFactory (Inversion of Control container). We will now go a few steps beyond the simple example. We are now going to create an HrmClient, an application that produces output for end users. The HrmClient makes use of an HrmService, a Business Service according to the following HrmService interface:

package nl.amis.spring.hrm;
import java.util.List;

public interface HrmService  {

  public void setEmployeeDao(EmpDAO employeeDAO);

  public List getAllEmployees();

  public long getSalarySum();

}

The HrmClient needs access to a Business Service object. It will use the Spring BeanContainer to get hold of such a service object. The client can just program against the interface and rely on the container to provide an actual implementation.

You probably have already noticed that the HrmService requires an EmployeeDao – presumably an object that provides access to Employee objects retrieved from some back-end datastore. The EmployeeDao object must implement the EmpDAO interface:

package nl.amis.spring.hrm;
import java.util.List;

public interface EmpDAO {

    public long getSalarySum();

    public List getAllEmployees ();
}

Of course we will have this DAO injected be the BeanFactory into our HrmService. We happen to have the EmployeeJdbcDAO class that the Bean Factory can use for this purpose. However, since this class extends from JdbcDaoSupport we know we need to inject a DataSource. Or have the Bean Factory inject that DataSource for us.

The Bean Configuration File now looks as follows:

<?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="dataSourceDBDirect"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource"
        destroy-method="close">
    <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
    <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"/>
    <property name="username" value="scott"/>
    <property name="password" value="tiger"/>
  </bean>
  <bean id="employeeDAO" class="nl.amis.spring.jdbc.EmployeeJdbcDAO">
    <property name="dataSource">
      <ref local="dataSourceDBDirect"/>
    </property>
  </bean>
  <bean id="hrmService" class="nl.amis.spring.hrm.HrmServiceImpl">
    <property name="employeeDao">
      <ref local="employeeDAO"/>
    </property>
  </bean>
</beans>

We see the hrmService bean that gets the employeeDao bean injected that in turn gets the dataSourceDBDirect object injected. When we ask for the hrmService bean, we get it with a DAO that is ready to go with a DataSource. All ready to roll.

This picture illustrates what is going on. HrmClient access the Spring Bean Container to get a HrmService object. The Bean Container instantiates and injects a DataSource and an EmpDAO before return the HrmService to HrmClient.

Our HrmClient becomes quite simple now. It needs to communicatie with the Bean Factory, get hold of an HrmService object and do its thing:

package nl.amis.spring;
import java.util.Iterator;

import nl.amis.spring.hrm.Employee;
import nl.amis.spring.hrm.HrmService;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class HrmClient extends RuntimeException  {
  public HrmClient() {
  }

  public static void main(String[] args)  throws Exception {
    // initialize Bean Factory, based on the configuration file SpringConfig.xml in the classpath (at the /class root)
    ApplicationContext ctx = new ClassPathXmlApplicationContext("SpringConfig.xml");
    HrmService hrmService = (HrmService)ctx.getBean("hrmService");

    Iterator empIter = hrmService.getAllEmployees().iterator();
    while (empIter.hasNext()) {
      Employee emp = (Employee)empIter.next();
      System.out.println(emp.getLastName()+" ( " + emp.getJobTitle()+")");
    }//while
    System.out.println("The Salary Sum = "+hrmService.getSalarySum());
  }//main

}// HrmClient

The output from the HrmClient looks like this:

SMITH (CLERK)
ALLEN (SALESMAN)
WARD (SALESMAN)
JONES (ANALYST)
MARTIN (ACCOUNTNT)
BLAKE (MANAGER)
CLARK (MANAGER)
SCOTT (ANALYST)
KING (PRESIDENT)
TURNER (SALESMAN)
ADAMS (CLERK)
JAMES (CLERK)
FORD (ANALYST)
MILLER (CLERK)
Jellema (null)

The Salary Sum = 30025

The HrmServiceImpl class is quite ordinary now:

package nl.amis.spring.hrm;
import java.util.Iterator;
import java.util.List;
import nl.amis.spring.hrm.Employee;
import nl.amis.spring.hrm.EmpDAO;
import nl.amis.spring.jdbc.JDBCWithoutSpring;

public class HrmServiceImpl implements HrmService  {

  EmpDAO employeeDAO;

  public HrmServiceImpl() {
  }

  public void setEmployeeDao(EmpDAO employeeDAO) {
    this.employeeDAO = employeeDAO;
  }

  public List getAllEmployees() {
    List emps = employeeDAO.getAllEmployees();
    return emps;
  }//getAllEmployees()

   public long getSalarySum() {
      return employeeDAO.getSalarySum();
   }//getSalarySum

}

The EmployeeJdbcDAO is same class as shown earlier in this post. The one file missing from this picture is the Employee object. Well, nothing special there:

package nl.amis.spring.hrm;
import java.util.Date;

public class Employee {
  String FirstName;
  String LastName;
  String JobTitle;
  Float Salary;
  Date Hiredate;

  public Employee() {
  }

  public String getFirstName() {
    return FirstName;
  }

  public void setFirstName(String FirstName) {
    this.FirstName = FirstName;
  }

  public String getLastName() {
    return LastName;
  }

  public void setLastName(String LastName) {
    this.LastName = LastName;
  }

  public String getJobTitle() {
    return JobTitle;
  }

  public void setJobTitle(String JobTitle) {
    this.JobTitle = JobTitle;
  }

  public Float getSalary() {
    return Salary;
  }

  public void setSalary(Float Salary) {
    this.Salary = Salary;
  }

  public Date getHiredate() {
    return Hiredate;
  }

  public void setHiredate(Date Hiredate) {
    this.Hiredate = Hiredate;
  }
}

Conclusion

If you have been considering (how) to start using Spring, and you have the occasional JDBC statement in your application, Spring JDBC is the thing for you. Coming from a single jar-file, with no dependencies on other modules in the Spring Framework, Spring JDBC very rapidly provides benefits such as cleaner code, better exception and resource handling (at least than what I usually write) and the ability to really focus on the business problem instead of on plumbing code.

Spring JDBC is easy to set up, easy to use and a great gradual introduction into some of the Spring concepts, such as the Template Pattern (consistent service abstraction) and Dependency Injection or Inversion of Control.

For more details and downloads, go to http://www.springframework.org/.