java 调用存储过程实例_java调用存储过程实例

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 executeOracleStoredProcedure(String peNum){

CallableStatement cstmt = null;

ResultSet rs1=null;

ResultSet rs2=null;

String param="$per_num$="+peNum;

Map map=new HashMap();

List works=new ArrayList();

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> resultList=new ArrayList>();

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 mapObj = new HashMap();

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();

}

}

}