java調(diào)用存儲過程實例

package com.xxx.srm.sourcing.service.impl;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;


import com.framework.common.util.StringUtil;
@Service
public class QuarterPeDao {
    @Autowired
    private SqlSessionFactoryBean sqlSessionFactory;
    public Map<String,Object> executeOracleStoredProcedure(String peNum){
        CallableStatement cstmt = null;
        ResultSet rs1=null;
        ResultSet rs2=null;
        String param="$per_num$="+peNum;
        Map<String,Object> map=new HashMap<String,Object>();
        List<String> works=new ArrayList<String>();
        float count=0;
        DecimalFormat df = new DecimalFormat("0.00");
        try {
            SqlSessionFactory factory=  sqlSessionFactory.getObject();
            SqlSession session=factory.openSession();
            Connection  connect=session.getConnection();
            cstmt = connect.prepareCall("{CALL pkg_public_int.get_data(?,?,?,?,?)}");
            cstmt.setString(1, "PE_INIT");
            cstmt.setString(2, param);
            cstmt.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);
            cstmt.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);
            cstmt.registerOutParameter(5, oracle.jdbc.OracleTypes.FLOAT);
            cstmt.execute();
            rs1=(ResultSet) cstmt.getObject(3);
            rs2=(ResultSet) cstmt.getObject(4);
            count=cstmt.getFloat(5);

            
            List<Map<String,Object>> resultList=new ArrayList<Map<String,Object>>();
            while(rs2.next()){
                String site=rs2.getString(1);
                works.add(site);
            }
            String[] header = new String[works.size()];
            header=works.toArray(header);
                while (rs1.next()) {
                    Map<String, Object> mapObj = new HashMap<String, Object>();
                    mapObj.put("companyCode", rs1.getString("COMPANY_CODE"));
                    mapObj.put("itemCode",rs1.getString("ITEMGROUPOUT_CODE"));
                    mapObj.put("vendorCode", rs1.getString("VENDOR_CODE"));
                    mapObj.put("vendorName", rs1.getString("VENDOR_NAME"));
                    mapObj.put("year", rs1.getString("YEAR"));
                    mapObj.put("quarter", rs1.getString("QUARTER"));
                    for(int j = 0; j < header.length; j++){
                        String qty = rs1.getString("QTY_" + header[j])+"";
                        String je = rs1.getString("JE_" + header[j]);
                        if (!StringUtil.isEmpty(qty)) {
                            qty = df.format(Double.parseDouble(qty)) + "%";
                        }
                        if (!StringUtil.isEmpty(je)) {
                            je = df.format(Double.parseDouble(je)) + "%";
                        }
                        mapObj.put("qty"+header[j], qty);
                        mapObj.put("je"+header[j], je);
                        mapObj.put("fqty"+header[j], rs1.getString("FQTY_" + header[j]));
                        mapObj.put("fje"+header[j], rs1.getString("FJE_" + header[j]));
                    }
                    resultList.add(mapObj);
                }
                map.put("works", header);
                map.put("list", resultList);
                map.put("count", count);
        } 
        catch (Exception e) {
            e.printStackTrace();
        }finally{
            close(cstmt);
        }
        return map;
    }

    
    public void close(Statement stat)
    {
        try
        {
            if (stat != null)
            {
                stat.close();
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
}