Example of spring callbacks used in JdbcTemplate

0

In this article, I will show you an example of JdbcTemplate and its various callbacks like PreparedStatementCallback, ResultSetExtractor, RowCallbackHandler and RowMapper.
If you want to know more about JdbcTemplate, read my previous articles on it.

Example of JdbcTemplate Callbacks

I will first show you an example of how JdbcTemplate processes query with ‘?’ placeholders.
Query is:
SELECT * FROM ARTICLES WHERE CATEGORY=? AND AUTHOR=?
Our goal would be to execute the query and get the result as a list of Article beans.

We will execute the query in different variations using spring callbacks like PreparedStatementCallback, ResultSetExtractor, RowCallbackHandler and RowMapper.

SpringJdbcTemplateQueryExample:

package com.javarticles.jdbc.jdbctemplate;

import java.sql.SQLException;
import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

public class SpringJdbcTemplateQueryExample {
    private JdbcTemplate jdbcTemplate;

    public static void main(String[] args) throws SQLException {
        ApplicationContext context = new ClassPathXmlApplicationContext(
                "applicationContext.xml");
        SpringJdbcTemplateQueryExample stmtQueryExample = (SpringJdbcTemplateQueryExample) context
                .getBean("springJtExample");
        stmtQueryExample.queryEmployee();
    }

    public void queryEmployee() throws SQLException {
        String sql = "SELECT * FROM ARTICLES WHERE CATEGORY=? AND AUTHOR=?";
        String sqlWithValues = "SELECT * FROM ARTICLES WHERE CATEGORY='spring' AND AUTHOR='Joe'";
        Object[] values = new Object[] { "spring", "Joe" };

        // PreparedStatementCallback example
        System.out.println("PreparedStatementCallback example");
        List<Article> aList = getJdbcTemplate().execute(sqlWithValues,
                new ArticleListPreparedStatement());
        System.out.println(aList);

        // ResultSetExtractor Example
        System.out.println("ResultSetExtractor example");
        aList = getJdbcTemplate()
                .query(sql, values, new ArticleListResultSet());
        System.out.println(aList);

        // RowCallbackHandler Example
        System.out.println("RowCallbackHandler example");
        ArticleRowCallbackHandler rowHandler = new ArticleRowCallbackHandler();
        getJdbcTemplate().query(sql, values, rowHandler);
        System.out.println(rowHandler.getArticleList());

        // RowMapper Example
        System.out.println("RowMapper example");
        aList = getJdbcTemplate().query(sql, values, new ArticleRowMapper());
        System.out.println(rowHandler.getArticleList());
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }
}

Output:

PreparedStatementCallback example
[JdbcTemplate Example:Joe:spring, MVC Example:Joe:spring]
ResultSetExtractor example
[JdbcTemplate Example:Joe:spring, MVC Example:Joe:spring]
RowCallbackHandler example
[JdbcTemplate Example:Joe:spring, MVC Example:Joe:spring]
RowMapper example
[JdbcTemplate Example:Joe:spring, MVC Example:Joe:spring]

Example of PreparedStatementCallback

Our example uses few callbacks, lets go through each one of them.
The first one is ArticleListPreparedStatement. Since it implements PreparedStatementCallback, JdbcTemplate takes care of creating PreparedStatement using the Connection object and the SQL but to retrieve the ResultSet, we have to explicitly call ps.executeQuery(). The utility method QueryUtils.extractArticleListFromRs(rs) takes care of converting the result set into a list of Article beans.

The callback classes delegate the data access request to a helper class QueryUtils.

QueryUtils:

package com.javarticles.jdbc.jdbctemplate;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public final class QueryUtils {
    public static Article extractArticleFromRs(ResultSet rs) throws SQLException {
        Article article = new Article();
        article.setId(rs.getInt("ID"));
        article.setName(rs.getString("NAME"));
        article.setAuthor(rs.getString("AUTHOR"));
        article.setCategory(rs.getString("CATEGORY"));
        article.setTags(rs.getString("TAGS"));
        return article;
    }
    
    public static List<Article> extractArticleListFromRs(ResultSet rs) throws SQLException {
        List<Article> articleList = new ArrayList<Article>();
        while(rs.next()) {                
            articleList.add(extractArticleFromRs(rs));
        }
        return articleList;
    }
}

ArticleListPreparedStatement:

package com.javarticles.jdbc.jdbctemplate;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.PreparedStatementCallback;

public class ArticleListPreparedStatement implements PreparedStatementCallback<List<Article>> {
    public List<Article> doInPreparedStatement(PreparedStatement ps)
            throws SQLException, DataAccessException {
        return QueryUtils.extractArticleListFromRs(ps.executeQuery());            
    }
}

Example of ResultSetExtractor

If the callback implements ResultSetExtractor then we don’t have to call execute query any more. JdbcTemplate does it for us and passes the ResultSet as parameter value to extractData.

ArticleListResultSet:

package com.javarticles.jdbc.jdbctemplate;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;

public class ArticleListResultSet implements ResultSetExtractor<List<Article>> {
    public List<Article> extractData(ResultSet rs) throws SQLException,
            DataAccessException {
        return QueryUtils.extractArticleListFromRs(rs);
    }       
}

The next two callback handlers are at row level. The first one is ArticleRowCallbackHandler, it implements RowCallbackHandler. The ResultSet object is passed in as parameter value to processRow. Each ResultSet object represents one row. If you notice processRow doesn’t return anything so you can use this callback if you want to do some processing based on each row data.
In case you want to simply convert the row into a bean, you should use RowMapper.

Example of RowCallbackHandler

ArticleRowCallbackHandler:

package com.javarticles.jdbc.jdbctemplate;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.RowCallbackHandler;

public class ArticleRowCallbackHandler implements RowCallbackHandler {
    private List<Article> aList;
    
    public ArticleRowCallbackHandler() {
        aList = new ArrayList<Article>();
    }

    public void processRow(ResultSet rs) throws SQLException {
        aList.add(QueryUtils.extractArticleFromRs(rs));
    }
    
    public List<Article> getArticleList() {
        return aList;
    }        
}

Example of RowMapper

Our last callback implements RowMapper, it is used to convert ResultSet row into a different object representation. The ResultSet object passed into method mapRow represents a row so you should not call rs.next(). Unlike processRow method, mapRow returns an Object so you should use this callback if you want to map your database row to an Object. In our query API, the Object returned by mapRow automatically get added to the list that method query returns.
ArticleRowMapper:

package com.javarticles.jdbc.jdbctemplate;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class ArticleRowMapper implements RowMapper<Article> {
    public Article mapRow(ResultSet rs, int rowNum) throws SQLException {
        return QueryUtils.extractArticleFromRs(rs);
    }       
}

Download Source Code

In this article, I have shown you examples of various spring callbacks used in JdbcTemplate. You can download the source code here: springJdbcTemplateCallbacksQuery

Share.

Leave A Reply