package com.nms.server.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.nms.server.common.Constants;
import com.nms.server.dao.OracleToMysql;

/**
 * JDBC操作数据库工具类
 * 
 * @date Jul 20, 2011 2:27:04 PM
 * @author ZhangGang
 * 
 */
public class ConnectionOracle {
	private Logger logger = Logger.getLogger(ConnectionOracle.class);
	private Connection conn = null;
	private Statement sta = null;
	private PreparedStatement infoStmt = null;//detection_info表对应的preparedStatementstatement
	private PreparedStatement warningStmt = null;//warning表对应的preparedStatementstatement
	private PreparedStatement emailStmt = null;//email表对应的preparedStatementstatement
	private PreparedStatement statusChangeTimeStmt = null;//new表对应的preparedStatementstatement
	private PreparedStatement netStmt = null;//特殊格式追加,net表对应的preparedStatementstatement
	private PreparedStatement diskStmt = null;//特殊格式追加信息,disk表对应的preparedStatementstatement
	private PreparedStatement detailStmt = null;//detail表对应的preparedStatementstatement
	/**
	 * 以下 记录 当前有多少 insert 
	 */
	private int infoCount= 0;
	private int warningCount= 0;
	private int emailCount= 0;
	private int statusChangeTimeCount= 0;
	private int netCount= 0;
	private int diskCount= 0;
	private int detailCount= 0;
	
	
	private PreparedStatement ps = null;
	private ArrayList<PreparedStatement> psList = new ArrayList<PreparedStatement>();
	private ResultSet dbResultSet = null; // 操作 select 结果集
	
	private SimpleDateFormat format2 = new SimpleDateFormat(Constants.COMMON_DATE_FORMAT);		//Java Date 类型数据格式化格式

	/**
	 * 构造方法实现 加载驱动，创建连接
	 */
	protected ConnectionOracle() throws SQLException{
		try
		{
			conn = DbPool.getConnection();
			/*if(Constants.IS_MYSQL){
				setSqlMode(conn);
			}*/
		} catch (SQLException e)
		{
			logger.error("Getting a database connection failure");
			throw e;
		}catch (Exception e){
			logger.error("Getting a database connection failure");
		}
		if(conn == null){
			throw new SQLException("Getting a database connection failure");
		}else{
			logger.debug("获得数据库连接");
		}
		
	}
	
	
	public void setSqlMode(Connection conn){
		try {
			PreparedStatement 	stmt = conn.prepareStatement("set @@sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'");
			stmt.executeUpdate();
		} catch (Exception e) {
			logger.error("Setting sql-mode exceptions",e);
		}
	}
	
	public boolean isConnClosed() throws SQLException{
		return (conn==null)||(conn.isClosed());
	}

	/**
	 * 使用Statement对象 实现select操作 注意：在selectSql中的字段和fields中的个数、名称要保持一致
	 * 
	 * @param selectSql
	 *            用于执行的select语句
	 * @param fields
	 *            要选择的字段
	 * @return 含有Map的ArrayList，一条记录形成一个Map
	 */
	public ArrayList<Map<String, String>> dbSelect(String selectSql,
			ArrayList<String> fields) throws SQLException {
		ArrayList<Map<String, String>> selectResult = new ArrayList<Map<String, String>>(); // 新结果集
		Map<String, String> recordInfo;
		logger.debug("查询SQL> " + selectSql.toString());
		if (sta == null) {
			sta = conn.createStatement(); // 定义Statement对象
			sta.setQueryTimeout(Constants.DB_STATEMENT_EXECUTE_TIMEOUT);
		}
		selectSql = OracleToMysql.trans(selectSql);
		dbResultSet = sta.executeQuery(selectSql);
		// 循环取值
		while (dbResultSet.next()) {
			recordInfo = new HashMap<String, String>();
			for (int i = 0; i < fields.size(); ++i)
				recordInfo.put((String) fields.get(i), dbResultSet
						.getString(fields.get(i)));
			selectResult.add(recordInfo);
		}
		dbResultSet.close(); // 断开结果集
		logger.debug("dbSelect success");
		return selectResult;
	}
	
