개발문서 ORACLE CLOB 2008.6.9 ( 주 ) 아이캔매니지먼트 개발팀황순규 0. clob개요 1. lob과 long의비교와 clob와 blob 2. 테이블생성쿼리 ( 차이점-추가사항 ) 3. select 쿼리 4. insert 쿼리및 jdbc프로그래밍 5. update 쿼리및 jdbc프로그래밍 (4, 5). putclobdata() 클래스 6. select 와 like 7. clob함수들 0. clob 개요 - 문자대형객체 (Character). Oracle Server 는 CLOB 과 VARCHAR2 사이에암시적변환을수행한다. - 대형데이터를저장하는데사용한다. ( 최고 4GB 까지저장 ) 1. lob 과 long 의비교, clob 와 blob Long 과 Lob 는크기가큰오브젝트를저장하기위한데이터유형이다 * Long 1. Table 에하나의컬럼만생성할수있다. 2. Long 데이터유형은 2GB 의문자열데이터를저장할수있다. 3. SELECT 결과로데이터를리턴한다. 4. 데이터를직접저장한다. 5. 오브젝트유형을지원하지않는다.
* Lob 1. Table 에여러개의컬럼을생성할수있다. 2. Lob 데이터유형은 4GB 의문자열데이터를저장할수있다. 3. SELECT 결과로위치를리턴한다. 4. 데이터를직접저장또는간접저장한다. 5. 오브젝트유형을지원한다. * LOB TYPE 유형들 Blob 이진 (Binary) 데이터를저장합니다. Clob 기본서버문자집합을기준으로 1 BYTE의데이터베이스문자 (Character) 집합데이터를저장합니다. 2. 테이블생성쿼리 ( 차이점 - 추가사항 ) CREATE TABLE CAR ( ID NUMBER(6) PRIMARY KEY NOT NULL, NAME VARCHAR2(20), PRICE NUMBER(10), REVIEW CLOB) - 테이블생성시 CLOB 타입컬럼을선언하고, 데이터입력. - 따로 SIZE 는지정하지않는다. 3. select Method 및 jdbc 프로그래밍 /** * 설명 : clob 읽어오기 * @param column seq * @return Entity */ public Entity select( int seq ) throws Exception{ Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; Reader reader = null; StringBuffer stringbuffer = new StringBuffer(); APDBConnection apdbconnection = new APDBConnection(this); HopedreamEntity entity = new HopedreamEntity(); char[] charbuffer = new char[1024]; int read = 0; String sql = ""; sql += " /* 상세보기 */ "; sql += " SELECT SEQ, CONTENTS "; sql += " FROM KBCF10MT "; sql += " WHERE SEQ =? ";
try{ conn = apdbconnection.getconnection(); pstmt = conn.preparestatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); pstmt.setint(1, seq); rs = pstmt.executequery(); if(rs.next()){ entity.seq = rs.getint("seq"); //clob 타입데이터읽어오기시작 try { reader = rs.getcharacterstream("contents"); while ((read = reader.read(charbuffer, 0, 1024))!= -1) stringbuffer.append(charbuffer, 0, read); entity.contents = stringbuffer.tostring(); //CLOB 데이터를불러오기위해서는위에서처럼 //rs.getcharacterstream("content") 로불러서 StringBuffer 에담아야주자. catch (IOException e) { finally { if (reader!= null) reader.close(); //clob 타입데이터읽어오기끝 catch(sqlexception sqlex){ System.out.println(sqlEX);sqlEX.printStackTrace(); try{conn.rollback();catch(sqlexception e){ throw new SQLException(); catch(exception etcex){system.out.println(etcex);etcex.printstacktrace(); try{conn.rollback();catch(sqlexception e){ throw new Exception(); finally{ try{if(rs!= null) rs.close();catch(sqlexception e){ try{if(pstmt!= null) pstmt.close();catch(sqlexception e){ try{if(conn!= null) apdbconnection.closeconnection(); catch(exception e) { return entity; 4. insert Method 및 jdbc 프로그래밍 /** */ * 설명 : 게시물등록 ( 새글쓰기 ) * @param Entity * @return insert_rows public int insert(entity entity) throws Exception{ entity.seq = maxseq( ); Connection conn = null;
PreparedStatement pstmt = null; ResultSet rs = null; APDBConnection apdbconnection = new APDBConnection(this); String sql = ""; String sql1 = ""; int insert_rows = 0; sql += " /* 글쓰기 */ "; sql += " INSERT INTO KBCF10MT( "; sql += " SEQ, CONTENTS "; sql += " )VALUES( "; // 단 CLOB 필드에 null 을집어넣는다. EMPTY_CLOB() 함수이용 ( 저장공간확보 ) sql += "?, EMPTY_CLOB()"; sql += " ) "; //CLOB 필드에 LOCK 을걸고스트림전송하기위한쿼리 //* 필수 import oracle.sql.clob; sql1 += " /* 글쓰기 (clop) */ "; sql1 += " SELECT CONTENTS "; sql1 += " FROM KBCF10MT "; sql1 += " where seq =? for update "; try{ conn = apdbconnection.getconnection(); //CLOB column 을업데이트하는동안다른 process 의접근을막기위해 //setautocommit(false) 를반드시설정해야한다. conn.setautocommit(false); pstmt = conn.preparestatement(sql); pstmt.setint(1, entity.seq); update_rows = pstmt.executeupdate(); pstmt.close(); //CLOB 처리를위한 JDBC 코딩 - 시작 pstmt = conn.preparestatement(sql1); pstmt.setint(1, entity.seq); rs = pstmt.executequery(); // 스트림전송을위해배열을이용한다. //CLOB 필드에들어갈내용이배열에들어있음. String[] arrval = { entity.contents ; //oracle.sql.clob 클래스이용한스트림전송함수 putclobdata( rs, arrval ); //CLOB 처리를위한 JDBC 코딩 - 끝 rs.close(); pstmt.close(); if(insert_rows>0){conn.commit(); catch(sqlexception sqlex){ try{conn.rollback();catch(sqlexception e){ throw new SQLException(); catch(exception etcex){
try{conn.rollback();catch(sqlexception e){ throw new Exception(); finally{ try{if(pstmt!= null) pstmt.close();catch(sqlexception e){ try{if(conn!= null) apdbconnection.closeconnection(); catch(exception e) { return insert_rows; 5. update 쿼리및 jdbc 프로그래밍 /** * 설명 : 글수정 * @param communityentity * @return update_rows */ public int update( Entity entity ) throws Exception{ Connection conn = null; PreparedStatement pstmt = null; APDBConnection apdbconnection = new APDBConnection(this); ResultSet rs = null; String sql = ""; String sql1 = ""; int update_rows = 0; sql += " /* 글수정 */ "; sql += " UPDATE KBCF10MT SET "; // 일단아무거나집어넣는다. sql += " CONTENTS = EMPTY_CLOB(), "; sql += " WHERE SEQ =? "; //CLOB 필드에 LOCK 을걸고스트림으로전송하기위한쿼리시작 sql1 += " /* 글수정 (clop) */ "; sql1 += " SELECT CONTENTS "; sql1 += " FROM KBCF10MT "; //for update 를이용해 CLOB column 을 lock 한다. sql1 += " where seq =? for update "; //CLOB 필드에 LOCK 을걸고스트림으로전송하기위한쿼리끝 try{ conn = apdbconnection.getconnection(); //CLOB column을업데이트하는동안다른 process의접근을막기위해 //setautocommit(false) 를반드시설정해야한다. conn.setautocommit(false); pstmt = conn.preparestatement(sql); pstmt.setint(1, entity.seq); update_rows = pstmt.executeupdate(); pstmt.close();
//CLOB 처리를위한 JDBC 코딩 - 시작 pstmt = conn.preparestatement(sql1); pstmt.setint(1, entity.seq); rs = pstmt.executequery(); // 스트림전송을위해배열을이용한다. //CLOB 필드에들어갈내용이배열에들어있음. String[] arrval = { entity.contents ; //oracle.sql.clob 클래스이용한스트림전송함수 putclobdata( rs, arrval ); //CLOB 처리를위한 JDBC 코딩 - 끝 if(update_rows>0){ conn.commit(); else{ conn.rollback(); catch(sqlexception sqlex){ sqlex.printstacktrace(); try{conn.rollback();catch(sqlexception e){ throw new SQLException(); catch(exception etcex){ try{conn.rollback();catch(sqlexception e){ throw new Exception(); finally{ try{if(pstmt!= null) pstmt.close();catch(sqlexception e){ try{if(conn!= null) apdbconnection.closeconnection();catch(exception e){ return update_rows; (4, 5). putclobdata() Method /** * ResultSet의 CLOB 필드에데이터넣기 * @param java.sql.resultset rs * @return int */ public void putclobdata( ResultSet rs, String[] arrval ) throws Exception { //rs - Lock 걸린 ResultSet Writer target = null; Reader source = null; try { if ( rs.next() ) { //CLOB 필드에들어갈내용이배열에들어있음. // - 한글자씩전송. 배열당한글자씩들어있다. for ( int i=0; i<arrval.length; i++ ) { //System.out.println( "Contents Length : " + arrval[i].length() ); //rs 를 CLOB 타입으로받음 oracle.sql.clob clobdata = (oracle.sql.clob) rs.getclob( i+1 ); //OutputStream 될곳을 rs 의 CLOB 로설정
target = clobdata.getcharacteroutputstream(); // 배열의값을 Reader형태로받아서전송준비. source = new CharArrayReader( arrval[i].tochararray() ); //1KB단위로읽어서전송하기위한버퍼설정 ( 배열 ) char[] buf = new char[1024]; int read = 0; // 실제전송 while ( (read = source.read( buf, 0, 1024 ))!= -1 ) target.write( buf, 0, read ); catch (Exception e) { e.printstacktrace(); throw e; finally { if ( target!= null ) target.close(); if ( source!= null ) source.close(); 6. select 와 like /* clob 검색 */ SELECT * FROM 테이블명 where 1 = 1 and DBMS_LOB.INSTR(contents, ' 검색어 ')> 0 CLOB 에서는 like 를쓰지않습니다. Like 는글전체를검색을못하므로 DBMS_LOB.INSTR 을이용해서검색을한다. 7. clob 함수들 1) SUBSTR - 파일에서지정된바이트수만큼추출하는데사용한다. 사용법 ) DBMS_LOB.SUBSTR(CLOB 컬럼, 전체 Row 가져오는길이, OFFSET: 시작위치 ) 2) INSTR - 지정한위치부터찾고자하는패턴과일치하는 i 번째패턴을검색한다.
- 패턴이없으면 0, 패턴이있으면해당 OFFSET 을반환 사용법 ) DBMS_LOB.INSTR(CLOB 컬럼, 검색된단어, OFFSET, i 번째에있는검색된단어 ) 3) GETLENGTH - 오브젝트의실제길이를 byte 단위로알수있다. 사용법 ) DBMS_LOB.GETLENGTH(CLOB 컬럼 ) 4) READ - 파일의일부또는전체를메모리로읽어들인다. 사용법 ) READ(CLOB 컬럼, 읽을양, 시작위치, 버퍼위치 ) 5) COMPARE - 전체또는일부 LOB 을비교할경우사용 - 중복파일제거를위해두개외부파일을비교할때사용. - 데이터가똑같으면 0, 다르면 0 이아닌값을반환한다. 사용법 ) COMPARE(LOB1, LOB2, 비교할 byte 수, LOB1 비교시작위치, LOB2 비교시작위치 ) 6) FILEGETNAME - CLOB 컬럼과연결된디렉토리오브젝트와파일이름을반환 사용법 ) FILEGETNAME(CLOB 컬럼, 디렉토리오브젝트, 파일이름 ) 7) FILEISOPEN - BFILE 사용전에파일이이미열려있는지확인한다. - 열려있으면 1, 닫혀있으면다른정수값을리턴한다. 사용법 ) FILEOPEN(CLOB 컬럼 ) 8) FILEEXISTS
- 파일에지정한곳에제대로붙어있는지확인할경우사용한다. - 있으면 1, 없으면 0 을리턴한다. 사용법 ) FILEEXISTS(CLOB 컬럼 ) 9) FILECLOSEALL - 모든 BFILE 을처리한뒤세션을끝낼경우, 모든 BFILE 을닫을수있다. 사용법 ) FILECLOSEALL 10) FILECLOSE - BFILE 을다읽은뒤항상파일을닫는다. 사용법 ) FILECLOSE(CLOB 컬럼 ) 11) FILEOPEN - BFILE 을사용하려면우선파일을열어야한다. 사용법 ) FILEOPEN(CLOB 컬럼, DBMS_LOB.FILE_READONLY) 12) BFILENAME - BFILENAME 함수를통해테이블에 BFILE 에대한 CLOB 컬럼을삽입한다. - 파일삭제되거나이동될경우 CLOB 컬럼은예전위치를가리키고있기때문에에러가난다. 사용법 ) BFILENAME( 디렉토리오브젝트, 파일이름 )