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

24

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/.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

24 Comments

  1. Hi,
    Please tell me the exact difference between the Hibernate 3 vs Spring JDBC. Which one gives better performance

  2. Thank u so much 4 providing this good article with source code which helped me in doing my task in my project using Spring JDBC Framework

  3. What do you think about IBatis? I think it is much simpler than SpringJDBC, specially when u try using dynamic SQL and setting filters using WHERE clause

  4. Its a good article.But it lacks configuration details such as where to place xml,lib etc files. It would have been better by explaining in that way

  5. hi It is good enough but not so in brief to understand plzz make it understandable for new user of spring so that they can make more proffite. Thank you Vivek Kumar

  6. Thank u so much 4 providing this good article with source code which helped me in doing my task in my project using Spring JDBC Framework.

  7. Its a good article. But it lacks configuration details such as where to place xml,lib etc files. It would have been better by explaining in that way

  8. Spring JDBC intro is very nice, They can explain the keywords “JDBC template” , “RowSet”.. Thanks

  9. Its a good article. But it lacks configuration details such as where to place xml,lib etc files. It would have been better by explaining in that way .

  10. This is one of the best introduction I came across and I could relate most of it with my application.
    However, for a begininer I Think DAO and Template pattern must be explained at startof this article esp the advantages of DAO pattern.

  11. What do you think about IBatis? I think it is much simpler than SpringJDBC, specially when u try using dynamic SQL and setting filters using WHERE clause.
    So a combination of Spring IoC and IBatis will be better rather than SpringJDBC.

    Any comments?

  12. It is indeed a good introduction into Spring and JDBC. I liked it a lot and was able to relate most of it with my application.

  13. Very good intro to Spring JDBC . The article touches the essence of the frame work with respect to JDBC. Vasanth J2EE Developer

  14. hi
    It is good enough but not so in brief to understand plzz make it
    understandable for new user of spring so that they can make more
    proffite.
    Thank you
    Vivek Kumar

  15. how to use RESULTSET_TYPE_SENSITIVE,CONCUR_UPDATABLE ResultSet in spring to
    update the values from sql command.. plz help me

    Thanks in Advance
    Rengaram

  16. HrmService should extend EmpDAO

    i.e.
    public interface HrmService extends EmpDAO
    {
    public void setEmployeeDao(EmpDAO employeeDAO);
    }

    > public class HrmClient extends RuntimeException

    Is that some kind of joke?

    A non-Exception extends RuntimeException??

  17. Alright, but I would still prefer something more lightweight than Spring. Something like this:

    connect connStr

    for record in select ename, job from emp
    println (record.ename + " (" + record.job + ")"

    You would be right to argue that you do a little more than that. You load the data into an in memory data structure that could be cached and reused elsewhere. So maybe this would be the equivalent:


    connect connStr
    rs = select ename, job from emp

    for record in rs
    println record.ename + " (" + record.job + ")"

    But I guess I’m just kidding. Lightweight times are over, if they actually ever existed…