The basic and most useful class from the framework is the JdbcTemplate. This call should serve to do most of your work. But should you require a bit more sophistication, the two variants of JdbcTemplate—the SimpleJdbcTemplate and NamedParameterJdbcTemplate—should provide you that.

The JdbcTemplate class provides the common aspects of database operations, such as inserting and updating data, using prepared statements, querying tables by using standard SQL queries, invoking stored procedures, and so on. It can also iterate over the ResultSet data efficiently and effectively.

The connection management is hidden from the user, and so is the resource pooling and exception management. Regarding the exceptions, one does not have to clutter the code with try-catch blocks because the database-specific exceptions are wrapped under covers by Spring’s Runtime Exceptions.

As always inline with the template design pattern, the JdbTemplate provides callback interfaces for us to implement our business logic. For example, PreparedStatementCallback is used for creating PreparedStatements, while RowCallbackHandler is where you extract the ResultSet into your domain objects. The CallableStatementCallback is used when executing a stored procedure. We will work briefly with these callbacks in the next few sections.

Before we can jump into working with the JdbcTemplate, we need to take care of a few details about its creation. First, we need to supply a DataSource to the JdbcTemplate so it can configure itself to get database access. As we have seen in the previous chapter (JMS), just as ConnectionFactory was the gateway to a JMS Provider, so is the DataSource the gateway to our Database. It supplies the connection information which is used by the JdbcTemplate to get access to the underlying databse.

You can configure the DataSource in the XML file as shown in the following code snippet. I am using an open source JDBC framework—Apache commons DBCP—for creating my datasources.

<bean id="movieDataSource" class="org.apache.commons.dbcp.BasicDataSource" 
destroy-method="close">
  <property name="driverClassName" value="${jdbc.driver}"/>
  <property name="url" value="${jdbc.url}"/>
  <property name="username" value="${jdbc.username}"/>
  <property name="password" value="${jdbc.password}"/>
</bean>

Once we have the datasource created, our next job is to create the JdbcTemplate.

We have primarily two options: First, we can instantiate a JdbcTemplate in our Data Access Object (DAO), injecting the DataSource into it. Alternatively, we can define the JdbcTemplate in the XML file, wiring the datasource to it, and then injecting the JdbcTemplate reference into the DAO class. The following snippet uses this second option:

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
  <property name="dataSource" ref="movieDataSource"/>
</bean>

<bean id="movieDataSource" class="org.apache.commons.dbcp.BasicDataSource" 
  destroy-method="close">
  ....
</bean>

The JdbcTemplate is a threadsafe object once configured, so you can inject it into any number of DAOs.

Let’s define the DAO for accessing the MOVIES database. The following snippet shows this class:

public class MovieDAO implements IMovieDAO {
  private JdbcTemplate jdbcTemplate = null;
  
  private void setJdbcTemplate(JdbcTemplate jdbcTemplate){
    this.jdbcTemplate = jdbcTemplate;
  }
  
  private JdbcTemplate getJdbcTemplate() {
    return this.jdbcTemplate;
  }
  ...
}

The IMovieDAO is the interface that has all the movie-related functionality which is shown here:

public interface IMovieDAO {
  public Movie getMovie(String id);
  public String getStars(String title);
  public List<Movie> getMovies(String sql);
  public List<Movie> getAllMovies();
  public void insertMovie(Movie m);
  public void updateMovie(Movie m);
  public void deleteMovie(String id);
  public void deleteAllMovies();
}

The MovieDAO has JdbcTemplate as a member variable. It is configured and wired with a datasource in the XML file and injected into our concrete DAO. The wiring XML file is shown here:

<bean id="movieDao" class="com.madhusudhan.jscore.data.MovieDAO">
  <property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>
 
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
  <property name="dataSource" ref="movieDataSource"/>
</bean>

<bean id="movieDataSource" class="org.apache.commons.dbcp.BasicDataSource" 
  destroy-method="close">
  ....
</bean>

Here is a snippet of the MovieDAO class.

public class MovieDAO implements IMovieDAO {
  private JdbcTemplate jdbcTemplate = null;
  
  private void setJdbcTemplate(JdbcTemplate jdbcTemplate){
    this.jdbcTemplate = jdbcTemplate;
  }
  
  private JdbcTemplate getJdbcTemplate() {
    return this.jdbcTemplate;
  }
  ...
}

