XinYu_LZMES/.svn/pristine/cc/cc7521a5aea45e7e94396557551f7f0a4f6511e1.svn-base

package QCM.COMMUNAL;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;

import org.apache.commons.dbcp.BasicDataSource;

import CoreFS.SA01.CoreIComponent;
import CoreFS.SA06.CoreReturnObject;
import CoreFS.SA06.CoreSqlType;
import UIB.COM.XmlSqlParsersFactory;

/**
 * 
 * @desc   存储过程调用
 * @author meiguiping
 * @date   2010 1:17:52 PM
 */
public class ComDBProcedureQCM	extends CoreIComponent
{
	/**
	 * @desc  执行存储过程
	 * @param sqlID	xml中的SQL的ID
	 * @param ht	前台需要用hashtable传入，所有参数均存入hashmap中 ，前台必须以(I1，""),(I2，""),,(O1，""),,(O2，"")的方式存入
	 * 				I表示in，O表示out。
	 * @return CoreReturnObject
	 * @throws Exception
	 */
	public CoreReturnObject	executeProcedure(String sqlID , HashMap ht ) throws Exception
	{
		CoreReturnObject	cro					=	new CoreReturnObject();
		ArrayList  			inParam 			= 	new ArrayList();
		ArrayList 			inParamType 		= 	new ArrayList();
		
		ArrayList			aList				=	new ArrayList();
		String				key					=	"";	
		
		ArrayList			outParam			=	new ArrayList();
		ArrayList			outparamtype		=	new ArrayList();
		String[] 			str					=	new String[3];
		int 	index							=	0;
		for(Iterator it	=	ht.keySet().iterator(); it.hasNext(); )
		{
			key		=	it.next().toString();
			aList.add(key);
		}
		Collections.sort(aList);
				
		for(int i=0; i < aList.size(); i++)
		{
			if(aList.get(i).toString().toLowerCase().startsWith("i"))
			{
				inParam.add(ht.get(aList.get(i).toString()));
				inParamType.add(new Integer(CoreSqlType.CoreOracleType.STRING_TYPE.ordinal()));
			}
			else if (aList.get(i).toString().toLowerCase().startsWith("o"))
			{
				outParam.add(str[index++]);
				outparamtype.add(new Integer(CoreSqlType.CoreOracleType.STRING_TYPE.ordinal()));
			}
		}
		ht = null;//将HashMap置空，防止内存泄露
		String				sqlString	=	XmlSqlParsersFactory.getSql(sqlID).trim();
		cro = this.getDao("test1Dao").ExcuteProcedure(sqlString, inParamType , inParam , outparamtype , outParam);
		return cro;
	}
	
	/**
	 * @param sqlID
	 * @param ht
	 * @return
	 * @throws Exception
	 */
	public CoreReturnObject	doXmlProcedure(String sqlID , HashMap ht ) throws Exception
	{
		
		CoreReturnObject	cro		=	new CoreReturnObject();
		if(ht == null) return cro;
		String				sqlString	=	XmlSqlParsersFactory.getSql(sqlID).trim();
		Connection			conn		=	this.getDao("test1Dao").getConnection();
		CallableStatement 	cstm		=	conn.prepareCall(sqlString);
		
		String				key			=	"";	
 
//		BasicDataSource   ds = (BasicDataSource)this.dbproxy.getJdbcTemplate().getDataSource();
		try
		{
		String[]  outIndex	=	new String[3];//最多为3个输出
		int	j	=	0;
		for(Iterator it	=	ht.keySet().iterator(); it.hasNext(); )
		{
			key		=	it.next().toString();
			if(key.toLowerCase().startsWith("i"))
			{
				cstm.setString(Integer.parseInt(key.substring(1)), ht.get(key).toString());
			}
			else if(key.toLowerCase().startsWith("o"))
			{
				cstm.registerOutParameter(Integer.parseInt(key.substring(1)), java.sql.Types.VARCHAR);
				outIndex[j++]	=	key.substring(1);
			}
		}
		ht = null;//将HashMap置空，防止内存泄露
		if(cstm	==	null) throw new Exception("获取存储过程出错，请检查！");
		
		cstm.execute();		
		
		ArrayList	aList	=	new ArrayList();
		//输出
		for(int i = 0; i < outIndex.length; i++)
		{
			if( outIndex[i] != null)
			{
				aList.add(cstm.getString(Integer.parseInt(outIndex[i])));
//				System.out.print("************************输出：  "+cstm.getString(Integer.parseInt(outIndex[i])));
			}
		}
		cro.setResult(aList);
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			if(cstm != null)
				cstm.close();
			if(conn != null)
				conn.close();
//			System.out.println("关闭后，连接池最大连接数========================>"+ds.getMaxIdle());
//			System.out.println("关闭后，当前活动连接数#########################"+ds.getNumActive());
//			System.out.println("关闭后，当前连接池连接数#########################"+ds.getNumIdle());
		}
		return cro;
	}
}