	//查询大量数据，且网络较差的时候使用，减少和数据库的交互次数
	public ArrayList<Map<String, String>> dbSelectByFetchSize(String selectSql,
			ArrayList<String> fields) throws SQLException {
		ArrayList<Map<String, String>> selectResult = new ArrayList<Map<String, String>>(); // 新结果集
		Map<String, String> recordInfo;
		logger.debug("查询SQL> " + selectSql.toString());
		if (sta == null) {
			sta = conn.createStatement(); // 定义Statement对象
			sta.setQueryTimeout(Constants.DB_STATEMENT_EXECUTE_TIMEOUT);
		}
		sta.setFetchSize(Constants.FETCH_SIZE);
		selectSql = OracleToMysql.trans(selectSql);
		dbResultSet = sta.executeQuery(selectSql);
		// 循环取值
		while (dbResultSet.next()) {
			recordInfo = new HashMap<String, String>();
			for (int i = 0; i < fields.size(); ++i){
				recordInfo.put((String) fields.get(i), dbResultSet
						.getString(fields.get(i)));
			}
			selectResult.add(recordInfo);
		}
		dbResultSet.close(); // 断开结果集
		logger.debug("dbSelect success，共耗时");
		sta.setFetchSize(0);//为了不影响其它方法，此处将fetchsize修改为默认值，设置为0（jdbc自动取合适的值），一般会默认取10
		return selectResult;
	}
	
	
	//查询大量数据，且网络较差的时候使用，减少和数据库的交互次数
	public List<String[]> dbSelectList(String selectSql,
			ArrayList<String> fields) throws SQLException {
		List<String[]> rltList = new ArrayList<String[]>();
		logger.debug("查询SQL> " + selectSql.toString());
		if (sta == null) {
			sta = conn.createStatement(); // 定义Statement对象
			sta.setQueryTimeout(Constants.DB_STATEMENT_EXECUTE_TIMEOUT);
		}
		sta.setFetchSize(Constants.FETCH_SIZE);
		selectSql = OracleToMysql.trans(selectSql);
		dbResultSet = sta.executeQuery(selectSql);
		// 循环取值
		int fieldsSize = fields.size();
		while (dbResultSet.next()) {			
			String[] rlt = new String[fields.size()];
			for (int i = 0; i < fieldsSize; ++i){
				rlt[i] = dbResultSet.getString(fields.get(i));				
			}
			rltList.add(rlt);
		}
		dbResultSet.close(); // 断开结果集
		logger.debug("dbSelect success，共耗时");
		sta.setFetchSize(0);//为了不影响其它方法，此处将fetchsize修改为默认值，设置为0（jdbc自动取合适的值），一般会默认取10
		return rltList;
	}

	/**
	 * 使用PeraredStatement对象 实现select操作 注意：在selectSql中的字段和fields中的个数、名称要保持一致
	 * 
	 * @param selectSql
	 *            用于执行的select语句
	 * @param fields
	 *            要选择的字段
	 * @param params
	 *            参数数组
	 * @return 含有Map的ArrayList，一条记录形成一个Map
	 */
	public ArrayList<Map<String, String>> dbSelect(String selectSql,
			ArrayList<String> fields, Object... params) throws SQLException {
		ArrayList<Map<String, String>> selectResult = new ArrayList<Map<String, String>>();
		Map<String, String> recordInfo;
		logger.debug("存储SQL> " + selectSql.toString());
		// 设置参数
		selectSql = OracleToMysql.trans(selectSql);
		psList.add(ps = conn.prepareStatement(selectSql));
		ps.setQueryTimeout(Constants.DB_STATEMENT_EXECUTE_TIMEOUT);
		if (params != null && params.length > 0) {
			logger.debug(" params "+Arrays.toString(params));
			for (int i = 0; i < params.length; i++) {
				ps.setString((i + 1), StringUtils.isEmpty(params[i]==null?null:params[i].toString())?"": params[i].toString());
			}
		}
		dbResultSet = ps.executeQuery();
		// 循环取值
		while (dbResultSet.next()) {
			recordInfo = new HashMap<String, String>();
			for (int i = 0; i < fields.size(); ++i)
				recordInfo.put((String) fields.get(i), dbResultSet
						.getString(fields.get(i)));
			selectResult.add(recordInfo);
		}
		dbResultSet.close(); // 断开结果集
		logger.debug("dbSelect success");
		return selectResult;
	}

