Tag Archives: stored procedure

Processing Stored Procedure result sets in Spring

9 Replies

Recently, I was using Spring‘s JDBCTemplate for my Data Access Layer (DAL). I had a stored procedure that returned a result set, and had to spend quite a bit of search time finding an example of how to process it.

I managed to work it out using both the method of overriding Spring’s StoredProcedure and using the simple JDBCTemplate.query(sql, args, rowMapper). I found the latter to be simpler, since it doesn’t involve a inner class, etc., so here’s a complete example for anyone else who might need to try. Note the assumption that this is a Spring-managed application, so the dataSource is being injected.

This works as early as Spring 1.2.9 and Java 1.3.

package com.stevideter.business.dal.spring;
 
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
 
import javax.sql.DataSource;
 
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
 
import com.stevideter.business.Customer;
import com.stevideter.business.ApplicationException;
 
/**
 * CustomerSpringDao ...
 * @author stevi.deter@gmail.com
 * @version     %I%, %G%
 * @since       1.0
 **/
 
public class CustomerSpringDao {
 
    private JdbcTemplate jdbcTemplate;
 
    public Customer get(Long accountNumber) throws ApplicationException {
        if (accountNumber == null) {
            throw new IllegalArgumentException();
        }
        if (getJdbcTemplate() == null) {
            throw new ApplicationException(ApplicationException.NULL_DAO);
        }
        try {
            List customers = getJdbcTemplate().query(
                    "call PACKAGE.GET_CUSTOMER(?)",
                    new Object[] { accountNumber }, new CustomerMapper());
            if (CollectionUtils.isNotEmpty(customers)) {
                // return the first one
                return (Customer)customers.get(0);
            }
            return null;
        } catch (DataAccessException ex) {
            throw new ApplicationException(ApplicationException.DATA_ACCESS_ERROR);
        }
    }
 
    /**
    * CustomerMapper processes a ResultSet to populate a Customer object
    * @author stevi.deter@gmail.com
    * @version     %I%, %G%
    * @since       1.0
    *
     */
    protected static final class CustomerMapper implements RowMapper {
 
        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            Customer customer = new Customer();
            customer.setAccountNumber(new Long(rs.getLong("ACCOUNTNUMBER")));
            customer.setLastName(StringUtils.trimToNull(rs.getString("LASTNAME")));
            customer.setFirstName(StringUtils.trimToNull(rs.getString("FIRSTNAME")));
            return customer;
        }
    }
    /**
     * Spring DI for the datasource. instantiates the JdbcTemplate
     * @param dataSource
     */
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    /**
     * @return the jdbcTemplate
     */
    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }
}