BLOB/CLOB 사용

JDBC에서 LOB 데이터를 처리하는 인터페이스는 JDBC 4.0 스펙을 기반으로 구현되었으며, 다음과 같은 제약 사항을 가진다.

// JDK 1.6 이상

import java.sql.*;

Connection conn = DriverManager.getConnection(url, id, passwd);

Blob blob = conn.createBlob();

// JDK 1.6 미만

import java.sql.*;

import cubrid.jdbc.driver.*;

Connection conn = DriverManager.getConnection(url, id, passwd);

Blob blob = ((CUBRIDConnection)conn).createBlob();

LOB 데이터 저장

LOB 타입 데이터를 바인딩하는 방법은 다음과 같다. 예제를 참고한다.

예제 1

Class.forName("cubrid.jdbc.driver.CUBRIDDriver");

Connection conn = DriverManager.getConnection ("jdbc:cubrid:localhost:33000:image_db:::", "", "");

PreparedStatement pstmt1 = conn.prepareStatement("INSERT INTO doc(image_id, doc_id, image) VALUES (?,?,?)");

pstmt1.setString(1, "image-21");

pstmt1.setString(2, "doc-21");

 

//Creating an empty file in the file system

Blob bImage = conn.createBlob();

byte[] bArray = new byte[256];

 

//Inserting data into the external file. Position is start with 1.

bImage.setBytes(1, bArray);

//Appending data into the external file

bImage.setBytes(257, bArray);

pstmt1.setBlob(3, bImage);

pstmt1.executeUpdate();

예제 2

Class.forName("cubrid.jdbc.driver.CUBRIDDriver");

Connection conn = DriverManager.getConnection ("jdbc:cubrid:localhost:33000:image_db:::", "", "");

conn.setAutoCommit(false);

PreparedStatement pstmt1 = conn.prepareStatement("SELECT image FROM doc WHERE image_id = ? ");

pstmt1.setString(1, "image-21");

ResultSet rs = pstmt1.executeQuery();

 

while (rs.next())

{

Blob bImage = rs.getBlob(1);

PreparedStatement pstmt2 = conn.prepareStatement("INSERT INTO doc(image_id, doc_id, image) VALUES (?,?,?)");

pstmt2.setString(1, "image-22")

pstmt2.setString(2, "doc-22")

pstmt2.setBlob(3, bImage);

pstmt2.executeUpdate();

pstmt2.close();

}

pstmt1.close();

conn.commit();

conn.setAutoCommit(true);

conn.close();

LOB 데이터 조회

LOB 타입 데이터를 조회하는 방법은 다음과 같다.

예제 1

Connection conn = DriverManager.getConnection ("jdbc:cubrid:localhost:33000:image_db:::", "", "");

 

// ResetSet에서 직접 데이터 인출

PrepareStatement pstmt1 = conn.prepareStatement("SELECT content FROM doc_t WHERE doc_id = ? ");

pstmt2.setString(1, "doc-10");

ResultSet rs = pstmt1.executeQuery();

while (rs.next())

{

String sContent = rs.getString(1);

System.out.println("doc.content= "+sContent.);

}

예제 2

Connection conn = DriverManager.getConnection ("jdbc:cubrid:localhost:33000:image_db:::", "", "");

 

//ResultSet에서 Blob 객체를 얻고 Blob 객체로부터 데이터 인출

PrepareStatement pstmt2 = conn.prepareStatement(“SELECT image FROM image_t WHERE image_id = ?”);

pstmt2.setString(1,”image-20”);

ResultSet rs = pstmt2.executeQuery();

while (rs.next())

{

Blob bImage = rs.getBlob(1);

Bytes[] bArray = bImange.getBytes(1, (int)bImage.length());

}