Spring JDBC Stored Procedure

issues-jdbc.txt

**Custom Stored Procedure**

package com.web.helper;

import java.util.List;
import java.util.Map;

import oracle.jdbc.OracleTypes;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;

import com.domain.ViewFormDO;

public class FlexiView extends StoredProcedure {

    public FlexiView(JdbcTemplate jdbcTemplate, final String storedProc, ViewFormDO viewFormDO, int reportId){

        super(jdbcTemplate, storedProc);
        jdbcTemplate.setFetchSize(20000);

        declareParameter(new SqlOutParameter("P_CUR", OracleTypes.CURSOR, new FlexiViewMapper(reportId, viewFormDO)));
        declareParameter(new SqlParameter("P_AGENT_ID", OracleTypes.NUMBER));

        this.compile();
    }

    @SuppressWarnings("unchecked")
    public <T> List<T> executeStoredProc(final Map<String, Object> valueMap) {
        System.out.println(">>"+valueMap.size());
        // execute stored procedure
        Map<String, Object> resultMap = super.execute(valueMap);
        return (List<T>)resultMap.get("P_CUR");
    }

}

**Custom Row Mapper**

package com.web.helper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;

import org.springframework.jdbc.core.RowMapper;

import com.domain.ViewFormDO;
import com.domain.ViewFormGridDO;

public class FlexiViewMapper implements RowMapper<Object> {

    int reportId = 0;
    final SimpleDateFormat dtFormat = new SimpleDateFormat("dd-MM-yyyy");
    ViewFormDO viewFormDO;

    public FlexiViewMapper(int reportId, ViewFormDO viewFormDO){
        this.reportId = reportId;
        this.viewFormDO = viewFormDO;
    }

    @Override
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
        ViewFormGridDO flexiActiveGridDet = new ViewFormGridDO();
        if (reportId == 1001 || reportId == 1002) {
            // Get data from DB and assign it in ViewFormGridDO Bean
        }
        return flexiActiveGridDet;
    }

}

**Calling the method in Service**

@Override
public List<ViewFormGridDO> getFlexiGuideEnqGridDet1(final ViewFormDO viewFormDO)
        throws NsureException {
    List<ViewFormGridDO> flexiGuideGridDetails = null;
    final int reportId = viewFormDO.getFlexiTypeId() == GlobalNames.SCHEME_XXX ? 1002 : viewFormDO.getReportId();

    LinkedHashMap<String, Object> valueMap = new LinkedHashMap<String, Object>();
    valueMap.put("P_AGENT_ID", viewFormDO.getAgentId());

    FlexiView flexiView = new FlexiView(jdbcTemplate, "PKG_XXXXX.prGetXXXXX", viewFormDO, reportId); 
    flexiGridDetails = flexiView.executeStoredProc(valueMap);

    return flexiGridDetails;
}