/**
 * @(#)file    CommonKey.java
 * @(#)author  chj
 * @(#)version 1.0
 * @(#)date    2016. 6. 24.
 * @(#)since   JDK 1.6
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 */
package com.udapsoft.waf.common.util;

import java.io.CharArrayReader;
import java.io.Reader;
import java.io.Writer;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import kr.co.hsnc.common.base.WAFLogger;
import weblogic.jdbc.vendor.oracle.OracleThinClob;


public class ClobUtils {

	public ClobUtils() {
        super();
    }

    /**
     * Clob 데이터 유형을 String으로 변경하여 반환한다.
     * @param clob
     * @return
     * @throws Exception
     */
    public static String getClob(Clob clob) throws Exception{
        String strClob = "";
        Reader reader = null;
        try {
            if(clob != null){              
                reader = clob.getCharacterStream();
                char[] buffer = new char[1024];
                int length = 0;
                while( (length = reader.read(buffer)) != -1 ) {                  
                    for( int j=0; j<length; j++ ) {
                        strClob = strClob+buffer[j];
                    }
                }
            }        
        }
        finally {
            if(reader != null) reader.close();                    
        }   
        return strClob;
    }

    /**
     * 입력받은 (String)strData  를 clob 컬럽에 등록시킨다. 
     * @param conn
     * @param tableName        테이블명
     * @param columnName       컬럼명
     * @param keyColumnName    키컬럼명
     * @param keyColumnValue   키컬럼값
     * @param strData          등록할 데이터
     * @throws Exception
     */
    public static void setClob(Connection conn, String tableName, String columnName, String keyColumnName, String keyColumnValue, String strData) throws Exception{
        Statement stmt = null;
        Statement stmt1 = null;
        ResultSet rs = null;
        Writer writer = null;
        Reader reader = null;
        boolean isLocalTrx = false;
        String up_sql = "UPDATE " + tableName
                      + "   SET " + columnName    + " = empty_clob() " 
                      + " WHERE " + keyColumnName + " = " + keyColumnValue;
        String sel_sql = " SELECT " + columnName 
                       + "   FROM " + tableName
                       + "  WHERE " +keyColumnName + " = " + keyColumnValue + " FOR UPDATE ";
        try {
        	if( conn.getAutoCommit() == true )
        		isLocalTrx = true;
        	
        	if( isLocalTrx )
        		conn.setAutoCommit(false);
        	
            stmt = conn.createStatement();
            stmt.executeUpdate(up_sql);

            rs = stmt.executeQuery(sel_sql);
            if (rs.next()) {
                Clob clob = rs.getClob(1);

                writer = ((OracleThinClob) clob).getCharacterOutputStream();
                reader = new CharArrayReader(strData.toCharArray());
                char[] buffer = new char[1024];
                int read = 0;
                while ((read = reader.read(buffer, 0, 1024)) != -1) {
                    writer.write(buffer, 0, read);
                }
            }
        }
        catch(Exception e) {
        	WAFLogger.error(e);
        	if( isLocalTrx )
        		conn.rollback();
        	throw e;
        }
        finally {
            if( rs     != null ) rs.close();
            if( stmt   != null ) stmt.close();   
            if( writer != null ) writer.close();
            if( reader != null ) reader.close();
        	if( isLocalTrx ) {
        		conn.commit();
        		conn.setAutoCommit(true);
        	}
        }
    }    

    /**
     * 입력받은 (String)strData  를 clob 컬럽에 등록시킨다. 
     * @param conn
     * @param tableName        테이블명
     * @param columnName       컬럼명
     * @param keyColumnName    키컬럼명
     * @param keyColumnValue   키컬럼값
     * @param strData          등록할 데이터
     * @throws Exception
     */
    public static void setClob(Connection conn, String tableName, String columnName, String whereString, String strData) throws Exception{
        Statement stmt = null;
        ResultSet rs = null;
        Writer writer = null;
        Reader reader = null;
        boolean isLocalTrx = false;
        String up_sql = "UPDATE " + tableName
                      + "   SET " + columnName    + " = empty_clob() " 
                      + whereString;
        String sel_sql = " SELECT " + columnName 
                       + "   FROM " + tableName
                       + whereString + " FOR UPDATE ";
        try {
        	if( conn.getAutoCommit() == true )
        		isLocalTrx = true;
        	
        	if( isLocalTrx )
        		conn.setAutoCommit(false);
        	
            stmt = conn.createStatement();
            stmt.executeUpdate(up_sql);

            rs = stmt.executeQuery(sel_sql);
            if (rs.next()) {
                Clob clob = rs.getClob(1);

                writer = ((OracleThinClob) clob).getCharacterOutputStream();
                reader = new CharArrayReader(strData.toCharArray());
                char[] buffer = new char[1024];
                int read = 0;
                while ((read = reader.read(buffer, 0, 1024)) != -1) {
                    writer.write(buffer, 0, read);
                }
            }
        }
        catch(Exception e) {
        	if( isLocalTrx )
        		conn.rollback();
        	throw e;
        }
        finally {
            if( rs     != null ) rs.close();
            if( stmt   != null ) stmt.close();   
            if( writer != null ) writer.close();
            if( reader != null ) reader.close();
        	if( isLocalTrx ) {
        		conn.commit();
        		conn.setAutoCommit(true);
        	}
        }
    }
    
}