	/**
	 * 使用Statement对象 对数据库执行update，delete或insert操作
	 * 
	 * @param sql
	 *            要执行操作的SQL语句
	 * @return 成功返回true 失败返回false
	 */

	public boolean dbUpdate(String sql) throws SQLExecuteTimeoutException,SQLException,Exception {
		logger.debug("更新SQL> " + sql.toString());
		if (sta == null) {
			sta = conn.createStatement(); // 定义Statement对象
			sta.setQueryTimeout(Constants.DB_STATEMENT_EXECUTE_TIMEOUT);
		}
		try {			
			sql = OracleToMysql.trans(sql);
			sta.executeUpdate(sql);
		} catch (SQLException e) {
			if(isTimeout(e.getMessage())){
				throw new SQLExecuteTimeoutException();
			}else{
				throw e;
			}
		}
		logger.debug("dbUpdate success");
		return true;
	}

	/**
	 * 使用PeraredStatement对象 对数据库执行update，delete或insert操作
	 * 
	 * @param sql
	 *            要执行操作的SQL语句
	 * @param params
	 *            参数数组
	 * @return 执行成功返回true，失败返回false
	 */
	public boolean dbUpdate(String sql, Object... params) throws SQLExecuteTimeoutException,SQLException {
		logger.debug("更新SQL> " + sql.toString());
		sql = OracleToMysql.trans(sql);
		psList.add(ps = conn.prepareStatement(sql)); // 定义PrepareStatement对象
		ps.setQueryTimeout(Constants.DB_STATEMENT_EXECUTE_TIMEOUT);
		if (params != null && params.length > 0) {
			logger.debug(" params "+Arrays.toString(params));
			for (int i = 0; i < params.length; i++) {
				ps.setString((i + 1), StringUtils.isEmpty(params[i]==null?null:params[i].toString())?"": params[i].toString());
			}
		}
		
		try {			
			ps.executeUpdate();
		} catch (SQLException e) {
			if(isTimeout(e.getMessage())){
				throw new SQLExecuteTimeoutException();
			}else{
				throw e;
			}
		}finally{
			ps.close();
		}
		logger.debug("dbUpdate success");
		return true;
	}
	/**
	 * 使用PeraredStatement对象 对数据库执行update，delete或insert操作
	 * 
	 * @param sql
	 *            要执行操作的SQL语句
	 * @param params
	 *            参数数组
	 * @return 执行成功返回true，失败返回false
	 */
	public boolean dbUpdateByBatch(String sql, List<String[]> params) throws SQLExecuteTimeoutException,SQLException {
		logger.debug("批量更新SQL> " + sql.toString());
		sql = OracleToMysql.trans(sql);
		psList.add(ps = conn.prepareStatement(sql)); // 定义PrepareStatement对象
		ps.setQueryTimeout(Constants.DB_STATEMENT_EXECUTE_TIMEOUT);
		try {
			if (params != null && params.size() > 0) {
				for (String [] strs : params) {
					logger.debug(" params "+(strs==null?null:Arrays.toString(strs)));
					for(int i = 0; i <strs.length; i++){
						//logger.debug((i+1)+" <> "+(StringUtils.isEmpty(strs[i])?"":strs[i].toString()));
						ps.setString((i+1), StringUtils.isEmpty(strs[i])?null:strs[i].toString());
					}
					ps.addBatch();
				}
				ps.executeBatch();
			}
		} catch (SQLException e) {
			if(isTimeout(e.getMessage())){
				throw new SQLExecuteTimeoutException();
			}else{
				throw e;
			}
		}
		logger.debug("dbUpdate success");
		return true;
	}
	/**
	 * 使用PeraredStatement对象 对数据库执行update，delete或insert操作
	 * 
	 * @param sqls
	 *            要执行操作的SQL语句
	 * @param params
	 *            参数数组
	 * @return 执行成功返回true，失败返回false
	 */
	public boolean dbUpdateByBatch(List<String>sqls) throws SQLExecuteTimeoutException,SQLException {
		logger.debug(" 批量更新Sql " + sqls.size());
		if (sta == null) {
			sta = conn.createStatement(); // 定义Statement对象
			sta.setQueryTimeout(Constants.DB_STATEMENT_EXECUTE_TIMEOUT);
		}
		
		try {
			for (String str : sqls) {
				str = OracleToMysql.trans(str);
				sta.addBatch(str);
			}
			sta.executeBatch();
			sta.clearBatch();
		} catch (SQLException e) {
			if(isTimeout(e.getMessage())){
				throw new SQLExecuteTimeoutException();
			}else{
				throw e;
			}
		}
		logger.debug("dbUpdate By Batch success");
		return true;
	}
	
