Spring JDBC Template – A leaner alternative to fat Hibernate

Objective

This post will show you how to use Spring JDBC Template to perform real world CRUD operations. I hope I can show you that you do not need overweight Hibernate to interact with database of your choice.

The Problem Domain

Almost all application I have coded needed to store and get data of some sort. In most cases it was relational database. With hibernate being all the rage these days I have used it in multiple projects with varying degree of problems and success. In my personal opinion hibernate tries to solve a problem that really is not there. If you can not wire SQL you should not be using Hibernate or be doing any software development.

I advise the use of Spring JDBC templates. In essence Spring JDBC Template will offload all the mundane steps of database access from you shoulders only requiring you to write SQL and move data from result set into your model objects. Here is how Spring documentation describes responsibilities:

Action Spring You
Define connection parameters. X
Open the connection. X  
Specify the SQL statement.   X
Declare parameters and provide parameter values   X
Prepare and execute the statement. X  
Set up the loop to iterate through the results (if any). X  
Do the work for each iteration.   X
Process any exception. X  
Handle transactions. X  
Close the connection, statement and resultset. X  

CRUD Example with Spring JDBC Template

For this example you will need spring-core, spring-tx and spring-aop components. We will use spring-tx to manage the transaction so we can get back the auto generated IDs of the persisted objects and aop to set up the transaction management. Here is meat for spring configuration file:

<bean id="propertyPlaceholder" 
      class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer" >
  <property name="location" value="/WEB-INF/config.properties" />
</bean>

<bean id="mysqlDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" 
      destroy-method="close">
  <property name="driverClass" value="com.mysql.jdbc.Driver"/>
  <property name="jdbcUrl" value="${database.url}"/>
  <property name="user" value="${database.user}"/>
  <property name="password" value="${database.password}"/>
  <property name="initialPoolSize" value="${database.connections.start}"/>
  <property name="maxPoolSize" value="${database.connections.max}"/>  
  <property name="idleConnectionTestPeriod" value="270" />
</bean>

<bean id="txManager" 
      class="org.springframework.jdbc.datasource.DataSourceTransactionManager" >
  <property name="dataSource" ref="mysqlDataSource" />
</bean>

<tx:advice id="txAdvice" transaction-manager="txManager">
  <tx:attributes>
    <!-- all methods starting with 'get' are read-only -->
    <tx:method name="get*" read-only="true"/>
    <tx:method name="list*" read-only="true"/>
      <!-- other methods use the default transaction settings (see below) -->
      <tx:method name="*"/>
    </tx:attributes>
</tx:advice>

<aop:config>
  <aop:pointcut id="uOwnerDataSvcOperation" 
      expression="execution(* com.es.rto.dao.UOwnerDataServiceImpl.*(..))"/>
  <aop:advisor advice-ref="txAdvice" pointcut-ref="uOwnerDataSvcOperation"/>
</aop:config>

<bean id="namedTemplate" 
    class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
  <constructor-arg index="0" ref="mysqlDataSource"/>   
</bean>

<bean id="qandADao" class="com.es.rto.dao.QandADao" >
  <property name="jdbcTemplate" ref="namedTemplate" />
</bean>

<bean id="uOwnerDataSvc" class="com.es.rto.dao.UOwnerDataServiceImpl" >
  <property name="qandADao" ref="qandADao" />
</bean>

First thing we do we load our config.properties file where we have database configuration defined. Second we define a pooled data source to connect to mysql database. Third bean is a transaction manager that is watching our data source from the step two. Next we set up our transaction manager. We instruct transaction manager that methods starting with “get” and “list” are read only. This means that they will not be subject to transaction management. Then we define named jdbc template and pass in the data source as first constructor argument and then pass in the template into our own DAO bean.

Why do we need Transaction Management?

When we create a new row in a mysql table with autoincrement id value we need a way to get that auto generated id. To do so we need to query the database using same connection. When we use jdbcTemplate.update() or jdbcTemplate.query() it will check out a connection form the pool execute the query, process the results and return this connection to the pool. So if we do update() and then get() to query “SELECT LAST_INSERT_ID()” we will get another connection, which is a problem since we need to do this on the same connection to get the right id. This is where the transaction comes in. Instead of releasing connection back to the pool spring will hold on to it until we are done with it.

Java code side of the Spring JDBC template

We will use 3 beans to illustrate the scenario: question, answer and user.