That’s it! Your JdbcTemplate is configured and ready to be used straightaway. Let’s concentrate on what we can do with the template in our hand.

The simplest operation is to fetch movie actors using a criteria such as a movie title. We can write a simple SQL query like select stars from MOVIES to retrieve the movie actors.

Our MovieDAO has a method getStars() that takes a title and returns comma-separated stars list. The snippet here shows the implementation of this query:

@Override
public String getStars(String title) {
 
  String stars = getJdbcTemplate().
    queryForObject("select stars from MOVIES where title='"+ title +"'",
                String.class);
  return stars;
}

The query was executed using queryForObject method on JdbcTemplate. This method takes two parameters, a SQL query without bind variables and an expected type of the result.

The expected result is a comma-separated stars list. We can improve this query by parameterizing the query. The where clause will have a bind variable that will change on queries. It is shown in the listing here:

@Override
public String getStars(String title) {
  
  // using where clause
  String stars = getJdbcTemplate().
    queryForObject("select stars from MOVIES where title=?", 
      new Object[]{title}, String.class);

  return stars;
}

Here, ideally the second argument is passed via method arguments.

There are plenty of queryForXXX methods defined on the JdbcTemplate, such as queryForInt, queryForList, queryForMap, and so on. Please refer to the Spring Framework’s API to understand the workings of these various methods.

The previous queries returned a single piece of data, movie stars, as we have seen in that case.

How can we retrieve a single row, say a Movie object for a given an id? The JdbcTemplate’s queryForObject method comes handy for such requirement. One additional thing we need to do is to pass a RowMapper instance.

The JDBC API returns a ResultSet, and we need to map each and every column of data from the ResultSet onto our domain objects. The Spring framework eliminates this repetitious process by providing the RowMapper interface.

Simply put, RowMapper is an interface for mapping table rows to a domain object. It has one method called mapRow that should be implemented by the concrete classes.

What we need to do is implement this interface to map our table columns to a Movie object. Let’s implement a row mapper for our Movie domain object—see the snippet here:

public class MovieRowMapper implements RowMapper {
  Movie movie = null;
  public Object mapRow(ResultSet rs, int rowNum) throws SQLException{
    movie = new Movie();
    // This is where we extract data 
    // from ResultSet and set it on Movie object
   
    movie.setID(rs.getString("id"));
    movie.setTitle(rs.getString("title"));
    ...
    return movie;
  
  }
} 

Basically, the idea is to extract the relevant columns from the ResultSet and populate our Movie domain object and return it.

Now that our MovieRowMapper is implemented, use jdbcTemplate to retrieve the results.

@Override
public Movie getMovie(String id){

  String sql = "select * from MOVIES where id=?";

  return getJdbcTemplate().queryForObject(sql, 
    new Object[]{id},new MovieRowMapper());
}

The JdbcTemplate executes the query by binding the argument and invoking the MovieRowMapper with a returned ResultSet from the query.

We need to enhance this logic a bit more to get all the movies (as a list) instead of a single movie result.

We can use the same MovieRowMapper for returning all the movies. However, it should be wrapped in a RowMapperResultSetExtractor as shown here:

public List getAllMovies(){
  RowMapper mapper = new MovieRowMapper();
  String sql = "select * from MOVIES";
  
  return getJdbcTemplate().query(sql, 
    new RowMapperResultSetExtractor(mapper,10));
}

We can use the jdbcTemplate.update() method to insert, update, or delete the data. The following code shows the insertion of Movie into our database.

@Override
public void insertMovie(Movie m) {
  String sql = "insert into MOVIES (ID, TITLE, GENRE, SYNOPSIS) 
    values(?,?,?,?)";

  // The insert parameters
  Object[] params = new Object[] { m.getID(), m.getTitle(), m.getGenre(), 
m.getSynopsis() };

  // The insert parameters types
  int[] types = new int[] { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, 
Types.VARCHAR };

  // Run the query
  jdbcTemplate.update(sql, params, types);
}

The second and third parameters indicate the input values and their respective types.

Similarly, deleting a single movie from the database is straightforward:

@Override
public void deleteMovie(String id) {
  String sql = "delete from MOVIES where ID=?";
  Object[] params = new Object[] { id };
  jdbcTemplate.update(sql, params);
}

In order to delete all movies, use the following code:

