package kr.co.proten.db;

import java.io.BufferedReader;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.ObjectOutput;
import java.io.ObjectOutputStream;
import java.io.Reader;
import java.math.BigDecimal;
import java.sql.Array;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.FileUtils;
import org.apache.commons.io.FilenameUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


import kr.co.proten.common.ArgsInfo;
import kr.co.proten.common.util.FileUtil;
import kr.co.proten.common.util.StringUtil;
import kr.co.proten.config.Config;
import kr.co.proten.config.data.Result;
import kr.co.proten.config.data.Select;
import kr.co.proten.config.data.WhereArgs;
import kr.co.proten.config.server.ServerInfo;
import kr.co.proten.custom.ClassConfigNameFactory;
import kr.co.proten.custom.ClassDBNameFactory;
import kr.co.proten.custom.ClassNameFactory;
import kr.co.proten.doc2txt.Doc2TxtClient;
import kr.co.proten.filter.FileContent;
 



public class DBManager  {
    private boolean isError = false;
    protected String m_vender="";    
    
    protected String dbId="";    
    private int columnCnt = 0;
   
    protected String characterSet = "";
    
    protected Config _config = null;
    private static final Logger log = LoggerFactory.getLogger(DBManager.class);
    
    protected Connection t_conn;
    protected Connection l_conn;
    
    protected ResultSet t_rs;  
    protected LinkedHashMap<String,String> t_column ;
    protected List<String> etc_column ;
    
    protected PreparedStatement t_pstmt;    
    protected HashMap<String,Connection> sub_conn;
    protected ArrayList<Result> rList = null; 
    protected FileContent fileContent = null;
    protected String primaryKey = "";
    
    protected boolean  isFieldExpansion = false;
    
    protected int rowCount = 0;
    
    protected int dbVersion = 0;
    
    protected LinkedHashSet<String> dupKey = new LinkedHashSet<String>();
    protected Map<String, Doc2TxtClient> doc2serverClientMap = null; 
    
    
    
    
    public DBManager() throws Exception {
    	
    }
    public DBManager(Config config) throws Exception {
    	_config = config;  
    }
    
    public boolean isError() {
        return isError;
    }
    
    public String getColumnType(String key) {
    	if(t_column==null) {
    		return "";
    	}
    	String type = t_column.get(key);
    	return StringUtil.checkNull(type);
    }
    
    
    
    public void setDbList(HashMap<String,ServerInfo> dbList,String dbId) throws Exception {
    	this.dbId = dbId;
    	DBPool.setPool(dbList);    	
    	log.info("DB Connection Start");
		t_conn = DBPool.getConnection(dbId);
		m_vender = DBPool.getVender(dbId);//mariadb
		dbVersion = StringUtil.parseInt(DBPool.getVersion(dbId),0);//
		if(m_vender.equals("mssql")||m_vender.equals("mysql")||m_vender.equals("mariadb")||m_vender.equals("tibero")||m_vender.equals("postgre")) {
			l_conn = DBPool.getConnection(dbId);
		}
		log.info("DB Connection ["+dbId+"] Success");
    }
    /**
     * 특정 ID의 DB 접속을 풀형태로 가지고 있다 반환한다.
     * @param dbId
     * @return
     */ 
    