public class Question {
	private long questionId;
	private long categoryId;
	private long userId;
	private long areaId;
	private String question;
	private String verifyKey;
	private Date created;
	private User user;
	private List<Answer> answers;
	private long answerCount;
	private String name;
	// getters and setters omited...
}
public class Answer {
	private long answerId;
	private long questionId;
	private long userId;
	private String answer;
	private String verifyKey;
	private String url;
	private Date created;
	private User user;
	private String name;
	// getters and setters omited...
}
public class User implements Serializable {
	private long id;
	private String email;
	private String retypeE;
	private String name;
	private String password;
	private String password2;
	private String verifyKey;
	private boolean buyer;
	private boolean seller;
	private boolean active;
	private boolean privacy;
	private int pageSize = 20;
	private String next;
	private boolean administrator;
	// getters and setters omited...
}

Here are the 2 classes that do all the work:

public class QandADao {

  private final Logger logger = Logger.getLogger(QandADao.class);
  protected NamedParameterJdbcTemplate jdbcTemplate = null;
  

  @SuppressWarnings("unchecked")
  public List<Question> getQuestions( String search, int page, int step ) {
    try {
      MapSqlParameterSource params = new MapSqlParameterSource( "search", "%"+search+"%" );
      String sql = "SELECT q.*, count( a.answer_id ) as answers, u.name, u.seller  FROM users u, questions q LEFT JOIN answers a "+
      "ON  a.question_id = q.question_id WHERE a.answer LIKE :search AND q.user_id = u.id "+
      "GROUP BY q.question_id, q.category_id, q.user_id, q.question, q.created, q.area_id, u.name, u.seller "+  
      "UNION "+
      "SELECT q.*, count( a.answer_id ) as answers, u.name, u.seller  FROM users u, questions q LEFT JOIN answers a "+ 
      "ON  a.question_id = q.question_id WHERE q.question LIKE :search AND q.user_id = u.id "+
      "GROUP BY q.question_id, q.category_id, q.user_id, q.question, q.created, q.area_id, u.name, u.seller "+
      "LIMIT "+(step*(page-1))+", "+step;
      logger.debug( sql );
      return jdbcTemplate.query(sql, params, new RowMappers.QuestionsMapper());
    } catch ( DataAccessException exc ) {
      logger.error("FAILED to get Question List", exc);
      return new ArrayList<Question>();
    }
  }

  public Question saveQuestion( Question question ) {
    try {
      BeanPropertySqlParameterSource namedParameters = new BeanPropertySqlParameterSource(question);
      String sql;
      if( question.getQuestionId() == 0 )
        sql = "INSERT INTO questions ( category_id, user_id, question, area_id, uname ) VALUES ( :categoryId, :userId, :question, :areaId, :name )";
      else
        sql = "UPDATE questions SET question=:question, category_id=:categoryId, area_id=:areaId WHERE question_id=:questionId";
      logger.debug( sql );
      jdbcTemplate.update(sql, namedParameters);
      if( question.getQuestionId() == 0 )
        question.setQuestionId( this.getInsertedID() );
        
    } catch ( DataAccessException exc ) {
      logger.error("SAVE answer FAILED!", exc);
      throw exc;
    }
    return question;
  }

  protected long getInsertedID() {
    return jdbcTemplate.queryForLong( "SELECT LAST_INSERT_ID()" , new MapSqlParameterSource());
  }
  
}
public class RowMappers {

  public static final class QuestionMapper implements RowMapper {
      public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
        Question q = new Question();
        q.setQuestionId(rs.getLong( "question_id" ));
        q.setCategoryId(rs.getLong( "category_id" ));
        q.setUserId(rs.getLong( "user_id" ));
        q.setAreaId( rs.getLong("area_id"));
        q.setQuestion( rs.getString("question"));
        q.setCreated( rs.getTimestamp( "created" ) );
        q.setName( rs.getString("uname") );
        User u = new User();
        u.setName( rs.getString("name") );
        u.setSeller( rs.getBoolean("seller") );
        u.setId(q.getUserId());
        q.setUser(u);
      return q;
    }
  } 
}

So what is going on here? Lets look at the QuestionMapper. It implements one method – mapRow. Its hob is to process a single row of the result set. As you see here we create a Question bean and a User bean which is included in the Questions bean. It can not be more strait forward then this.

