XinYu_LZMES/.svn/pristine/97/975bbc90a9c380a23fb68340797e2678ffb8e325.svn-base

package UIB.JHY;

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

import org.apache.commons.dbcp.DelegatingConnection;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import UIB.COM.XmlSqlParsersFactory;
import CoreFS.SA01.CoreIComponent;
import CoreFS.SA06.CoreReturnObject;
import CoreFS.SA06.CoreSqlType;
import CoreFS.SA06.CoreSqlType.CoreOracleType;

/**
 * 
 * @desc   存储过程调用
 * @author meiguiping
 * @date   2010 1:17:52 PM
 */
public class JHyComCallProc	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	doCoreProc(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;
		
		//取出hashmap中的key，存储进ArrayList中
		for(Iterator it	=	ht.keySet().iterator(); it.hasNext(); )
		{
			key		=	it.next().toString();
			aList.add(key);
		}
		Collections.sort(aList);
				
		String	key_1	=	null;
		for(int i=0; i < aList.size(); i++)
		{
			key_1	=	aList.get(i).toString().toLowerCase();
			if(key_1.startsWith("i"))//输入参数
			{
				if(key_1.indexOf(":") != -1 )
				{
					ArrayList	al	=	null;
					Object obj= ht.get(aList.get(i).toString());//是否按ArrayList传递
					if(obj instanceof ArrayList)
					{
						al = (ArrayList)obj;
					}
					else //进行转换
					{
						al	=	new ArrayList();
						al.add(obj.toString());
					}
					Array ar = this.getDao("JhyDao").getArray(key_1.substring(key_1.indexOf(":")+1).toUpperCase(), al );
					inParam.add(ar);
					inParamType.add(CoreOracleType.ARRAY_TYPE.ordinal());
				}
				else//输出参数
				{
					inParam.add(ht.get(aList.get(i).toString()));
					inParamType.add(new Integer(CoreSqlType.CoreOracleType.STRING_TYPE.ordinal()));
				}
			}
			else if (key_1.startsWith("o"))
			{
				//outParam.add(str[index++]);
//				outparamtype.add(new Integer(CoreSqlType.CoreOracleType.STRING_TYPE.ordinal()));
				outparamtype.add(CoreSqlType.CoreOracleType.STRING_TYPE.ordinal());
			}
		}
		ht = null;
		String				sqlString	=	XmlSqlParsersFactory.getSql(sqlID).trim();
		cro = this.getDao("JhyDao").ExcuteProcedure(sqlString, inParamType , inParam , outparamtype , outParam);
//		cro = this.getDao("JhyDao").ExcuteProcedure(sqlString, inParamType , inParam );
		return cro;
	}
	
	/**
	 * @param sqlID
	 * @param ht
	 * @return
	 * @throws Exception
	 */
	public CoreReturnObject	doSimpleProc(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("JhyDao").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"))
				{
					if(key.toLowerCase().indexOf(":") > 0)//":"不会在字符串的最前面，判断是否包含“：”
					{
						String	arrayType	  =	    key.substring(key.indexOf(":")+1).toUpperCase();//数组类型
						ArrayDescriptor   ad  =     ArrayDescriptor.createDescriptor(arrayType,getNativeConnection(conn));
						ARRAY  	array 		  = 	new ARRAY(ad,getNativeConnection(conn), ((List)ht.get(key)).toArray()  );
						cstm.setArray(Integer.parseInt(key.substring(1,2)), array);
					}
					else
					{
						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;
			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])));
				}
			}
			cro.setResult(aList);
		}catch(Exception ex)
		{
			System.out.print("存储过程参数错误： "+ex.getMessage());
		}
		finally
		{
			if(cstm != null)
				cstm.close();
			if(conn != null)
				conn.close();
		}
		return cro;
	}
	
	private static Connection getNativeConnection(Connection con)
    throws SQLException
    {
	    if ((con instanceof DelegatingConnection)) {
	      Connection nativeCon = ((DelegatingConnection)con).getInnermostDelegate();
	      return nativeCon != null ? nativeCon : con.getMetaData().getConnection();
	    }
	    return con;
    }
	

}