    public Connection getSubConnection(String dbId) {
    	Connection subCon = null;
    	dbId = dbId.trim();
    	if(sub_conn==null) {
			sub_conn =new HashMap<String,Connection>(); 
		}
    	
		if(!sub_conn.containsKey(dbId)) {
						
			try {
				subCon = DBPool.getConnection(dbId);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
			sub_conn.put(dbId, subCon);
			
			log.info("DB Connection Success"+sub_conn);
		}else {
			subCon = sub_conn.get(dbId);
			log.debug("DB get :"+sub_conn);
			try {
				if(subCon.isClosed()) {
					subCon.close();
					log.debug("DB Closed");
					//Thread.sleep(1000);
					subCon = DBPool.getConnection(dbId);
					sub_conn.put(dbId, subCon);
					log.debug("New Connectio Success "+sub_conn);
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		
		return subCon;
    	
    }
    
    public void init() throws Exception {
    	fileContent =  new FileContent(true);
        FileUtil.deleteFile(ArgsInfo.homePath+"filter"+FileUtil.fileseperator+ArgsInfo.selectid+FileUtil.fileseperator, "txt");
    	fileContent.setFilterRoot(ArgsInfo.homePath+"filter"+FileUtil.fileseperator+ArgsInfo.selectid+FileUtil.fileseperator);
        
    	HashMap<String,ServerInfo> dbList = _config.getDbServer();
    	DBPool.setPool(dbList);  
    	doc2serverClientMap = new HashMap<String, Doc2TxtClient>();
    }

    public int getColumnCnt() {
        return columnCnt;
    }
    /**
     * db 접속함.
     * @throws Exception
     */
    public void dbConnection() throws Exception{
    	Select select = _config.getSelect();
    	
    	LinkedHashMap<String,String> dbInfo= select.getDbInfo();
    	
    	for ( Map.Entry<String, String> entry : dbInfo.entrySet() ) {
		     String dbtag = entry.getKey().trim();
		     String dbId = entry.getValue().trim();
		     
		     //log.info("DB Connection "+dbtag+"/"+dbId);
	    	 if(dbtag.equals("db")) {
	    		 //log.info("DB Connection Start");
	    		 t_conn = DBPool.getConnection(dbId);
	    		 m_vender = DBPool.getVender(dbId);
	    		 dbVersion = StringUtil.parseInt(DBPool.getVersion(dbId),0);
	    		 if(m_vender.equals("mssql")||m_vender.equals("mysql")||m_vender.equals("mariadb")||m_vender.equals("tibero")||m_vender.equals("postgre")) {
	    			 l_conn = DBPool.getConnection(dbId);
	    		 }
	    		 log.info("DB Connection ["+dbId+"] Success");
	    	 }else { 
	    		 getSubConnection(dbId);
	    		
	    	 }
    	}
    }

    
    public boolean next() {
        boolean isBool = false;
        if(t_rs == null) {
            return false;
        }
        try {
            isBool = t_rs.next();
        } catch (SQLException e) {
            log.error("[next() SQL Error Code : " +e.getErrorCode()+" ]");
            log.error("[next() SQLException " +StringUtil.StackTraceToString(e)+"\n]");
            isBool = false;
            isError = true;
        }
        return isBool;
    }
    
    
    /**
     * Set Target Table ResultSet
     * @param query SQL query string
     * @throws Exception Error info
     */
    public void setResultSet(String query) throws Exception {
        t_rs = null;
        log.debug("[setResultSet]["+StringUtil.trimDuplecateSpace(query)+"]", 3);
         
        try {
        	t_pstmt = t_conn.prepareStatement(query);
        	t_pstmt.setFetchSize(10);
            t_rs = t_pstmt.executeQuery();
          
            t_column =  getColumnInfo(t_rs.getMetaData());
            //System.out.println("t_columnt_column"+t_column);
            //primaryKey = t_rs.getMetaData().getColumnLabel(1);
        } catch (SQLException e) {
        	e.printStackTrace();
            log.error("[DBJob] [setResultSet SQL Query : "+query+"]");
            log.error("[DBJob] [setResultSet SQL Error Code : "+e.getErrorCode()+" ]");
            log.error("[DBJob] [setResultSet SQLException "+e.getMessage()+"]");
            throw new Exception(": setResultSet SQL Error " +e);
        }
    }
    
    /**
     * result에 없는 필드의 리스트를 작성한다.
     * @param rInList
     */
    public void setEtcColumn(ArrayList<Result> rInList) {
    	etc_column =  new ArrayList<String>();
    	
    	Iterator<String> keyData = t_column.keySet().iterator();
        
        while (keyData.hasNext()) {
        	String key = keyData.next();
        	if(!etc_column.contains(key)) {
        		etc_column.add(key);
        	}
                
        } // while
    	
    	for(Result _result : rInList) {
    		etc_column.remove(_result.getColumn());
    	} 
    	
    	for(String columns : etc_column) {
    		log.debug("setEtcColumn : "+ columns);
    	} 
    	
    }
    
    
    /**
     * Set Target Table ResultSet
     * @param query SQL query string
     * @throws Exception Error info
     */
    public void setResultSetPk(String query) throws Exception {
        t_rs = null;
        log.debug("[setResultSet]["+StringUtil.trimDuplecateSpace(query)+"]", 3);
         
        try {
        	//System.out.println(t_conn);
        	/*if(m_vender.equals("mysql")||m_vender.equals("mariadb")) {
        		t_pstmt = t_conn.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, 
                        ResultSet.CONCUR_READ_ONLY);
    			t_pstmt.setFetchSize(10);
        		
    		}else {
    			//t_pstmt = t_conn.prepareStatement(query);
        		t_pstmt = t_conn.prepareStatement(query);
    			//t_pstmt.setFetchSize(10);
    		//}*/
        	
        	t_pstmt = t_conn.prepareStatement(query);
            t_rs = t_pstmt.executeQuery();
            
            /*try {
            	if(m_vender.equals("mysql")||m_vender.equals("mariadb")) {
		            t_rs.last();     
		            rowCount = t_rs.getRow();
		            t_rs.beforeFirst();
		            
		            log.debug("[setResultSet][ Total Row Count : "+rowCount+"]");
            	}
            }catch(Exception ex) {
            	
            }*/
            
            t_column =  getColumnInfo(t_rs.getMetaData());
            //System.out.println("t_columnt_column"+t_column);
            Select select = _config.getSelect();
            rList = new ArrayList<Result>();
            
            
            ArrayList<Result> rInList = select.getResultMap().getResult();
            
            if(select.isFieldExpansion()) {
            	setEtcColumn(rInList);
            	isFieldExpansion = true;
            }
            for(Result _result : rInList) {
            	if(_result.isSql() ) {
            		if(!_result.isMemoryKey()) {
            		 
            			WhereArgs wArgs = StringUtil.getSqlArgs(_result.getSql(),t_column,_result.isStatement());
	            		_result.setWargs(wArgs);
            		}
            	}
            	rList.add(_result);
            	if("y".equals(_result.getPrimary())){
            		primaryKey = _result.getColumn().toLowerCase();
            	}
            } 
            String pType = t_column.get(primaryKey);
            t_column.put("_id", pType);
        } catch (SQLException e) {
        	e.printStackTrace();
            log.error("[DBJob] [setResultSet SQL Query : "+query+"]");
            log.error("[DBJob] [setResultSet SQL Error Code : "+e.getErrorCode()+" ]");
            log.error("[DBJob] [setResultSet SQLException "+e.getMessage()+"]");
            throw new Exception(": setResultSet SQL Error " +e);
        }
    }
    
    /**
     * Set Target Table ResultSet
     * @param query SQL query string
     * @throws Exception Error info
     */
    public void setResultSetPk(String query, String[][] values) throws Exception {
        t_rs = null;
        log.debug("[setResultSet:setResultSetPk]["+StringUtil.trimDuplecateSpace(query)+"]", 3);
         
        try {
        	if(t_pstmt!=null) {
        		t_pstmt.close();
        	}
        	/*if(m_vender.equals("mysql")||m_vender.equals("mariadb")) {
        		t_pstmt = t_conn.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, 
                        ResultSet.CONCUR_READ_ONLY);
    			t_pstmt.setFetchSize(10);
        		
    		//}else {
    			
    		//}*/
        	t_pstmt = t_conn.prepareStatement(query);
        	t_pstmt = setPstmt(t_pstmt, values);
            t_rs = t_pstmt.executeQuery();
            
            
            
            t_column =  getColumnInfo(t_rs.getMetaData());
            //System.out.println("t_columnt_column"+t_column);
            
            
           /* try {
            	if(m_vender.equals("mysql")||m_vender.equals("mariadb")) {
		            t_rs.last();     
		            rowCount = t_rs.getRow();
		            t_rs.beforeFirst();
		            log.debug("[setResultSet][ Total Row Count : "+rowCount+"]");
            	}	            
	           
            }catch(Exception ex) {
            	
            }*/
            
            
            
            Select select = _config.getSelect();
            rList = new ArrayList<Result>();
            ArrayList<Result> rInList = select.getResultMap().getResult();
            for(Result _result : rInList) {
            	if(_result.isSql() ) {
            		if(!_result.isMemoryKey()) {
            		 
            			WhereArgs wArgs = StringUtil.getSqlArgs(_result.getSql(),t_column,_result.isStatement());
	            		_result.setWargs(wArgs);
            		}
            	}
            	rList.add(_result);
            	if("y".equals(_result.getPrimary())){
            		primaryKey = _result.getColumn().toLowerCase();
            	}
            } 
            String pType = t_column.get(primaryKey);
            t_column.put("_id", pType);
        } catch (SQLException e) {
        	e.printStackTrace();
            log.error("[DBJob] [setResultSet SQL Query : "+query+"]");
            log.error("[DBJob] [setResultSet SQL Error Code : "+e.getErrorCode()+" ]");
            log.error("[DBJob] [setResultSet SQLException "+e.getMessage()+"]");
            throw new Exception(": setResultSet SQL Error " +e);
        }
    }
    
    
    /**
    * Set Target Table ResultSet
    * @param query SQL query string
    * @throws Exception Error info
    */
   public void setResultSet(String query, String[][] values) throws Exception {
       t_rs = null;
       log.debug("[setResultSet]["+StringUtil.trimDuplecateSpace(query)+"]", 3);
        
       try {
       		t_pstmt = t_conn.prepareStatement(query);
       		t_pstmt.setFetchSize(10);
       		t_pstmt = setPstmt(t_pstmt, values);
            t_rs = t_pstmt.executeQuery();
            t_column =  getColumnInfo(t_rs.getMetaData());
           //System.out.println("t_columnt_column"+t_column);
          
       } catch (SQLException e) {
       	e.printStackTrace();
           log.error("[DBJob] [setResultSet SQL Query : "+query+"]");
           log.error("[DBJob] [setResultSet SQL Error Code : "+e.getErrorCode()+" ]");
           log.error("[DBJob] [setResultSet SQLException "+e.getMessage()+"]");
           throw new Exception(": setResultSet SQL Error " +e);
       }
   }
   /*
    * DB Crawler 에서 사용하는 데이터 가지고 오는 함수
    */
    public LinkedHashMap<String,Object> getResultData() { 
    	LinkedHashMap<String,Object> resultMap = new LinkedHashMap<String,Object>();
        try {
        	ResultSetMetaData meta = t_rs.getMetaData();        	
        	int colCount = meta.getColumnCount();        	
        	for( int idx  = 1 ; idx <= colCount ; idx ++ ) {
        		//String columnType = meta.getColumnTypeName(idx);
        		String columName = meta.getColumnLabel(idx).toLowerCase();
        		//String columnValue = "";
        		Object object = t_rs.getObject(idx);
        		if(object == null) {
        			resultMap.put(columName, "");
            	}else {
            		resultMap.put(columName, object);
            	}
        		
        		if(!"".equals(primaryKey)){
        			if(primaryKey.equals(columName)) {
        				resultMap.put("_id", object);
        			}
        		}
        		if(idx == 1){
        			resultMap.put("_id", object);
        		}
        		
        	}
        } catch (Exception e) {
            log.error("[Exception]["+StringUtil.StackTraceToString(e)+"\n]");
        }
        return resultMap;
    	//return getResultData(t_rs,primaryKey);
    } 
   
	public HashMap<String,String> getResultData(ResultSet rs,String key) {
        HashMap<String,String> resultMap = new HashMap<String,String>();
        try {
        	ResultSetMetaData meta = rs.getMetaData();        	
        	int colCount = meta.getColumnCount();        	
        	for( int idx  = 1 ; idx <= colCount ; idx ++ ) {
        		String columnType = meta.getColumnTypeName(idx);
        		String columName = meta.getColumnLabel(idx).toLowerCase();
        		String columnValue = "";
        		
        		// oracle 9i 대응용//
        		//System.out.println("m_vender:"+m_vender+"/dbVersion : "+dbVersion);
        		if( "oracle".equalsIgnoreCase(m_vender) &&  dbVersion == 9) {
	        		columnValue = getOracleColumnData(rs,idx,columnType);
	        		//if(ArgsInfo.debug) { 
		        			//System.out.println("Db oracle test cloumn : "+columName+"/"+columnType+"//"+columnValue);
		        	//}
        		}else {
        			Object object = rs.getObject(idx);
	        		//if(ArgsInfo.debug) {
	        			//System.out.println("Db cloumn : "+columName+"/"+columnType+"//"+object);
	        		//}
	        		
	        		if(object != null) {
	                	columnValue = getColumnData(object, columnType);
	            	}
        		}
        		resultMap.put(columName, columnValue);
        		if(!"".equals(key)){
        			if(key.equals(columName)) {
        				resultMap.put("_id", columnValue);
        			}
        		}else if(idx == 1){
        			resultMap.put("_id", columnValue);
        		}
        	}
        } catch (Exception e) {
            log.error("[Exception]["+StringUtil.StackTraceToString(e)+"\n]");
        }
        return resultMap;
    }
	
	
	public HashMap<String,Object> getResultPkData(ResultSet rs) {
        HashMap<String,Object> resultMap = new HashMap<String,Object>();
        try {
        	//ResultSetMetaData meta = rs.getMetaData();        	
        	//int colCount = meta.getColumnCount();        	
        	//for( int idx  = 1 ; idx <= colCount ; idx ++ ) {
        	int idx = 1;
    		//String columnType = meta.getColumnTypeName(idx);
    		//String columName = meta.getColumnLabel(idx).toLowerCase();
    		String columnValue = "";
    		Object object = rs.getObject(idx);
    		if(object != null) {
    			resultMap.put("_id", object);
        	}
    		//resultMap.put(columName, columnValue);
    		
        	 
        } catch (Exception e) {
            log.error("[Exception]["+StringUtil.StackTraceToString(e)+"\n]");
        }
        return resultMap;
    }
	
	
	public HashMap<String,Object> getResultPkDateData(ResultSet rs) {
        HashMap<String,Object> resultMap = new HashMap<String,Object>();
        try {
        	//ResultSetMetaData meta = rs.getMetaData();        	
        	//int colCount = meta.getColumnCount();        	
        	//for( int idx  = 1 ; idx <= colCount ; idx ++ ) {
        	int idx = 1;
    		//String columnType = meta.getColumnTypeName(idx);
    		//String columName = meta.getColumnLabel(idx).toLowerCase();
    		String columnValue = "";
    		Object object = rs.getObject(idx);
    		
    		
    		if(object != null) {
    			columnValue=object.toString();
    			//resultMap.put("_pk", object);
        	}
    		
    		object = rs.getObject(idx+1);
    		if(object != null) {
    			columnValue+="|"+object.toString();
        	}
    		//resultMap.put(columName, columnValue);
    		resultMap.put("_id", columnValue);
        	 
        } catch (Exception e) {
            log.error("[Exception]["+StringUtil.StackTraceToString(e)+"\n]");
        }
        return resultMap;
    }
    
    
     
    
    
    
    
    
    
    public LinkedHashMap<String,String> getColumnInfo(ResultSetMetaData meta){    	
    	LinkedHashMap<String,String> colInfo = new LinkedHashMap<String,String>();
    	try {
    		int colCnt = meta.getColumnCount();
    		//System.out.println("colCntv"+colCnt);
    		for(int idx = 1  ; idx <= colCnt ; idx ++) {
           	 	String columName =meta.getColumnLabel(idx).toLowerCase();
                String className = meta.getColumnClassName(idx); //Not supported method at symfoware
                //System.out.println("1:"+columName+"//"+className);
                className = getClassName(className);
                //System.out.println("2:"+columName+"//"+className);
                colInfo.put(columName, className);
           }
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return colInfo;
    }
    
    
    
    
    
    
    
    
    /**
     * Sql query execute method
     * @param _conn  connection object
     * @param query  sql query string
     * @param values parameter values
     * @param isStatement statement or preparestatement
     * @return success or fail
     * @throws SQLException error info
     * @throws Exception  error info
     */
    public int executePQuery(Connection _conn, String query, String[][] values, boolean isStatement)
            throws SQLException{
        log.debug("["+query+"]" + isStatement);
        PreparedStatement pstmt ;
        Statement stmt ;
        int ret = -1;
        if(isStatement){
            stmt = _conn.createStatement();
            try {
				query = getStmtQuery(query, values);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
            ret = stmt.executeUpdate(query);
            releaseDB(stmt);
        }else{
            pstmt = _conn.prepareStatement(query);
            pstmt.setFetchSize(10);
            if( values != null){
                try {
					pstmt = setPstmt(pstmt, values);
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
            }
            ret = pstmt.executeUpdate();
            releaseDB(pstmt);
        }

        return ret;
    }
    
    
    /**
     * Sql query execute method
     * @param _conn  connection object
     * @param query  sql query string
     * @param valueList parameter values
     * @param isStatement statement or preparestatement
     * @return success or fail
     * @throws SQLException error info
     * @throws Exception  error info
     */
    //#2019-05-06 수정작업 진행 (배치 카운드 조정 작업 )
    public int executePQueryList(Connection _conn, String query, List<String[][]> valueList, boolean isStatement)
            throws SQLException{
        log.debug("["+query+"]" + isStatement);
        PreparedStatement _pstmt ;
        Statement _stmt ;
        int ret = -1;
        int excuteCount = 0 ;
        if(isStatement){
        	_stmt = _conn.createStatement(); 
            try {
            	
            	for(String[][] values : valueList) {
            		query = getStmtQuery(query, values);
            		_stmt.addBatch(query);
            		excuteCount++;
            		if(excuteCount>500) {
            			_stmt.executeBatch();
                		_stmt.clearBatch();
                		excuteCount=0;
            		}
            		 
            		 
            	}
            	if(excuteCount>0) {
            		_stmt.executeBatch();
            		_stmt.clearBatch();
            	}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
            releaseDB(_stmt);
        }else{
        	_pstmt = _conn.prepareStatement(query);
        	_pstmt.setFetchSize(10);
            try {
            	
            	for(String[][] values : valueList) {
            		_pstmt = setPstmt(_pstmt, values);
            		_pstmt.addBatch();
            		excuteCount++;
            		if(excuteCount>500) {
            			_pstmt.executeBatch();
            			_pstmt.clearBatch();
                		excuteCount=0;
            		}
            		
            	}
            	if(excuteCount>0) {
            		_pstmt.executeBatch();
            		_pstmt.clearBatch();
            	}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
            
            //ret = pstmt.executeUpdate();
            releaseDB(_pstmt);
             
        }

        return ret;
    }
    
    public Connection getConnection() {
    	 
    	if(m_vender.equals("mssql")||m_vender.equals("mysql")||m_vender.equals("mariadb")||m_vender.equals("tibero")||m_vender.equals("postgre")) {
    	//if(m_vender.equals("mysql")||m_vender.equals("tibero")) {
    		try {
				if(l_conn.isClosed()) {
					l_conn = DBPool.getConnection(dbId);
				} 
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    		return l_conn;
	   	}else {
	   		return t_conn;
	   	}
    	 
    }
    public String getQueryData(String query,String seperator) {
    	Connection conn =  getConnection();
        Statement _stmt = null;
        ResultSet _rs = null;
        StringBuilder sb = new StringBuilder();
        try {
           
        	_stmt = conn.createStatement();
        	_rs = _stmt.executeQuery(query);
           
            while(_rs.next()) {
            	sb.append(_rs.getString(1)).append(seperator);
            }
        } catch (Exception e) {
            log.error("[Exception "+StringUtil.StackTraceToString(e)+"]");
        } finally {
            releaseDB(_rs, _stmt);
        }
        return sb.toString();
    }
    
    
    public List<HashMap<String,String>> getQueryData(String query, String[][] values,
            String seperator, boolean isStatement) {
    	Connection conn =  getConnection();
    	//return getQueryData(conn, query, values, seperator, isStatement, "");
    	return null;
    }
    
     
     
    
     
    
    /**
     * classDBName data return
     * @param className
     * @param data
     * @return
     */
    
    
	public String getCustomDBClassData(String className, String data) {
		String ret = "";
		try {
			//System.out.println("getCustomDBClassData"+className+"/"+data);
			Connection _conn = getConnection();
			ret = (String)ClassDBNameFactory.getInstance(className).customData(data,_conn);
			
		} catch (Exception e) {
			e.printStackTrace();
			log.error("[getCustomDBClassData error:" + e.toString() + "]");
		}
		return ret;
	}
    /**
     * className data return
     * @param className
     * @param data
     * @return
     */
    
    
	public String getCustomClassData(String className, String data) {
		String ret = "";
		try {
			ret = (String)ClassNameFactory.getInstance(className).customData(data);
		} catch (Exception e) {
			e.printStackTrace();
			log.error("[getCustomClassData error:" + e.toString() + "]");
		}
		return ret;
	}
	
	/**
     * className data return
     * @param className
     * @param data
     * @return
     */
    
    
	public Object getCustomObjectClassData(String className, String data) {
		Object ret = null;
		try {
			ret = ClassNameFactory.getInstance(className).customDataObject(data);
		} catch (Exception e) {
			e.printStackTrace();
			log.error("[getCustomObjectClassData error:" + e.toString() + "]");
		}
		return ret;
	}
	
	 /**
     * classDBName data return
     * @param className
     * @param data
     * @return
     */
    
    
	public Object getCustomDbObjectClassData(String className, String data) {
		Object ret = null;
		try {
			Connection _conn = getConnection();
			ret =  ClassDBNameFactory.getInstance(className).customDataObject(data,_conn);
			
		} catch (Exception e) {
			e.printStackTrace();
			log.error("[getCustomDBClassData error:" + e.toString() + "]");
		}
		return ret;
	}
	
	
	  /**
     * classDBName data return
     * @param className
     * @param data
     * @return
     */
    
    
	public Object getCustomConfigClassData(String className,String key, String data) {
		Object ret = "";
		try {
			
			ret =  ClassConfigNameFactory.getInstance(className).customData(key,data,_config);
			 
		} catch (Exception e) {
			e.printStackTrace();
			log.error("[getCustomConfigClassData error:" + e.toString() + "]");
		}
		return ret;
	}
	
	
	 
	
	/**
     * className data return
     * @param className
     * @param data
     * @return
     */
    
    
	public List<String> getCustomAterClassData(String className, String data) {
		List<String> ret = new ArrayList<String>();
		try {
			ret = (List<String>)ClassNameFactory.getInstanceAfter(className).customDataAfter(data);
		} catch (Exception e) {
			e.printStackTrace();
			log.error("[getCustomAterClassData error:" + e.toString() + "]");
		}
		return ret;
	}
	
	/**
     * className data return
     * @param className
     * @param data
     * @return
     */
    
    
	public String getCustomClassMapData(String className, String data,Map<String,String[]> resultMemory) {
		String ret = "";
		try {
			ret = (String)ClassNameFactory.getInstance(className).customDataMemory(data,resultMemory);
		} catch (Exception e) {
			e.printStackTrace();
			log.error("[getCustomClassMapData error:" + e.toString() + "]");
		}
		return ret;
	}
	
	
    
    public int execPQuery(String query,String[][] pkArr,String dbtag) throws SQLException, Exception {
        
        int ret ;
        if("".equals(dbtag)) {
        	Connection _conn = getConnection();
        	ret = executePQuery(_conn, query, pkArr, false);
        } else {
        	Connection subCon = sub_conn.get(dbtag);
        	ret = executePQuery(subCon, query, pkArr, false);
        }
        return ret;
    }
    
    
    public int execPQueryList(String query,List<String[][]> pkList,String dbtag) throws SQLException, Exception {
        
        int ret ;
        if("".equals(dbtag)) {
        	Connection _conn = getConnection();
        	ret = executePQueryList(_conn, query, pkList, false);
        } else {
        	Connection subCon = sub_conn.get(dbtag);
        	ret = executePQueryList(subCon, query, pkList, false);
        }
        return ret;
    }
    
    public int execPQuery(String query) throws SQLException, Exception {
        
        int ret = -1 ;
        Connection _con = getConnection();
        ret = executePQuery(_con, query, null, false);
        return ret;
    }
    
    /**
     * bind variable method
     * @param cstmt PreparedStatement
     * @param values parameter values
     * @return PreparedStatement
     * @throws Exception error info
     * @throws SQLException error info
     */
    protected ResultSet executeStateQuery(Statement stmt ,String sql, String[][] values) throws Exception {
        
    	for(int i=0; values != null && i<values.length  ; i++) {
            String value = values[i][0];
            String type = values[i][1];
            log.debug("[cstmt set value is '"+value+"', type is '"+type+"']");
            sql = StringUtil.replaceCnt(sql,"?",value,1);
        }
        log.debug("[executeStateQuery]"+sql);
        return stmt.executeQuery(sql);
    }
    
    
    /**
     * bind variable method
     * @param cstmt PreparedStatement
     * @param values parameter values
     * @return PreparedStatement
     * @throws Exception error info
     * @throws SQLException error info
     */
    protected CallableStatement setCstmt(CallableStatement cstmt, String[][] values) throws Exception {
        for(int i=0; values != null && i<values.length  ; i++) {
            String value = values[i][0];
            String type = values[i][1];
            log.debug("[cstmt set value is '"+value+"', type is '"+type+"']");
            if(((value == null || value.equals("")))) {
            	cstmt.setString(i+1, "");
            	log.debug("[SubQuery] [sql bind variable value(not String Type) is null or empty : Please check SubQuery.]", 2);
           // }else if(type==null) {
            //	cstmt.setString(i+1, value);
            }else if(type.equals("integer")) {
                cstmt.setInt(i+1, Integer.parseInt(value));
            } else if(type.equals("long") || type.equals("biginteger")) {
                cstmt.setLong(i+1, Long.parseLong(value));
            } else if(type.equals("short")) {
                cstmt.setShort(i+1, Short.parseShort(value));
            } else if(type.equals("double")) {
                cstmt.setDouble(i+1, Double.parseDouble(value));
            } else if(type.equals("decimal")) {    //java.math.BigDecimal
                cstmt.setBigDecimal(i+1, BigDecimal.valueOf(Long.parseLong(value)));
            } else {
                cstmt.setString(i+1, value);
            } 
        }
        log.debug("[cstmt set end(success)]");
        return cstmt;
    }
    /**
     * bind variable method
     * @param pstmt PreparedStatement
     * @param values parameter values
     * @return PreparedStatement
     * @throws Exception error info
     * @throws SQLException error info
     */
    protected PreparedStatement setPstmt(PreparedStatement pstmt, String[][] values) throws Exception {
        for(int i=0; values != null && i<values.length  ; i++) {
            String value = values[i][0];
            String type = values[i][1];
            log.debug("[pstmt set value is '"+value+"', type is '"+type+"']");
            if(((value == null || value.equals("")))) {
            	pstmt.setString(i+1, "");
            	log.debug("[SubQuery] [sql bind variable value(not String Type) is null or empty : Please check SubQuery.]", 2);
           // }else if(type==null) {
            //	pstmt.setString(i+1, value);
            }else if(type.equals("integer")) {
                pstmt.setInt(i+1, Integer.parseInt(value));
            } else if(type.equals("long") || type.equals("biginteger")) {
                pstmt.setLong(i+1, Long.parseLong(value));
            } else if(type.equals("short")) {
                pstmt.setShort(i+1, Short.parseShort(value));
            } else if(type.equals("double")) {
                pstmt.setDouble(i+1, Double.parseDouble(value));
            } else if(type.equals("decimal")) {    //java.math.BigDecimal
                pstmt.setBigDecimal(i+1, BigDecimal.valueOf(Long.parseLong(value)));
            } else {
                
                
            	pstmt.setString(i+1, value);
                
                
            } 
        }
        log.debug("[pstmt set end(success)]");
        return pstmt;
    }



    private static String[] className = new String[]{"integer","short", "long","double","bigdecimal","string"};

    /**
     *
     * @param classNm dbms classname
     * @return java ClassName
     */
    private String getClassName(String classNm) {
    	classNm = classNm.toLowerCase();
        String ret = "string";
        for(int i=0;i<className.length; i++) {
            if(classNm.indexOf(className[i])>-1) {
                if(classNm.equals("java.math.bigdecimal")) {
                    ret  = "decimal";
                }else{
                    ret = StringUtil.replace(classNm,"java.lang.","");
                }
                break;
            }else {
            	ret  = "string";
            }
        }
        return ret;
    }

    

    /**
     *
     * @param query sql query string
     * @param values  parameter values
     * @return  String  statement query string
     * @throws Exception error info
     * @throws SQLException  error info
     */
    protected String getStmtQuery(String query, String[][] values) throws Exception{
        for(int i=0; values != null && i<values.length  ; i++) {
            String value = values[i][0];
            String type = values[i][1];
            if(type.equals("string")) {
                query = StringUtil.replace(query, "%s"+(i+1), "'"+value+"'" );
            }else{
                query = StringUtil.replace(query, "%s"+(i+1),value );
            }
        }
        return query;
    }

    /**
     * Clob type processing method
     * @param in InputStream
     * @return String result string
     */
    public String inputStreamToString(InputStream in) {
        String retStr = "";
        if(in == null) return retStr;
        try {
            int size = in.available();
            byte[] buf = new byte[size];
            int n = in.read(buf);
            retStr = new String(buf).trim();
        } catch (Exception e) {
            log.error("[Occur during reading stream error : "
                    +"\n"+StringUtil.StackTraceToString(e)+"\n]");
            return retStr;
        }
        return retStr;
    }


    public ArrayList<String> blobToFile(Connection conn, String query, String[][] values,
                             String preFix, boolean isStatement) {
        String dbFileName = "";
        String retFileName = "";
        int cnt = 0;
        ArrayList<String> arList = new ArrayList<String>();
        log.debug("["+query+"]");
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            if(isStatement){
                stmt = conn.createStatement();
                rs = stmt.executeQuery(query);
            }else{
                pstmt = conn.prepareStatement(query);
                pstmt.setFetchSize(10);
                pstmt = setPstmt(pstmt, values);
                rs = pstmt.executeQuery();
            }
            ResultSetMetaData Meta = rs.getMetaData();
            cnt = Meta.getColumnCount();
            if(cnt != 2){
                String msg = "BlobToFile Please Check  Blob select query\n";
                msg +=  "ex) SELECT filename, blob_content FROM table_name";
                throw new Exception(msg);
            }
            while(rs.next()) {
                for(int i=1;i<=cnt;i++) {
                    String className = Meta.getColumnClassName(i).toLowerCase();
                    if(i == 1 && className.equals("string")){
                        dbFileName = StringUtil.getTimeBasedUniqueID()+rs.getString(i);
                    } else if( i == 2 ) {
                        log.debug("[DBManager  blobToFile...]");
                        InputStream initialStream = rs.getBinaryStream(i);

                        if(initialStream == null) {
                        	log.debug("[Blob data is Empty.]");
                        }

                       // retFileName = FileUtils.copyToFile( is,new File(preFix,dbFileName));
                        
                        File targetFile = new File(FilenameUtils.normalize(preFix)+dbFileName);
                        
                        FileUtils.copyInputStreamToFile(initialStream, targetFile);
                        
                        arList.add(targetFile.getAbsolutePath());
                    } else{
                        throw new Exception("DBController Class Useing Table Struct : file_name(string), file_content(blob)");
                    }
                }
            }
        } catch (SQLException e) {
            log.error("[SQL Error Code : "+e.getErrorCode()+" ]");
            log.error("[SQLException][ "
                    +"\n"+StringUtil.StackTraceToString(e)+"\n]");
            errorDisplay(values);
            throw new Exception(": Make blobToFile error :" + e.getMessage());
            
        } finally {
            releaseDB(rs, pstmt);
            releaseDB(rs, stmt);
            return arList;
        }
    }
    
    /**
     * error parameters
     * @param values
     */
    public void errorDisplay(String values[][]) {
    	
   	 for(int i=0; values != null && i<values.length  ; i++) {
            String value = values[i][0];
            String type = values[i][1];
            log.error("[errorDisplay set value is '"+value+"', type is '"+type+"']");
   	 }
   }

   
    /**
     * Connection Release
     * @param conn Connection
     */
    public void releaseDB1(Connection conn){
        if(conn != null) {
            try{
                conn.close();
            }catch(Exception e){
                log.error("[releaseDB "+e.getMessage()+"]");
            }
        }
        
        
        
    }
    public void releaseDB(ResultSet rs, Statement stmt) {
        releaseDB(rs);
        releaseDB(stmt);
         
    }
    public void releaseDB(CallableStatement cstmt) {
    	if(cstmt != null) {
            try{
            	cstmt.close();
            }catch(Exception e){
                log.error("[releaseDB "+e.getMessage()+"]");
            }
        }
          
    }
    public void releasePreSt() {
        releaseDB(t_rs);
        releaseDB(t_pstmt); 
         
    }
	public void releaseDB(ResultSet _rs) {
		if (_rs != null) {
			try {
				_rs.close();
			} catch (Exception e) {				
			}
		}
	}
    public void releaseDB(Statement _stmt){
        if(_stmt != null) {
        	try{ 
        		_stmt.close(); 
        	}catch(Exception e){
        		log.error("[releaseDB "+e.getMessage()+"]");
        	}
        }
    }
    
    
    public void releaseDB(PreparedStatement pstmt){
        if(pstmt != null) {
        	try{ 
        		pstmt.close(); 
        	}catch(Exception e){
        		log.error("[releaseDB "+e.getMessage()+"]");
        	}
        }
    }    
    public String getString(Object obj) throws SQLException {
		SimpleDateFormat transFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");

		if( obj != null && obj instanceof java.sql.Clob ) {
			StringBuffer strOut = new StringBuffer();
			String str = "";
			BufferedReader br = new BufferedReader(((Clob) obj).getCharacterStream());
			try {
				while ((str = br.readLine()) != null) {
					strOut.append(str);
				}
			} catch (IOException e) {
				log.error("Occurring an exception when to get objectFromClob Because of [{}] ", StringUtil.StackTraceToString(e));
			}
			return strOut.toString();
		} else if (obj != null && obj instanceof java.lang.String) {
			return obj.toString();
		} else if (obj != null && (obj instanceof java.sql.Date || obj instanceof java.sql.Timestamp || obj instanceof java.sql.Time)) {
			
			return transFormat.format(obj) + "Z";
		} else {
			return obj.toString();
		}
	}
    
    
    
    
    /**
     * Get BLOB Attach file name method
     * Not support mysql blob type
     * @param query sql query string
     * @param values prameter values
     * @param preFix  blob file path
     * @param isStatement statement or preparestatement
     * @return  attach file names
     * @throws Exception error info
     */
    public String[] getBolbFileNames(String query, String[][] values, String preFix,
                                     boolean isStatement,String dbtag) throws Exception {
       
        ArrayList<String> vt = new ArrayList<String>();
        if("".equals(dbtag)) {
            vt = blobToFile(t_conn, query, values, preFix, isStatement);
        } else {
        	Connection subCon = sub_conn.get(dbtag);
            vt = blobToFile(subCon, query, values, preFix, isStatement);
        }
        String[] list = new String[vt.size()];
        for(int i=0; i<vt.size(); i++) {
            list[i] = (String) vt.get(i);
        }
        return list;
    }
    
    public void close() {
    	dbClose();
    	releaseClient();
    }
    
    
    public void dbClose() {
    	if(t_conn!=null) {
    		try {
				t_conn.close();
			} catch (SQLException e) {
				 
			}
    	}
    	if(l_conn!=null) {
    		try {
    			l_conn.close();
			} catch (SQLException e) {
				 
			}
    	}
    	if(sub_conn!=null) {
    		if(sub_conn!=null) {
		    	for ( Map.Entry<String, Connection> entry : sub_conn.entrySet() ) {
		    		Connection subCon = entry.getValue();
		    		try {
		    			if(subCon!=null) {
		    				subCon.close();
		    			}
					} catch (SQLException e) {
						 
					}
		    	}
    		}
    	}
    	 
    }
    
    public void releaseClient() {
    	if(doc2serverClientMap!=null) {
	    	for ( Map.Entry<String, Doc2TxtClient> entry : doc2serverClientMap.entrySet() ) {
	         	Doc2TxtClient docClient = entry.getValue();
	     		try {
	     			if(docClient!=null) {
	     				docClient.close();
	     			}
	 			} catch (Exception e) {
	 				 
	 			}
	     	}
    	}
    }
    
    
    
     
    public String getOracleColumnData(ResultSet rs,int idx,String typeName) {
        String value = "";
         
        try {     
	        if (typeName.equalsIgnoreCase("VARCHAR2")||typeName.equalsIgnoreCase("VARCHAR")||typeName.equalsIgnoreCase("NUMBER")) {
	        	value = rs.getString(idx); 
	        }else if(typeName.equals("clob")||typeName.equals("CLOB")){
	        	
	        	Object object = rs.getObject(idx);
	        	if(object!=null) {
	                Reader reader = ((Clob)object).getCharacterStream();
	                BufferedReader br = new BufferedReader(reader);
	                StringBuffer sb = new StringBuffer();
	                String line ;
	                try {
	                    while( (line = br.readLine()) != null){
	                        sb.append(line).append(StringUtil.newLine);
	                    }
	                } catch (IOException e) {
	                    log.error("[Get CLOB ColumnData : "+StringUtil.StackTraceToString(e)+"\n]");
	                }finally{
	                    if(br != null) br.close();
	                    if(reader != null) reader.close();
	                    if(sb != null){
	                        value = sb.toString();
	                    }
	                }
	        	}
	         /**
                }else if(typeName.toLowerCase().indexOf("blob") > -1){
					try {
						InputStream  is  = ((Blob)object).getBinaryStream();
						BufferedReader br = new BufferedReader(new InputStreamReader(is));
						StringBuffer sb = new StringBuffer();
						String line = null;
						try {
						    while( (line = br.readLine()) != null){
						        sb.append(line);
						    }
						} catch (IOException e) {
						    System.out.println(e.getMessage());
						}finally{
						    if(br != null) br.close();
						    if(is != null) is.close();
						    if(sb != null){
						        value = sb.toString();
						    }
						}
					}catch(Exception ex) {
						 ex.printStackTrace();
					}
					**/
	        
	        } else{
	        	log.error("[GetColumnData() [" +idx+"] Unsupported type  Name: " + typeName+ " Please Check Field Type in the Table]");
	        }
        }catch(Exception ex) {
        	log.error("[GetColumnData() : " +"\n"+StringUtil.StackTraceToString(ex)+"\n]");
        }
        return StringUtil.checkNull( value );
    }
    
    
    public String getColumnData(Object object, String typeName) {
        String value = "";
        if(object != null) {
            String className = object.getClass().getName();
            try {
                //clob -> typeName, mssql -> java.sql.Clob type.
                if (className.equals("java.lang.String")
                        && (typeName.equals("text")||typeName.equals("ntext")||typeName.equals("TEXT")||typeName.equals("NTEXT"))
                    ) {
                    value = (String) object;
                }else if(typeName.equals("clob")||typeName.equals("CLOB")
                        ||typeName.equals("text")||typeName.equals("ntext")||typeName.equals("TEXT")||typeName.equals("NTEXT")
                        ||object.getClass().getName().equals("java.sql.Clob")
                        ||object.getClass().getName().equals("oracle.sql.CLOB")
                        ||className.equals("com.tmax.tibero.jdbc.TbBlob")
                        ||className.equals("com.tmax.tibero.jdbc.TbClob")){
                    Reader reader = ((Clob)object).getCharacterStream();
                    BufferedReader br = new BufferedReader(reader);
                    StringBuffer sb = new StringBuffer();
                    String line ;
                    try {
                        while( (line = br.readLine()) != null){
                            sb.append(line).append(StringUtil.newLine);
                        }
                    } catch (IOException e) {
                        log.error("[Get CLOB ColumnData : "+StringUtil.StackTraceToString(e)+"\n]");
                    }finally{
                        if(br != null) br.close();
                        if(reader != null) reader.close();
                        if(sb != null){
                            value = sb.toString();
                        }
                    }
                }else if(typeName.toLowerCase().indexOf("blob") > -1){
					try {
						InputStream  is  = ((Blob)object).getBinaryStream();
						BufferedReader br = new BufferedReader(new InputStreamReader(is));
						StringBuffer sb = new StringBuffer();
						String line = null;
						try {
						    while( (line = br.readLine()) != null){
						        sb.append(line);
						    }
						} catch (IOException e) {
						    System.out.println(e.getMessage());
						}finally{
						    if(br != null) br.close();
						    if(is != null) is.close();
						    if(sb != null){
						        value = sb.toString();
						    }
						}
					}catch(Exception ex) {
						 ex.printStackTrace();
					}
                }else if (className.equals("java.lang.String")) {
                    value = (String) object;
                } else if (className.equals("java.lang.Integer")) {
                    value = object.toString();
                } else if (className.equals("java.lang.Short")) {
                    value = object.toString();
                } else if (className.equals("java.lang.Long")) {
                    value = object.toString();
                } else if (className.equals("java.lang.Double")) {
                    value = object.toString();
                } else if (className.equals("java.math.BigDecimal")) {
                    value = object.toString();
                } else if (className.equals("java.lang.Boolean")) {
                    value = object.toString();
                } else if (className.equals("java.math.BigInteger")) {
                    value = object.toString();
                } else if (className.equals("java.sql.Timestamp")) {
                    value = object.toString();
                } else if (className.equals("java.sql.Date")) {
                    value = object.toString();
                    //oracle.sql.OPAQUE
                    //}else if (className.equals("oracle.xdb.XMLType")) {
                    //     value =  ((XMLType)object).getStringVal();
                } else if (className.equals("com.sybase.jdbc2.tds.SybTimestamp")) {
                    value = object.toString();
                } else if (typeName.equals("RAW")) {
                	ByteArrayOutputStream bos = null;
                	ObjectOutput out = null;
                	try {
	                	bos = new ByteArrayOutputStream();
	                	
	                	out = new ObjectOutputStream(bos);
	                	out.writeObject (object);
	                	value = new String (bos.toByteArray(), "EUC-KR"); 
                	}finally {
                		if(out!=null)
                			out.close();
                		if(bos!=null)
                			bos.close();
	                	 
                	}
                	
                } else {
                	log.error("[GetColumnData() " +"Unsupported class  Name: " + className+ " Please Check Field Type in the Table]");
                }
            } catch (Exception e) {
            	log.error("[GetColumnData() : " +"\n"+StringUtil.StackTraceToString(e)+"\n]");
            }
        } 
        return StringUtil.checkNull( value );
    }
    
    public Object getValue(String type,String sbColumn) {
    	if("int".equals(type)) {
			Integer value = 0;
			try {
				value = Integer.parseInt(sbColumn);
			}catch(Exception ex) {
				value = 0;
			}
			return value;
		}else if("long".equals(type)) {
			Long value = 0L;
			
			try {
				value = Long.parseLong(sbColumn);
			}catch(Exception ex) {
				value = 0L;
			}
			//System.out.println(sbColumn.toString()+"//"+value);
			return value;
		}else if("float".equals(type)) {
			float value = 0f;
			try {
				value = Float.parseFloat(sbColumn);
			}catch(Exception ex) {
				value = 0L;
			}
			return value;
		}else if("double".equals(type)) {
			double value = 0f;
			try {
				value = Double.parseDouble(sbColumn);
				
			}catch(Exception ex) {
				value = 0L;
			}
			return value;
		}else {
			return sbColumn; 
		}
    }
    
    
    public int getRowCount() {
    	return rowCount;
    }
}