QandADao is the class where all the magic happens. First lets examine the getQuestions() method. First we define query parameter(s) using MapSqlParameterSource class. We simply put the values in the map and jdbc template will pull them out by the key. Next we write our sql query. In this case iti is pretty complex to pull out any questions where either question or the answer have the thing we are searching for. We use union to be able to pull out the questions without the answers. Lastly we call query method on the jdbc template and we pass in our query, parameters and the row mapper instance. Spring takes care of all the plumbing. What we get out of this call is a list of Question objects.

saveQuestion() method is not much different. In this case we use BeanPropertySqlParameterSource which maps bean properties to sql parameters. If you define a sql parameter “name” your bean should have “getName()” method. Instead of calling query() we call update() on the jdbc template which only takes sql and the parameters.

Conclusion

You may have noticed that the effort to use the Spring approach to JDCB is very simple, flexible and powerful. You have all the control in all the right places! You write the sql with all the performance hints and tweaks, you write code how to transfer results to the domain objects the way you need it or like it. I only mentioned about one tenth of the options you have with spring. Read The Fine Manual (RTFM) to explore the full potential.

31 thoughts on “Spring JDBC Template – A leaner alternative to fat Hibernate

  1. Hi Tomas,

    I’ve tried your approach but keep getting “Unable to convert between org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource and JAVA_OBJECT” ERROR. I’ve googled for possible causes but have been unable to find out why I’m getting this error. Have you experience this before?

  2. Sounds like you are passing in your java object into jdbcTemplate’s execute/query method instead of BeanPropertySqlParameterSource. Can you post your code to execute the sql query?

  3. I’m considering writing an article called “Why Hibernate sucks and Spring stinks and you must use Servlets 3.0 and JDBC”.

    But, anyway, Hibernate is bullshit. Each and everyone of us who has been forced to use it to get our checks paid knows it.

  4. Both sucks.

    Hibernate is a waste of time due to debugging and finding out whats going wrong and your solution is a step backward and a joke if you are working with objects anyway. My recommendation is to use PHP – you are faster and dont waste 90% of your time on debugging Java and getting the infrastructure to work…

  5. I had some problems with Hibernate and JPA too.
    I like SQL and transactions. They aren’t problems.
    I like OO too, so i’m tring to do something different about:

    My Little JAVA ORM here:
    http://www.codigorapido.com.br/testeOrm.zip
    the source are very simple to understand.
    it is just a draft, but is working. I wanna improve it.

    If someone like the idea, please send me a e-mail with the subject “I LIKE YOUR ORM” to
    luiz-unb@bol.com.br

    I’m looking for friends to make something different.

  6. Hi there. A great overview!
    I got to work with c3p0 but so far only setting it up from the java class.
    I use the Spring JDBC layer but so far couldn’t manage to use an XML configuration for Spring.

    I search over the internet for that, but nobody seems to mention how to setup this.
    I use spring.framework jars only including them to my classpath so nothing is generated automatically with any applicationContext.xml or anything.

    Thanks in advance.

    1. If you are not running a web application, you can init spring application context like this in your main() method:

      ApplicationContext context = new ClassPathXmlApplicationContext(new String[] {“applicationContext.xml”, “applicationContext-part2.xml”});

      or

      ApplicationContext context = new FileSystemXmlApplicationContext(new String[] {“applicationContext.xml”, “applicationContext-part2.xml”});

      Tomas

  7. It is actually a web application using RESTful architecture and I am a bit confused where exactly to put that line to specify the Application Context. I have several classes for different services. It definitely can’t be in one, but which :/

  8. It is fine, I got it up and running. Thanks a lot!!! But now all the beans are loaded when a service is called and it can be seen visually that the first call is waaay slower than the next ones. (everything is created at that time)
    Sorry if the questions are noobish, but i’m kinda new to this. Thanks.

  9. You need to use spring specific jersey servlet 😉
    Here is what I have used in the past:

    <context-param>
    <param-name>contextConfigLocation</param-name>
    <param-value>classpath:applicationContext.xml</param-value>
    </context-param>

    <listener>
    <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>

    <servlet>
    <servlet-name>archetype-api</servlet-name>
    <servlet-class>com.sun.jersey.spi.spring.container.servlet.SpringServlet</servlet-class>
    <init-param>
    <param-name>com.sun.jersey.config.property.packages</param-name>
    <param-value>Your.package.name.for.rest.resources</param-value>
    </init-param>
    <init-param>
    <param-name>com.sun.jersey.api.json.POJOMappingFeature</param-name>
    <param-value>true</param-value>
    </init-param>
    <load-on-startup>1</load-on-startup>
    </servlet>

    all spring beans where defined in applicationContext.xml found on the classpath. I used Json pojo marshaling feature.

  10. Very nice :)) When I use json.POJO Mapping . I get this error : http://stackoverflow.com/questions/5161466/how-do-i-use-the-jersey-json-pojo-support , so I commented that part out. It maps all my pojos to json perfectly fine without it. So I don’t know why exactly I need it.
    When I load the beans from applicationContext.xml they usually print out some of the things they do in the tomcat console (like when c3p0 created its connection pool). But this doesn’t happen. Hm?
    And to actually use the beans I also created a common class like that.

    public class Common {

    private static ApplicationContext context;

    public static ApplicationContext getContext() {
    if (context == null) {
    context = new FileSystemXmlApplicationContext(
    “classpath:applicationContext.xml”);
    return context;
    }
    return context;
    }
    }

    which actually seems to create the beans when from the applicationContext when i call it.
    I would really like even the first call of my webservice to use those already created beans.
    Am I doing something wrong? 🙂
    Thanks a lot for the help!

  11. Thank you for your article and expressing your beliefs openly. To be honest, I thought I was the only one who thought this way for Hibernate. I do not see the advantages; in additional to the extra time required trying to debug a production problem, especially when you where not the individual who wrote the program in the beginning.

  12. At least now I know I am normal. The powers that are think that Hibernate is thee solution. Despite the learning curve, debugging pains, there are also issues with using Hibernate with legacy systems – anyone experienced those will know what I am talking about.

    I honestly do not think Hibernate is worth it. My votes go to JDBC Templates and iBatis. I have achieved what Hibernate tries to address using JDBC template – with minimal effort and headaches:

    Address address = new Address();
    address.setAddressId(rs.getString(“id”));

    AddressType addressType = new AddressTypeDaoImpl().find(rs.getString(“address_type”));
    address.setAddressTpe(addressType);

    address.setCode(rs.getString(“code”));
    address.setDateCreated(rs.getTimestamp(“date_created”));

    I prefer to do my own object graphs in the DAO layer. And that works regardless of project size.

  13. i have to use multiple datasource in a single dao. i tried implementing the same by defining two jdbc template but the same is giving error. anyone please explain

  14. Hi

    please help me iam new to hibernate technlogy can you please tell me the process how can we convert jdbc template to hibernate query
    for example
    “siteIdList.removeAll(jdbcTemplate.queryForList(sql,String.class));”
    consider the above jdbc template how can we convert this into hibernate
    Thanks

  15. CONVERT THIS TO HIBERNATE QUERY:
    String sql=”g.converteddate = “+getDateAsString(searchData.getFromDate());
    System.out.println(“Query for search: “+sql);
    System.out.println(“SiteID: “+searchData.getSiteId());
    list=jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(ThirdProtocolData.class));
    }catch (Exception e) {
    e.printStackTrace();
    }finally{
    if(searchData!=null)
    searchData=null;
    }
    System.out.println(list);
    return list;

    }private String getDateAsString(String toDate) {
    // TODO Auto-generated method stub
    return null;

  16. I advise the use of Spring JDBC templates. In essence Spring JDBC Template will offload all the mundane steps of database access from you shoulders only requiring you to write SQL and move data from result set into your model objects.
    Thanks for the post and the nice example. I notice you used a rather complex query with a UNION and three tables, I shudder to think of the amount of code and configuration required to do that in Hibernate. Funny with the rise of powerful languages like JavaScript that we’ve taken a new look at an old, but equally powerful language called SQL. I think Pareto’s Law fits nicely here, Spring JDBC gets me 80% of what I need, and gets out of my way for the remaining 20%. I would imagine there is a use case for something like Hibernate but in years of coding, have never hit. My thought is it may fit where you have a really lousy DB design perhaps, although I’d say, in this case, fix a broken window, and fix the DB design, don’t create another problem by attempting to solve poor DB design with another problem

  17. Is it (jdbc template) much faster? much less memory consuming way of accessing data than Spring-Data-Jpa (with hibernate i suppose) ?

Leave a Reply

Your email address will not be published. Required fields are marked *