Spring Data Access Support

Consistent abstract classes
for DAO support

make it easier to work with a variety of data access technologies such as JDBC,
JDO and Hibernate in a consistent way, Spring provides a set of abstract DAO
classes that one can extend. These abstract classes have methods for providing
the data source and any other configuration settings that are specific to the
relevant data-access technology.

  • JdbcDaoSupport –
    superclass for JDBC data access objects. Requires a DataSource to be
    provided; in turn, this class provides a JdbcTemplate instance initialized
    from the supplied DataSource to subclasses.
  • HibernateDaoSupport –
    superclass for Hibernate data access objects. Requires a SessionFactory to
    be provided; in turn, this class provides a HibernateTemplate instance
    initialized from the supplied SessionFactory to subclasses. Can
    alternatively be initialized directly via a HibernateTemplate, to reuse
    the latters settings like SessionFactory, flush mode, exception
    translator, and so forth.
  • JdoDaoSupport – super
    class for JDO data access objects. Requires a PersistenceManagerFactory to
    be provided; in turn, this class provides a JdoTemplate instance
    initialized from the supplied PersistenceManagerFactory to subclasses.
  • JpaDaoSupport – super
    class for JPA data access objects. Requires a EntityManagerFactory to be
    provided; in turn, this class provides a JpaTemplate instance initialized
    from the supplied EntityManagerFactory to subclasses.


  1. Data access using JDBC

There are a number of options for selecting an
approach to form the basis for your JDBC database access. There are three
flavors of the JdbcTemplate, a new "SimpleJdbc" approach taking
advantage of database metadata, and there is also the "RDBMS Object"
style for a more object oriented approach similar in style to the JDO Query
design. We’ll briefly list the primary reasons why you would pick one of these
approaches. Keep in mind that even if you start using one of these approaches,
you can still mix and match if there is a feature in a different approach that
you would like to take advantage of. All approaches requires a JDBC 2.0
compliant driver and some advanced features require a JDBC 3.0 driver.

  • JdbcTemplate – this is the classic Spring JDBC approach and
    the most widely used. This is the "lowest level" approach and
    all other approaches use a JdbcTemplate under the covers. Works well in a
    JDK 1.4 and higher environment.
  • NamedParameterJdbcTemplate – wraps a JdbcTemplate to provide
    more convenient usage with named parameters instead of the traditional
    JDBC "?" place holders. This provides better documentation and
    ease of use when you have multiple parameters for an SQL statement. Works
    with JDK 1.4 and up.
  • SimpleJdbcTemplate – this class combines the most frequently
    used features of both JdbcTemplate and NamedParameterJdbcTemplate plus it
    adds additional convenience by taking advantage of some Java 5 features
    like varargs, autoboxing and generics to provide an easier to use API.
    Requires JDK 5 or higher.
  • SimpleJdbcInsert and SimpleJdbcCall
    – designed to take advantage of database metadata to limit the amount of
    configuration needed. This will simplify the coding to a point where you
    only need to provide the name of the table or procedure and provide a Map
    of parameters matching the column names. Designed to work together with
    the SimpleJdbcTemplate. Requires JDK 5 or higher and a database that
    provides adequate metadata.
  • RDBMS Objects including MappingSqlQuery, SqlUpdate and StoredProcedure – an approach where you create reusable and
    thread safe objects during initialization of your data access layer. This
    approach is modeled after JDO Query where you define your query string,
    declare parameters and compile the query. Once that is done any execute
    methods can be called multiple times with various parameter values passed
    in. Works with JDK 1.4 and higher.

1.1 JdbcTemplate

