In my previous post, I gave an example of code on how to use Spring’s JDBCTemplate to call a stored procedure and process a returned result set using the simple query.

Here’s the code to do the same with overriding Spring’s StoredProcedure class.

The advantage in this method is that using the declareParameter method can help with type checking and make it more clear to the user what expected values are.

The biggest difference to note is the syntax of the query string used in constructing the code. When calling the stored procedure (sproc) via JDBCTemplate.query(), you include the initial “CALL” and include parameters in the actual query string:

String sproc = "call PACKAGE.GET_CUSTOMER(?)";

When using StoredProcedure, you’re just passing in the sproc name; the Spring code will construct the full query as part of the processing:

String sproc = "PACKAGE.GET_CUSTOMER";

I’d recommend using JDBCTemplate.query() when the sproc is very simple - one or two parameters. Once the number of parameters grows, overriding StoredProcedure makes mapping the parameter values more clear and will help with debugging.

package com.stevideter.business.dal.spring;
 
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
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 org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlReturnResultSet;
import org.springframework.jdbc.object.StoredProcedure;
 
import com.stevideter.business.Customer;
import com.stevideter.business.ApplicationException;
 
/**
 * CustomerSpringDao provides the Spring implementation for the CustomerDao
 * @author stevi.deter@gmail.com
 * @version     %I%, %G%
 **/
 
public class CustomerSpringDao {
 
    private static final String CUSTOMER_SPROC = "PACKAGE.GET_CUSTOMER";
    private JdbcTemplate jdbcTemplate;
 
    /**
     *
    * CustomerStoredProcedure provides processing to call SPROC for getting customers
    * @author stevi.deter@gmail.com
    * @version     %I%, %G%
    *
     */
    class CustomerStoredProcedure extends StoredProcedure {
        private static final String ACCOUNTNUMBER_PARAM = "@ACCTNUM";
 
        public CustomerStoredProcedure(DataSource dataSource, String sprocName) {
            super(dataSource, sprocName);
            declareParameter(new SqlReturnResultSet("rs", new CustomerMapper()));
            declareParameter(new SqlParameter(ACCOUNTNUMBER_PARAM,
                    Types.NUMERIC));
            compile();
        }
 
        public Map execute(Long accountNumber) {
            Map inputs = new HashMap();
            inputs.put(ACCOUNTNUMBER_PARAM, accountNumber);
            return super.execute(inputs);
        }
 
    }
 
    public Customer get(Long accountNumber) throws ApplicationException {
        if (accountNumber == null) {
            throw new IllegalArgumentException();
        }
        if (getJdbcTemplate() == null) {
            throw new ApplicationException(ApplicationException.NULL_DAO);
        }
        try {
            CustomerStoredProcedure proc = new CustomerStoredProcedure(
                    getJdbcTemplate().getDataSource(), CUSTOMER_SPROC);
            Map results = proc.execute(accountNumber);
            List customers = (List) results.get("rs");
            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;
    }
 
}

Posted: 09 Mar, 2008 |
Categories: Java | Tags: | By: stevi.