@Override
public void deleteAllMovies(){
  String sql = "delete from MOVIES";
  jdbcTemplate.update(sql);
}

Calling stored procedures is also an easy thing, using the update method:

public void deleteAllMovies(){
  String sql = "call MOVIES.DELETE_ALL_MOVIES";
  jdbcTemplate.update(sql);
}

As we have noticed, the JdbcTemplate has eased our burden in accessing the database dramatically. I advise you to refer to Spring’s API for more such methods on the template class.

Now that the sessionFactory is defined, the next bit is to define HibernateTemplate. The HibernateTemplate requires a SessionFactory instance, so the following declaration wires the sessionFactory that we defined above.

<bean id="hibernateTemplate" 
  class="org.springframework.orm.hibernate.HibernateTemplate">
    <property name="sessionFactory" ref="sessionFactory"/>
</bean>

<bean id="movieDao" 
  class="com.madhusudhan.jscore.data.MovieDAO">
  <property name="hibernateTemplate" ref="hibernateTemplate"/>
</bean>

That’s all—the configuration is completed. Let’s see how we get the data from our database.

The getMovie method shown here uses the template’s load method.

public Movie getMovie(String id){  
  //Searching for a movie
  return (Movie)getHibernateTemplate().load (Movie.class, id);
}

As you can see, there’s no SQL that retrieves a movie in this method. It feels like we are working with Java objects rather than data! The load method accesses the database to load the matching row based on the id passed.

Updating a Movie is simple as well:

public void updateMovie(Movie m){
  //Updating a movie
  getHibernateTemplate().update (m);
}

As you can see, the single statement above does the job! Deleting a row is as simple as invoking the delete method.

public void deleteMovie(Movie m){
  // Deleting a movie
  getHibernateTemplate().delete (m);
}

Running queries is straightforward, too. Hibernate introduces Hibernate Query Language (HQL) for writing queries. Use find methods to execute such queries.

For example, returning a Movie based on a ID is shown here:

public Movie getMovie(String id){
  String sql="from MOVIES as movies where movies.id=?";
  // Finding a movie
  return (Movie)getHibernateTemplate().find(sql, id);
}

That’s about using Hibernate from a very high ground. I strongly advise you to pick up any Hibernate book to understand in detail.

The recomended way to use Spring with Hibernate is to use Hibernate’s API rather than Spring’s template wrapper. The subject is provided at length in my other book Just Spring Data Access, so please go through it if you have the copy. For completness, let’s browse through this style quickly.

The Hibernates’s org.hibernate.SessionFactory and org.hibernate.Session forms the central part of the Hibernate API.

Spring provides a org.springframework.orm.hibernate3.LocalSessionFactoryBean to create a wrapper around the SessionFactory. This wrapper is then injected into our DAO object.

As expected, this bean should be wired in with a DataSource, along with other Hibernate properties. The configuration is almost same as the one shown earlier:

<bean id="sessionFactory" 
  class="org.springframework.orm.hibernate3.LocalSessionFactoryBean"> 
  <property name="dataSource" ref="mySqlDataSource" /> 
  <property name="mappingResources">
     <list> 
       <value>Trade.hbm.xml</value>
     </list>
   </property>
<property name="hibernateProperties">
  <props> 
   <prop key="hibernate.show_sql">false</prop> 
   <prop key="hibernate.hbm2ddl.auto">true</prop>
   ....
  </props>
</bean>

The TradeDAO, along with the injected SessionFactory, is declared here:

public class TradeDAO {
  private SessionFactory sessionFactory = null;
  public SessionFactory getSessionFactory() {
   return sessionFactory;
  }
  public void setSessionFactory(SessionFactory sessionFactory) {
    this.sessionFactory = sessionFactory;
  }
}

Once we have the handle to the SessionFactory, we can fetch a Session out of it by using getSessionFactory().getCurrentSession() method invocation. The session is the main interface that we should be using to do the database operations. For example, in order to persist the Trade, all we need to do is to call a save method on this session. This is shown here:

public void persist(Trade t) {
  session.beginTransaction();
  session.save(t);
  session.getTransaction().commit();
}

That’s it, you are done with persisting the trade—no messy SQL or ResultSets or RowMappers!

Note that each of these database operation should be carried in a transaction, hence we are surrounding our code by beginning a transaction and commiting the same.

There are a plethora of operations that you could carry out on the Session object—refer to the API to get more insight.