Oracle Clob SQL 실행 java class WCOracleStmt

[문한구]

Oracle Clob SQL 실행 java class WCOracleStmt

oracle에서는 대용량의 data를 저장할수 있는 CLOB 이라는 field type이 있습니다.
이 field 에 data를 저장하고, 읽어오는 java class를 소개하겠습니다.
WCOracleStmt.java 입니다.
WCStmt.java 에서 상속받았습니다.

database connection은 아래의 3개의 class를 사용합니다.

WCDBConnectionManager.java
WCDBConnectionPool.java
WCDBConnection.java

아래의 jsp 프로그램은 database connection pool WDLDatabasePool을 사용하며
test_table의 content field에 clob값을 읽어오고 설정하는 sample입니다.

-- sql_oracleClob.jsp 시작
<%@ page language="java" import="wdl.*,wdl.board.*,wdl.board.*,java.util.*,java.sql.*,java.lang.*,
java.io.*,java.io.File " contentType="text/html; charset=EUC-KR"%>
<%@ page import="oracle.sql.CLOB" %>
<%@ page import="oracle.jdbc.driver.OracleResultSet" %>
<%
 WCPage oPage = new WCPage();
 if (oPage.initCtrl(pageContext) > 0)
 {
  return;
 }
 oPage.printCtrl();

 String sClob = executeQuery();
 executeUpdate(sClob);
%>
<%=sClob%>
<%!
public String executeQuery()
{
 WCOracleStmt oStmt = new WCOracleStmt();
 String sSql = "select content from test_table where itemid = 454";
 String sDsn = "WDLDatabasePool";
 String sClob = oStmt.getClobValue(sSql,sDsn);
 return sClob;
}
public void executeUpdate(String sClobValue)
{
 WCOracleStmt oStmt = new WCOracleStmt();
 String sSql = "select content from test_table where itemid = 454 for update";
 String sDsn = "WDLDatabasePool";
 oStmt.setClobValue(sSql,sDsn,sClobValue);
}
%>
-- sql_oracleClob.jsp 끝

아래의 소스가 완벽히 실행하기 위해서는 WCProperties,WCVector,WCLog,WCSystem 등의 source코드가 필요합니다.
이러한 java source는 Web Development Library(www.webdevlib.net)에서 download 받아 사용하실 수 있습니다.
의도적으로 소스코드의 일부만 글로 쓰는 것은 아닙니다. 모든 소스에 대한 설명은 차차 하겠습니다.