	public boolean isTimeout(String msg) {
//		String temp="ORA-01013: 用户请求取消当前的操作";
		boolean flag = false;
		
		//判断操作
		if(StringUtils.isNotBlank(msg) && msg.indexOf("ORA-01013")!=-1){
			flag = true;
		}
		
		return flag;
	}
	/**
	 * 将设置提交方式
	 */
	public void setAutoCommit(boolean flag) throws SQLException {
		conn.setAutoCommit(flag);
		logger.debug("数据库连接 自动提交状态："+(flag?"是":"否"));
	}

	/**
	 * 提交请求
	 */
	public void commit() throws SQLException {
		if(!conn.getAutoCommit()){
			conn.commit();
			logger.debug("conn.commit()");
		}else{
			logger.debug("autoCommit=true,不需提交！");
		}
	}

	/**
	 * 回滚操作
	 */
	public void rollback() throws SQLException{
		if(!conn.getAutoCommit()){
			conn.rollback();
		}else{
			logger.debug("autoCommit=true,无法回滚！");
		}
	}
	
	/**
	 * 关闭数据库连接
	 * 
	 * @return 成功返回true， 失败返回false
	 */
	public boolean clearConn() {
		try {
			
			//- 断开结果集
			if (dbResultSet != null) {
				dbResultSet.close();
				dbResultSet = null;
			}
			
			//- 断开Statement对象
			if (sta != null) {
				sta.close();
				sta = null;
			} 
			//- 断开PeraredStatement对象集合
			if (psList.size() > 0){
				for (PreparedStatement ps : psList){
					if (ps != null){
						ps.close(); // 断开PeraredStatement对象
					}
				}
				psList.clear();
			}
			//- 还原conn
			if(conn!= null){	
				if(!conn.getAutoCommit()){
					setAutoCommit(true);
				}
			}
			logger.debug("数据库链接清理");
			return true;
		} catch (SQLException e) {
			logger.error("Database link cleaning exception",e);
			return false;
		}
	}

	/**
	 * 关闭数据库连接
	 * 
	 * @return 成功返回true， 失败返回false
	 */
	public boolean close() {
		try {
			//- 断开结果集
			if (dbResultSet != null) {
				dbResultSet.close();
				dbResultSet = null;
			}
			
			//- 断开Statement对象
			if (sta != null) {
				sta.close();
				sta = null;
			} 
			//- 断开PeraredStatement对象集合
			if (psList.size() > 0){
				for (PreparedStatement ps : psList){
					if (ps != null){
						ps.close(); // 断开PeraredStatement对象
					}
				}
				psList.clear();
			}
			//- 断开数据库链接
			if (conn != null) {
				if(!conn.getAutoCommit()){
					conn.rollback();//还原连接
					setAutoCommit(true);
				}
				conn.close();
				conn = null;
			}
			logger.debug("数据库连接释放");
			return true;
		} catch (SQLException e) {
			logger.error("Database connection release exception" ,e);
			return false;
		}
	}

