How to use spring-jdbc from the springframework?

15 Dec 2009 at 00:00:00 - 4 comment(s)

In my previous post we have seen how to create a webapp. Today we will see how to use spring-jdbc which is the Spring Framework's JDBC abstraction framework. I invite you to check the documentation for spring jdbc. Thanks to this you will be able to connect your application or webapp to a database (I'll use the webapp from my previous post here). In my example I will use mysql but it is really easy to use any other database.

Add dependencies

As in the previous post, I use maven. You'll need to have dependencies for commons-collections, commons-dbcp, mysql and spring-jdbc so in your pom file you have:


If we have commons-dbcp and commons-collections, it's because we want to have a connection pool. Indeed, creating a new connection for each user can be time consuming (often requiring multiple seconds of clock time), in order to perform a database transaction that might take milliseconds. In my experience commons-dbcp is a very good choice to get a database connection pool.

Spring beans

Now that we have all the dependencies we need we'll create the spring beans which we'll inject using the AutoWired annotation (by the way, it's a good idea to read the documentation this link lead to).

The bean to create the datasource:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
      <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
      <property name="url" value="jdbc:mysql://localhost:3306/mydb?autoReconnect=true" />
        <property name="username" value="myuser" />
    <property name="password" value="mypassword" />
      <property name="initialSize" value="5"/>
      <property name="validationQuery" value="select 1" />
      <property name="defaultAutoCommit" value="false" />
      <property name="maxActive" value="10" />
      <property name="maxIdle" value="10" />
      <property name="maxWait" value="15000" />
      <property name="removeAbandoned" value="true" />
      <property name="removeAbandonedTimeout" value="300" />
      <property name="testOnBorrow" value="true" />

Check the javadoc of BasicDataSource if you want to know more about this.

The bean to create the JdbcTemplate:

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <constructor-arg><ref bean="dataSource" /></constructor-arg>

Use JdbcTemplate

In my previous post we add, let's use it to see what we can do with JdbcTemplate.

public class Application {

  private JdbcTemplate template;
  public ModelAndView home(HttpServletRequest request, HttpServletResponse response) {
    final List<Post> posts = new ArrayList<Post>();
    template.query("SELECT * FROM Post LIMIT 5", new RowCallbackHandler() {
      public void processRow(ResultSet rs) throws SQLException {
        Post p = new Post();

    Post post = template.queryForObject("SELECT * FROM Post WHERE id=?", new RowMapper<Post>() {
      public Post mapRow(ResultSet rs, int arg1) throws SQLException {
        Post p = new Post();
        return p;
    Map<String,Object> model = new HashMap<String,Object>();
    return new ModelAndView("home",model);

Post is a class I have created. You can see here how I make use of the RowCallbackHandler to create a List and how I use RowMapper to get one Post. I then add these objects to the ModelAndView. Not really great code there but it's easier for me to show you in one class. You are able to download the project at the end of this post, I organized it a bit better. Thanks to this we'll be able to display the List. Let's see how.

A little bit of JSTL

To display the List, you would have in your jsp:

<c:forEach var="p" items="${posts}">
  ${} - ${p.title}


That's it. In this post you learned how to create and use a database connection pool and how to use JdbcTemplate. We've seen as well AutoWired and a little bit of jstl.

Click here to download the code related to this post


Notify me of follow up comments