-- WCOracleStmt.java 시작
package wdl;
import java.io.BufferedWriter;
import java.io.Reader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.CLOB;
 public class WCOracleStmt extends WCStmt
 {
  public synchronized String getClobValue(String sSql,String sDsn)
  {
   WCDBConnection oDBConn = null;
   try
   {
    WCDBConnectionManager m_connMgr = null;
    m_connMgr = WCDBConnectionManager.getInstance();
    oDBConn = m_connMgr.getConnection(sDsn);
    if (oDBConn == null)
     return null;
    String sRet = getClobValueByConn(oDBConn,sSql,sDsn);
    m_connMgr.freeConnection(sDsn, oDBConn);
    oDBConn = null;
    return sRet;
   }
   catch (Exception ex)
   {
    if (oDBConn != null)
    {
     m_connMgr.freeConnection(sDsn, oDBConn);
     oDBConn = null;
    }
    WCLog.getInstance().printLog("exception at WCOracleStmt.getClobValueByConn "+ex.toString());
    ex.printStackTrace();
   }
   return null;
  }
  private String getClobValueByConn(WCDBConnection oDBConn,String sSql,String sDsn)
  {
   try
   {
    m_sDsn = sDsn;
    Connection oConn = oDBConn.getConnection();
    String sRet = null;
    try
    {
     sRet = getClobValueEx(oConn,sSql);
    }
    catch (Exception ex)
    {
     WCLog.getInstance().printLog("exception at WCOracleStmt.getClobValue "+ex.toString());
    }
    return sRet;
   }
   catch (Exception ex)
   {
    WCLog.getInstance().printLog("exception at WCOracleStmt.getClobValueByConn "+ex.toString());
    ex.printStackTrace();
   }
   return null;
  }
  private String getClobValueEx(Connection oConn,String sSql)
  {
   StringBuffer output  = new StringBuffer();
   PreparedStatement pstmt = null;
   ResultSet rs    = null;
   String outstr   = "";
   try
   {
    oConn.setAutoCommit(false);
    pstmt = oConn.prepareStatement(sSql);
    rs = pstmt.executeQuery();
    this.log(m_sDsn,sSql);
    oConn.setAutoCommit(false);
    if(rs.next())
    {
     Reader input = rs.getCharacterStream(1);
     char[] buffer = new char[1024];
     int byteRead;
     while((byteRead=input.read(buffer,0,1024))!=-1)
     {
      output.append(buffer,0,byteRead);
     }
     input.close();
    }
    outstr = output.toString();
   }
   catch (Exception e)
   {
    WCLog.getInstance().printLog("exception at WCOracleStmt.getClobValueEx "+e.toString());
   }
   finally
   {
    try
    {
     oConn.commit();
     oConn.setAutoCommit(true);
    }
    catch (Exception e)
    {
     WCLog.getInstance().printLog("exception at WCOracleStmt.getClobValueEx "+e.toString());
     e.printStackTrace();
    }
    if (rs!=null)
    {
     try
     {
      rs.close();
     }
     catch (Exception e)
     {
      WCLog.getInstance().printLog("exception at WCOracleStmt.getClobValueEx "+e.toString());
      e.printStackTrace();
     }
    }
    if (pstmt!=null)
    {
     try
     {
      pstmt.close();
     }
     catch (Exception e)
     {
      WCLog.getInstance().printLog("exception at WCOracleStmt.getClobValueEx "+e.toString());
      e.printStackTrace();
     }
    }
   }
   return outstr;
  }
  public boolean setClobValue(String sSql,String sDsn,String sClobValue)
  {
   WCDBConnection oDBConn = null;
   try
   {
    WCDBConnectionManager m_connMgr = null;
    m_connMgr = WCDBConnectionManager.getInstance();
    oDBConn = m_connMgr.getConnection(sDsn);
    if (oDBConn == null)
     return false;
    boolean bRet = setClobValueByConn(oDBConn,sSql,sDsn,sClobValue);
    m_connMgr.freeConnection(sDsn, oDBConn);
    oDBConn = null;
    return bRet;
   }
   catch (Exception ex)
   {
    if (oDBConn != null)
    {
     m_connMgr.freeConnection(sDsn, oDBConn);
     oDBConn = null;
    }
    WCLog.getInstance().printLog("exception at WCOracleStmt.getClobValueByConn "+ex.toString());
    ex.printStackTrace();
   }
   return false;
  }
  private boolean setClobValueByConn(WCDBConnection oDBConn,String sSql,String sDsn,String sClobValue)
  {
   try
   {
    m_sDsn = sDsn;
    if (oDBConn == null)
    {
     return false;
    }
    Connection oConn = oDBConn.getConnection();
    boolean bRet = false;
    try
    {
     bRet = this.setClobValueEx(oConn,sSql,sClobValue);
    }
    catch (Exception ex)
    {
     WCLog.getInstance().printLog("exception at WCOracleStmt.setClobValue "+ex.toString());
    }
    return bRet;
   }
   catch (Exception ex)
   {
    WCLog.getInstance().printLog("exception at WCOracleStmt.getClobValue "+ex.toString());
    ex.printStackTrace();
   }
   return false;
  }
  private boolean setClobValueEx(Connection oConn,String sSql,String sClobValue)
  {
   PreparedStatement pstmt = null;
   ResultSet rs = null;
   boolean flag = false;
   try
   {
    oConn.setAutoCommit(false);
    pstmt = oConn.prepareStatement(sSql);
    rs = pstmt.executeQuery();
    this.log(m_sDsn,sSql);
    oConn.setAutoCommit(false);           
    if(rs.next())
    {
     CLOB cl = ((OracleResultSet)rs).getCLOB(1);
     BufferedWriter writer = new BufferedWriter(cl.getCharacterOutputStream());
     writer.write(sClobValue);
     cl = null;
     writer.close();
    }
   }
   catch(Exception e)
   {
    WCLog.getInstance().printLog("exception at WCOracleStmt.setClobValueEx "+e.toString());
   }
   finally
   {
    try
    {
     oConn.commit();
     oConn.setAutoCommit(true);
    }
    catch (Exception e)
    {
     WCLog.getInstance().printLog("exception at WCOracleStmt.getClobValueEx "+e.toString());
     e.printStackTrace();
    }
    if (rs!=null)
    {
     try
     {
      rs.close();
     }
     catch (Exception e)
     {
      WCLog.getInstance().printLog("exception at WCOracleStmt.getClobValueEx "+e.toString());
      e.printStackTrace();
     }
    }
    if (pstmt!=null)
    {
     try
     {
      pstmt.close();
     }
     catch (Exception e)
     {
      WCLog.getInstance().printLog("exception at WCOracleStmt.getClobValueEx "+e.toString());
      e.printStackTrace();
     }
    }
   }
   return flag ;
  }
 }
-- WCOracleStmt.java 끝

첨부파일 : sql_oracleClob.zip

sql_oracleClob.jsp
WCDBConnection.java
WCDBConnectionManager.java
WCDBConnectionPool.java
WCOracleStmt.java
WCProperties.java
WCStmt.java

출처 : 고급 웹 UI 개발 라이브러리 Web Development Library 소스공개 : http://www.webdevlib.net