	public Connection getConn(){
		return conn;
	}
	
	private void addRecordToStatement(PreparedStatement pState,List<String[]> params)
		throws SQLException{		
		try {
			if (params != null && params.size() > 0) {
				for (String [] strs : params) {
					logger.debug(" params "+Arrays.toString(strs));
					for(int i = 0; i <strs.length; i++){
						pState.setString((i+1), StringUtils.isEmpty(strs[i])?null:strs[i].toString());
					}
					pState.addBatch();
				}
			}
		} catch (SQLException e) {
			if(isTimeout(e.getMessage())){
				throw new SQLExecuteTimeoutException();
			}else{
				throw e;
			}
		}
	}

	public void addRecordToInfo(String sql,List<String[]> params)
			throws SQLException {
		if(infoStmt==null) {
			sql = OracleToMysql.trans(sql);
			infoStmt = conn.prepareStatement(sql);
			infoStmt.setQueryTimeout(Constants.DB_STATEMENT_EXECUTE_TIMEOUT);
			infoCount = 0;
		}
		try {
			addRecordToStatement(infoStmt, params);
			infoCount ++;
		} catch (SQLException e) {
			if(isTimeout(e.getMessage())){
				throw new SQLExecuteTimeoutException();
			}else{
				throw e;
			}
		}

	}	
	
	public void addRecordToDisk(String sql,List<String[]> params)
		throws SQLException {
		if(diskStmt==null) {
			sql = OracleToMysql.trans(sql);
			diskStmt = conn.prepareStatement(sql);
			diskStmt.setQueryTimeout(Constants.DB_STATEMENT_EXECUTE_TIMEOUT);
			diskCount = 0;
		}
		try {
			addRecordToStatement(diskStmt, params);
			diskCount ++;
		} catch (SQLException e) {
			if(isTimeout(e.getMessage())){
				throw new SQLExecuteTimeoutException();
			}else{
				throw e;
			}
		}
		
	}
	
