/**
 * <p>Title:</p>
 * <p>Description:</p>
 * <p>Copyright: Copyright (C) 2011 </p>
 * <p>Company: VCI </p>
 * @author Bear
 * @time 2011-8-22
 * @version 1.0
 */
package com.vci.server.base.persistence.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.LinkedHashMap;

import com.vci.common.log.ServerWithLog4j;


public class JDBCTemplate {

	private Object res = null;
	private Connection cn = null;
	private ResultSet rst = null;
	private CallableStatement cst = null;
	private String sql = "";
	private JDBCCallback callback = null;
	
	/**
	 * 执行存储过程或函数
	 * @param callback 回调对象
	 * @return
	 * @throws Exception
	 */
	public Object run(JDBCCallback callback) throws JDBCException{
		this.callback = callback;
		if(callback == null){
			return res;
		}
		try{
			cn = callback.getCn();
			if(cn == null){
				return res;
			}
			sql = callback.getSql();
			cst = cn.prepareCall(sql);
			Object[] params = callback.getParams();
			JDBCRunType runType = callback.getRunType();
			int i = 1;
			// 函数的第一个参数是返回值
			if(runType == JDBCRunType.Function){
				cst.registerOutParameter(1, callback.getReturnSqlType());
				i = 2;
			}
			// 依次设置参数值
			if(params != null){
				for(Object val : params){
					cst.setObject(i++, val);
				}
			}
			int refOutParamIndex = i;
			// 设置引用输出游标参数
			if(runType == JDBCRunType.Procedure && callback.isHasRefCursorOut()){
//				oracle.jdbc.OracleTypes.CURSOR = -10
				cst.registerOutParameter(refOutParamIndex, -10);
				// add by xchao 2012.12.13 完善存储过程对输出参数的支持
				registerOutParameter();
			}
			// 数据处理
			// 是函数时，直接取函数的返回值作为当前函数的返回值
			if(runType == JDBCRunType.Function){
				// add by xchao 2012.12.13 完善存储过程对输出参数的支持
				registerOutParameter();
				// 执行
				rst = cst.executeQuery();
				res = cst.getObject(1);
				// add by xchao 2012.12.13 完善存储过程对输出参数的支持
				readOutParamValueToMap();
			}
			// 是存储过程中时，如果有引用游标输出，则调用回调处理，否则
			else if(runType == JDBCRunType.Procedure){
				// 执行
				cst.execute();
				// add by xchao 2012.12.13 完善存储过程对输出参数的支持
				readOutParamValueToMap();
				if(callback.isHasRefCursorOut()){
					rst = (ResultSet)cst.getObject(refOutParamIndex);
					res = callback.execute(rst);
				}
			} else if(runType == JDBCRunType.SQL){
				rst = cst.executeQuery();
				res = callback.execute(rst);
			}
//			cn.commit();
		}catch(SQLException e){
			e.printStackTrace();
			try {
				cn.rollback();
			} catch (Throwable t) {
			}
			ServerWithLog4j.logger.error("↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓");
			ServerWithLog4j.logger.error(e);
			ServerWithLog4j.logger.error(e.getClass().getName() + ":StackTrace", e);
			ServerWithLog4j.logger.error("↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑");
			throw new JDBCException(e);
		}finally{
			try{
				if(rst != null) rst.close();
				if(cst != null) cst.close();
				// 根据参数选项，决定要不要关闭连接对象
				// 如果此连接来自于与Session连接池中的Connection，则不应该关闭连接
				// 如果此连接来自与DB创建的新连接（无连接池或不是从连接池中取出的连接），则需要即时关闭
				// 对于在启用了Hiberate的应用中，不需要即时关闭连接
				if(callback.isCloseConnectionInFinally()){
					if(cn != null) cn.close();
				}
			}catch(SQLException e){
				throw new RuntimeException(e);
			}
		}
		return res;
	}
	
	/**
	 * 设置输出参数、类型
	 * @throws SQLException
	 */
	private void registerOutParameter() throws SQLException{
		// add by xchao 2012.12.13 完善存储过程对输出参数的支持
		if(callback.getOutParamTypeMaps() != null){
			Iterator<Integer> keys = callback.getOutParamTypeMaps().keySet().iterator();
			while(keys.hasNext()){
				Integer key = keys.next();
				cst.registerOutParameter(key, callback.getOutParamTypeMaps().get(key));
			}
		}
	}
	/**
	 * 读取输出参数数据值
	 * @throws SQLException
	 */
	private void readOutParamValueToMap() throws SQLException{
		// add by xchao 2012.12.13 完善存储过程对输出参数的支持
		if(callback.getOutParamTypeMaps() != null){
			LinkedHashMap<Integer, Object> outParamValueMaps = new LinkedHashMap<Integer, Object>();
			Iterator<Integer> keys = callback.getOutParamTypeMaps().keySet().iterator();
			while(keys.hasNext()){
				Integer key = keys.next();
				Object value = cst.getObject(key);
				outParamValueMaps.put(key, value);
			}
			callback.setOutParamValueMaps(outParamValueMaps);
		}
	}
}