Moving the Curve

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

String sproc = "PACKAGE.GET_CUSTOMER";

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;
    }
 
}