	public void addRecordToNet(String sql,List<String[]> params)
	throws SQLException {
		if(netStmt==null) {
			sql = OracleToMysql.trans(sql);
			netStmt = conn.prepareStatement(sql);
			netStmt.setQueryTimeout(Constants.DB_STATEMENT_EXECUTE_TIMEOUT);
			netCount = 0;
		}
		try {
			addRecordToStatement(netStmt, params);
			netCount ++;
		} catch (SQLException e) {
			if(isTimeout(e.getMessage())){
				throw new SQLExecuteTimeoutException();
			}else{
				throw e;
			}
		}
		
	}
	public void addRecordToWarning(String sql,List<String[]> params)
	throws SQLException {
		if(warningStmt==null) {
			sql = OracleToMysql.trans(sql);
			warningStmt = conn.prepareStatement(sql);
			warningStmt.setQueryTimeout(Constants.DB_STATEMENT_EXECUTE_TIMEOUT);
			warningCount = 0;
		}
		try {
			addRecordToStatement(warningStmt, params);
			warningCount ++;
		} catch (SQLException e) {
			if(isTimeout(e.getMessage())){
				throw new SQLExecuteTimeoutException();
			}else{
				throw e;
			}
		}
		
	}
	public void addRecordToEmail(String sql,List<String[]> params)
	throws SQLException {
		if(emailStmt==null) {
			sql = OracleToMysql.trans(sql);
			emailStmt = conn.prepareStatement(sql);
			emailStmt.setQueryTimeout(Constants.DB_STATEMENT_EXECUTE_TIMEOUT);
			emailCount = 0;
		}
		try {
			addRecordToStatement(emailStmt, params);
			emailCount ++;
		} catch (SQLException e) {
			if(isTimeout(e.getMessage())){
				throw new SQLExecuteTimeoutException();
			}else{
				throw e;
			}
		}
		
	}
	public void addRecordToStatusChange(String sql,List<String[]> params)
		throws SQLException {
		if(statusChangeTimeStmt==null) {
			sql = OracleToMysql.trans(sql);
			statusChangeTimeStmt = conn.prepareStatement(sql);
			statusChangeTimeStmt.setQueryTimeout(Constants.DB_STATEMENT_EXECUTE_TIMEOUT);
			statusChangeTimeCount = 0;
		}
		try {
			addRecordToStatement(statusChangeTimeStmt, params);
			statusChangeTimeCount ++;
		} catch (SQLException e) {
			if(isTimeout(e.getMessage())){
				throw new SQLExecuteTimeoutException();
			}else{
				throw e;
			}
		}
		
	}
	
	
	public void setDetailVals(String sql,List<String> fieldNames,
			Map<String,String>tempMap,long currentId,String detailTableName){
		try
		{
			if(detailStmt==null) {
				sql = OracleToMysql.trans(sql);
				detailStmt = conn.prepareStatement(sql);
				detailStmt.setQueryTimeout(Constants.DB_STATEMENT_EXECUTE_TIMEOUT);
				detailCount = 0;
			}
			StringBuffer sbInsertTemp = new StringBuffer();
			for(int fieldNum = 0, fieldSize = fieldNames.size(); fieldNum < fieldSize; fieldNum ++){
				String fieldName = fieldNames.get(fieldNum);
				String fieldValue = null;
				if("detection_info_id".equals(fieldName)){
					fieldValue = currentId + "";
				}else{
					fieldValue = tempMap.get(fieldName);
				}
				
				if(DateUtil.isDate(fieldValue, Constants.COMMON_DATE_FORMAT)){
					detailStmt.setTimestamp(fieldNum+1, new Timestamp(format2.parse(fieldValue).getTime()));
				}else{
					detailStmt.setString(fieldNum+1, StringUtils.isEmpty(fieldValue)?null:fieldValue);
				}
				sbInsertTemp.append(fieldValue + ",");
			}
			detailStmt.addBatch();
			detailCount ++;
			//添加一条详细信息
			logger.debug(detailTableName + " 表添加一条数据value： " + sbInsertTemp.toString());			
		
		} catch (Exception e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void executeBatchAllStmt(boolean isExecuteStatusChange) throws SQLException {
		//测试代码
		logger.debug("statement==null? info="+infoStmt+" detailStmt="+detailStmt+"  warningStmt="+warningStmt+"   netStmt="+netStmt+"   diskStmt="+diskStmt+"   emailStmt="+emailStmt+"   statusChangeTimeStmt="+statusChangeTimeStmt);
		if(infoStmt!=null && infoCount > 0) {
			try
			{
				infoStmt.executeBatch();
				logger.debug("info 共批量导入：" + infoCount +" 条");
			} catch (SQLException e){
				logger.error("          第三步-2： 批量入库detection_info表 异常");
				throw new SQLException(e);
			}finally {
				infoCount = 0;
				infoStmt.clearBatch();
			}
		}else{
			logger.info("          第三步-2： detection_info的preparestatement为null ");
		}	
		if(detailStmt!=null && detailCount > 0) {
			try{
				detailStmt.executeBatch();
				logger.debug("detail 共批量导入：" + detailCount +" 条");
			} catch (SQLException e){
				logger.error("          第三步-2： 批量入库详细表 异常");
				throw new SQLException(e);
			}finally {
				detailCount = 0;
				detailStmt.clearBatch();
			}
		}else{
			logger.info("          第三步-2： 详细表的preparestatement为null ");
		}	
		if(warningStmt!=null && warningCount > 0) {
			try{
				warningStmt.executeBatch();
				logger.debug("warning 共批量导入：" + warningCount +" 条");
			} catch (SQLException e){
				logger.error("          第三步-2： 批量入库warning表 异常");
				throw new SQLException(e);
			}finally {
				warningCount = 0;
				warningStmt.clearBatch();
			}
		}else{
			logger.info("          第三步-2： warning的preparestatement为null ");
		}			
		if(netStmt!=null && netCount > 0)  {
			try{
				netStmt.executeBatch();
				logger.debug("net 共批量导入：" + netCount +" 条");
			} catch (SQLException e){
				logger.error("          第三步-2： 批量入库系统信息-net表 异常");
				throw new SQLException(e);
			}finally {
				netCount = 0;
				netStmt.clearBatch();
			}
		}else{
			logger.info("          第三步-2： 系统信息net的preparestatement为null ");
		}	
		if(diskStmt!=null && diskCount > 0) {
			try{
				diskStmt.executeBatch();
				logger.debug("disk 共批量导入：" + diskCount +" 条");
			} catch (SQLException e){
				logger.error("          第三步-2： 批量入库系统信息-disk表 异常");
				throw new SQLException(e);
			}finally {
				diskCount = 0;
				diskStmt.clearBatch();
			}
		}else{
			logger.info("          第三步-2： 系统信息disk的preparestatement为null ");
		}	
		if(emailStmt!=null && emailCount > 0) {
			try{
				emailStmt.executeBatch();
				logger.debug("email 共批量导入：" + emailCount +" 条");
			} catch (SQLException e){
				logger.error("          第三步-2： 批量入库email表 异常");
				throw new SQLException(e);
			}finally {
				emailCount = 0;
				emailStmt.clearBatch();
			}
		}else{
			logger.info("          第三步-2： email的preparestatement为null ");
		}	
		if(statusChangeTimeStmt!=null && isExecuteStatusChange && statusChangeTimeCount > 0) {
			try{
				statusChangeTimeStmt.executeBatch();
				logger.debug("statusChangeTime 共批量导入：" + statusChangeTimeCount +" 条");
			} catch (SQLException e){
				logger.error("          第三步-2： 批量更新new表状态变更 异常");
				throw new SQLException(e);
			}finally {
				statusChangeTimeCount = 0;
				statusChangeTimeStmt.clearBatch();
			}
		}else{
			logger.info("          第三步-2： new表状态变更的preparestatement为null ");
		}	
	}
	
	public void closeCommonBatchStatement(){		
		try
		{
			if(infoStmt!=null) {
				infoStmt.close();
				infoStmt = null;
			}		
			if(detailStmt!=null) {
				detailStmt.close();
				detailStmt = null;
			}	
			if(warningStmt!=null) {
				warningStmt.close();
				warningStmt = null;
			}		
			if(netStmt!=null) {
				netStmt.close();
				netStmt = null;
			}
			if(diskStmt!=null) {
				diskStmt.close();
				diskStmt = null;
			}
			if(emailStmt!=null) {
				emailStmt.close();
				emailStmt = null;
			}
			if(statusChangeTimeStmt!=null) {
				statusChangeTimeStmt.close();
				statusChangeTimeStmt = null;
			}
		} catch (SQLException e){
			logger.error("", e);
		}finally{
			infoCount= 0;
			warningCount= 0;
			emailCount= 0;
			statusChangeTimeCount= 0;
			netCount= 0;
			diskCount= 0;
			detailCount= 0;
		}
		
	}
	
	public void closeDetailBatchStatement(){
		try
		{
			if(detailStmt!=null) {
				detailStmt.close();
				detailStmt = null;
			}	
		} catch (SQLException e){
			logger.error("", e);
		}finally{
			detailCount = 0;
		}
	}
	
	
	public ArrayList<String> dbSelectSingleColumn(String selectSql,Object... params) throws Exception {
		ArrayList<String> selectResult = new ArrayList<String>();
		logger.debug("存储SQL> " + selectSql.toString());
		// 设置参数
		psList.add(ps = conn.prepareStatement(selectSql));
		ps.setQueryTimeout(Constants.DB_STATEMENT_EXECUTE_TIMEOUT);
		if (params != null && params.length > 0) {
			logger.debug(" params "+Arrays.toString(params));
			for (int i = 0; i < params.length; i++) {
				ps.setString((i + 1), StringUtils.isEmpty(params[i]==null?null:params[i].toString())?"": params[i].toString());
			}
		}
		dbResultSet = ps.executeQuery();
		// 循环取值
		while (dbResultSet.next()) {
			selectResult.add(dbResultSet.getString(1));
		}
		dbResultSet.close(); // 断开结果集
		logger.debug("dbSelect success");
		return selectResult;
	}
	
	/**
	 * 模拟oracle start with connect by
	 * @param sql select * from table where xxx
	 * @param start 开始条件
	 * @param idName
	 * @param pidName
	 * @return
	 * @throws Exception 
	 */
	public  String startWithConnectBy(String sql ,String start,String idName,String pidName) throws Exception{
		//List<String> result = new ArrayList<String>();
		Set<String> result = new HashSet<String>();
		result.add("-9999");
		StringBuilder sb = new StringBuilder();
		sb.append(sql);
		sb.append(" and ");
		sb.append(start);
		List<String> temp = this.dbSelectSingleColumn(sb.toString());
		//result.addAll(temp);
		do {
			if(temp == null || temp.size() <1){
				break;
			}else{
				result.addAll(temp);
				StringBuilder tsb = new StringBuilder();
				for(String m : temp){
					tsb.append(",");
					tsb.append(m);
				}
				tsb.deleteCharAt(0);
				start = tsb.toString();
			}
			StringBuilder psb = new StringBuilder();
			psb.append(sql);
			psb.append(" and ");
			psb.append(pidName);
			psb.append(" in ( ");
			psb.append(start);
			psb.append(" )");
			temp = this.dbSelectSingleColumn(psb.toString());
			//result.addAll(temp);
		} while (true);
		StringBuilder r = new StringBuilder();
		for(String s : result){
			r.append(",");
			r.append(s);
		}
		if(r.length() > 0){
			r.deleteCharAt(0);
		}
		return r.toString();
	}
	
	/**
	 * 反向查找 所有父节点
	 * @param sql
	 * @param start
	 * @param idName
	 * @param pidName
	 * @return
	 * @throws Exception
	 */
	public  String startWithConnectByReverse(String table,String where ,String start,String idName,String pidName) throws Exception{
//		List<String> result = new ArrayList();
		Set<String> result = new HashSet<String>();
		result.add("-9999");
		String sql = "select " + idName +","+pidName+ " from " + table + " where "+ (StringUtils.isBlank(where)?" 1=1 " : where) ;
		StringBuilder sb = new StringBuilder();
		sb.append(sql);
		sb.append(" and ");
		sb.append(start);
		ArrayList<String> fields = new ArrayList<String>();
		fields.add(idName);
		fields.add(pidName);
		Set<String> temp = new HashSet<String>();
		ArrayList<Map<String,String>> list = this.dbSelect(sql, fields);
		if(list != null){
			for(Map<String,String> map : list){
				String id = map.get(idName);
				String pid = map.get(pidName);
				result.add(id);
				temp.add(pid);
			}
		}
		sql = "select " +pidName+ " from " + table + " where "+ (StringUtils.isBlank(where)?" 1=1 " : where) ;
		do {
			if(temp == null || temp.size() <1){
				break;
			}else{
				result.addAll(temp);
				StringBuilder tsb = new StringBuilder();
				for(String m : temp){
					tsb.append(",");
					tsb.append(m);
				}
				tsb.deleteCharAt(0);
				start = tsb.toString();
			}
			StringBuilder psb = new StringBuilder();
			psb.append(sql);
			psb.append(" and ");
			psb.append(idName);
			psb.append(" in ( ");
			psb.append(start);
			psb.append(" )");
			temp.clear();
			List<String> tempList = this.dbSelectSingleColumn(psb.toString());
			temp.addAll(tempList);
		} while (true);
		StringBuilder r = new StringBuilder();
		for(String s : result){
			r.append(",");
			r.append(s);
		}
		if(r.length() > 0){
			r.deleteCharAt(0);
		}
		return r.toString();
	}
	
	
}
