/**
 * @(#)file    BlobUtils.java
 * @(#)author  [개발자명]
 * @(#)version 1.0
 * @(#)date    2014-06-24
 * @(#)since   JDK 1.6
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 */
package com.udapsoft.waf.common.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.logger.Logger;
import weblogic.jdbc.vendor.oracle.OracleThinBlob;


public class BlobUtils {

	public BlobUtils() {
		super();
	}
	
    /**
     * 입력받은 (String)strData  를 clob 컬럽에 등록시킨다. 
     * @param conn
     * @param tableName        테이블명
     * @param columnName       컬럼명
     * @param keyColumnName    키컬럼명
     * @param keyColumnValue   키컬럼값
     * @param file             등록할 파일
     * @throws Exception
     */
    public static void setBlob(Connection conn, String tableName, String columnName, String keyColumnName, String keyColumnValue, File file) throws Exception{
        Statement stmt = null;
        ResultSet rs = null;
        
        FileInputStream fis = null;
        OutputStream os = null;

        String up_sql = "UPDATE " + tableName
                      + "   SET " + columnName    + " = empty_blob() " 
                      + " WHERE " + keyColumnName + " = " + keyColumnValue;
        String sel_sql = " SELECT " + columnName 
                       + "   FROM " + tableName
                       + "  WHERE " +keyColumnName + " = " + keyColumnValue + " FOR UPDATE ";
        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(up_sql);

            rs = stmt.executeQuery(sel_sql);
            if( rs.next() ) {
            	
                Blob blob = rs.getBlob(1);

                fis = new FileInputStream(file);
                byte[] temp = new byte[(int)file.length()];
                fis.read(temp);
                
                os = blob.setBinaryStream(0);
                os.write(temp);
            }
        }
        catch(Exception e) {
            Logger.err.printStackTrace(e);
        }
        finally {
            if( rs     != null ) rs.close();
            if( stmt   != null ) stmt.close();   
            if( fis    != null ) fis.close();
            if( os     != null ) os.close();
        }
    }    

    /**
     * 입력받은 File을 BLOB Column에 등록시킨다. 
     * @param conn
     * @param tableName        테이블명
     * @param columnName       컬럼명
     * @param keyColumnName    키컬럼명
     * @param keyColumnValue   키컬럼값
     * @param file             등록할 파일
     * @throws Exception
     */
    public static void setBlob2(Connection conn, String tableName, String columnName, String whereSql, File file) throws Exception{
        Statement stmt = null;
        Statement stmt1 = null;
        ResultSet rs = null;
        
        FileInputStream fis = null;
        OutputStream os = null;

        boolean isLocalTrx = false;
        
        String up_sql = "UPDATE " + tableName
                      + "   SET " + columnName    + " = empty_blob() " 
                      + " WHERE " + whereSql;
        String sel_sql = " SELECT " + columnName 
                       + "   FROM " + tableName
                       + "  WHERE " + whereSql + " FOR UPDATE ";
        try {
        	if( conn.getAutoCommit() == true )
        		isLocalTrx = true;
        	
        	if( isLocalTrx )
        		conn.setAutoCommit(false);
        	
            stmt = conn.createStatement();
            stmt.executeUpdate(up_sql);
            
            stmt1 = conn.createStatement();
            rs = stmt1.executeQuery(sel_sql);
            if( rs.next() ) {
            	/*Oracle 10g이하 */
            	OracleThinBlob blob = (OracleThinBlob)rs.getBlob(1);
            	
            	long fileLength = (long)file.length();				   				   
    			 
            	fis = new FileInputStream(file);
            	os = blob.getBinaryOutputStream();
            	
            	int size = blob.getBufferSize();
            	byte[] buffer = new byte[size];
            	int length = -1;
		 
            	while ((length = fis.read(buffer)) != -1) {
            		os.write(buffer, 0, length);
            	}
            	
            	/*Oracle 10g이상 */
//            	Blob blob = (Blob)rs.getBlob(1);
//            	long fileLength = (long)file.length();				   				   
//            	fis = new FileInputStream(file);
//				os = blob.setBinaryStream(0);
//				int size = (int)blob.length();
//            	byte[] buffer = new byte[size];
//            	int length = -1;
//            	while ((length = fis.read(buffer)) != -1) {
//            		os.write(buffer, 0, length);
//            	}
            }
        }
        catch(Exception e) {
            Logger.err.printStackTrace(e);
            WAFLogger.debug(e.getLocalizedMessage());
            WAFLogger.debug(e.getMessage());
            WAFLogger.debug(e.getStackTrace());
        	if( isLocalTrx )
        		conn.rollback();
        	throw e;
        }
        finally {
            if( rs     != null ) rs.close();
            if( stmt   != null ) stmt.close(); 
            if( stmt1  != null ) stmt1.close();
            if( fis    != null ) fis.close();
            if( os     != null ) os.close();
        	if( isLocalTrx ) {
        		conn.commit();
        		conn.setAutoCommit(true);
        	}
        }
    } 
}