public class CalendarDaoImpl extends JdbcDaoSupport implements
CalendarDao {

List<Calendar> getLatestThirtyDays(Long userID, Date startDate, Date
endDate, int typeOne, int typeTwo) throws SQLException {


Object[]{userID, startDate, endDate, typeOne, typeTwo, null},





1.2 NamedParameterJdbcTemplate

public class CompanyDaoImpl extends NamedParameterJdbcDaoSupport
implements CompanyDao {

List<Office> getCompanyOffices(long id) {

Object> params = new HashMap<String, Object>();


        // sqlResource.getSql()
is used to get sql string


params, new OfficeRowMapper());




1.3 SimpleJdbcTemplate

public class CalendarDaoImpl extends SimpleJdbcDaoSupport
implements CalendarDao {

List<Calendar> getLatestThirtyDays(Long userID, Date startDate, Date
endDate, int typeOne, int typeTwo) throws SQLException {

super.getSimpleJdbcTemplate().getJdbcOperations().query("call proc_return_user_history_days(?,?,?,?,?,?)",

Object[]{userID, startDate, endDate, typeOne, typeTwo, null},





would be a bit overkilled to use SimpleJdbcTemplate, as the method getSimpleJdbcTemplate().getJdbcOperations()
is actually overlapping  with what getJdbcTemplate()
could provide.  However, by extends the SimpleJdbcDaoSupport,
the newly added feature in spring framework, you got both options to run your
DAO implementation with NamedParamter feature or not with this feature in the
same class.


CalenderMapper and OfficeRowMapper that used in the preceding code are all
extended from RowMapper interface, which is used by JdbcTemplate for mapping rows of a ResultSet on a per-row basis.
Implementations of this interface perform the actual work of mapping each row
to a result object, but don’t need to worry about exception handling. SQLExceptions will be caught and handled
by the calling JdbcTemplate.

public class OfficeRowMapper implements RowMapper {

    public Object mapRow(ResultSet
rs, int rowNum) throws SQLException {

        Office office =
new Office();



        return office;




1.5 StoreProcedure VS

The StoredProcedure class is a superclass
for object abstractions of RDBMS stored procedures. This class is abstract, and
its various execute(..) methods have protected access, preventing use other
than through a subclass that offers tighter typing.

protected class VesselDetailsQuery extends StoredProcedure {

    public VesselDetailsQuery(DataSource
ds) {


SqlParameter("id ", Types.NUMERIC));

declareParameter(new SqlOutParameter("data",
OracleTypes.CURSOR, new VesselRowMapper()));

declareParameter(new SqlParameter("username", Types.VARCHAR));




is an inner class in the DAO class. It extends from StoredProcedure and
constructed with two variable: datasource and the procedure name;

is used to declare the input parameter and SqlOutParameter is there for output
parameters. You can implement the RowMapper for the output like what we did

SimpleJdbcCall takes advantage of metadata present in the database to look
up names of in and out parameters. This means that you don’t have to explicitly
declare parameters. You can of course still declare them if you prefer to do
that or if you have parameters that don’t have an automatic mapping to a Java
class like ARRAY or STRUCT parameters

public class JdbcActorDao implements ActorDao {

SimpleJdbcTemplate simpleJdbcTemplate;

    private SimpleJdbcCall


    public void setDataSource(DataSource
dataSource) {

this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);






    public Actor
readActor(Long id) {

in = new MapSqlParameterSource()

.addValue("in_id", id);

        Map out =

        Actor actor = new



actor.setLastName((String) out.get("out_last_name"));


        return actor;



The execution of the call involves
creating an
SqlParameterSource containing the in parameter. It’s
important to match the name of the parameter declared in the stored procedure.
The case doesn’t have to match since we use metadata to determine how database
objects should be referred to – what you specify in your source for the stored
procedure is not necessarily the way it is stored in the database, some
databases transform names to all upper case while others use lower case or the
case as specified.

You can specify all the details explicitly
rather than relying on metadata. This will be necessary if the database we use
is not part of the supported databases.

    public void
setDataSource(DataSource dataSource) {

        JdbcTemplate jdbcTemplate
= new JdbcTemplate(dataSource);



                new SimpleJdbcCall(jdbcTemplate)





new SqlParameter("in_id", Types.NUMERIC),

new SqlOutParameter("out_first_name", Types.VARCHAR),

new SqlOutParameter("out_last_name", Types.VARCHAR),

SqlOutParameter("out_birth_date", Types.DATE)





  1. Object Relational Mapping (ORM) data access

Spring advocates strikingly simple
solutions for proper resource handling, namely IoC via templating; for example
infrastructure classes with callback interfaces, or applying AOP interceptors. Spring
also offers Hibernate and JDO support, consisting of a
HibernateTemplate / JdoTemplate analogous to JdbcTemplate, a  HibernateInterceptor / JdoInterceptor, and a Hibernate / JDO transaction
manager. All the individual data access features are usable on their own but
integrate nicely with Spring’s application context concept, providing XML-based
configuration and cross-referencing of plain JavaBean instances that don’t need
to be Spring-aware. In a typical Spring application, many important objects are
JavaBeans: data access templates, data access objects (that use the templates),
transaction managers, business services (that use the data access objects and transaction
managers), web view resolvers, web controllers (that use the business
services), and so on.

Implementing Spring-based DAOs

will ensure that Session instances are properly opened and closed, and
automatically participate in transactions. The template instances are
thread-safe and reusable, they can thus be kept as instance variables of the
surrounding class. For more details
about how to access the data with HibernateTemplate, you can view my earlier
article at http://mybiblebook.spaces.live.com/blog/cns!8D4E97688DE1E193!460.entry

A callback implementation effectively can be used for any
Hibernate data access

public class ProductDaoImpl implements ProductDao {


HibernateTemplate hibernateTemplate;


    public void
setSessionFactory(SessionFactory sessionFactory) {

this.hibernateTemplate = new HibernateTemplate(sessionFactory);



    public Collection
loadProductsByCategory(final String category) throws DataAccessException {

this.hibernateTemplate.execute(new HibernateCallback() {


            public Object
doInHibernate(Session session) {

criteria = session.createCriteria(Product.class);

criteria.add(Expression.eq("category", category));








alternative to using Spring’s HibernateTemplate to implement DAOs, data
access code can also be written in a more traditional fashion, without wrapping
the Hibernate access code in a callback, while still respecting and participating
in Spring’s generic DataAccessException hierarchy. The advantage of such
direct Hibernate access code is that it allows any checked application
exception to be thrown within the data access code; contrast this to the HibernateTemplate
class which is restricted to throwing only unchecked exceptions within the
callback. Note that you can often defer the corresponding checks and the
throwing of application exceptions to after the callback, which still allows
working with HibernateTemplate.
In general, the HibernateTemplate class’ convenience methods are simpler
and more convenient for many scenarios.

public class HibernateProductDao extends HibernateDaoSupport
implements ProductDao {

    public Collection
loadProductsByCategory(String category) throws DataAccessException, MyException

        Session session =

        try {

            Query query =
session.createQuery("from test.Product product where product.category=?");

query.setString(0, category);

            List result =

            if (result ==
null) {

                throw new
MyException("No search results.");


            return result;


        catch (HibernateException
ex) {






DAOs based on plain Hibernate 3 API

3 provides a feature called "contextual Sessions", where Hibernate
itself manages one current Session per transaction. This is roughly
equivalent to Spring’s synchronization of one Hibernate Session per
transaction. A corresponding DAO implementation looks like as follows, based on
the plain Hibernate AP

public class ProductDaoImpl implements ProductDao {


    private SessionFactory


    public void
setSessionFactory(SessionFactory sessionFactory) {

this.sessionFactory = sessionFactory;



    public Collection
loadProductsByCategory(String category) {


.createQuery("from test.Product product where






above DAO follows the Dependency Injection pattern: it fits nicely into a
Spring IoC container, just like it would if coded against Spring’s HibernateTemplate.
Of course, such a DAO can also be set up in plain Java (for example, in unit
tests): simply instantiate it and call setSessionFactory(..) with
the desired factory reference.





This entry was posted in Spring. Bookmark the permalink.

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