Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 | 

Using BLOB/CLOB

The interfaces that porcess LOB data in JDBC is implemented based on JDBC 4.0 specification. The constraints of interfaces are as follows:

  • It supports sequential writes only when creating the objects of BLOB or CLOB. Writing to arbitary locations are not supported.
  •   You cannot change the data of BLOB or CLOB by calling methods of BLOB or CLOB object which are received from ResultSet.
  •   It does not support Blob.truncate, Clob.truncate, Blob.position, and Clob.position.
  •   You cannot bind LOB data by calling PreapredStatement.setAsciiStream, PreparedStatement.setBinaryStream, and PreparedStatement.setCharacterStream methods of BLOB/CLOB type columns.
  •   To use BLOB/CLOB types in an environment where JDBC 4.0 specification is not supported such as JDB version 1.5 or earlier, you must convert a conn object to CUBRIDConnection, explicitly. See the example below.

// JDK 1.6 or later

import java.sql.*;

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

Blob blob = conn.createBlob();

// JDK 1.5 or earlier

import java.sql.*;

import cubrid.jdbc.driver.*;

 

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

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

Saving LOB Data

The way to bind LOB type data is as follows:

  • Create java.sql.Blob or java.sql.Clob object and save the file contents in the object. Use, then, setBlob() or setClob() of PreparedStatement (example 1).
  • Perform query and get java.sql.Blob or java.sql.Clob object from the ResultSet object. Bind, then, the object in PreparedStatement (example 2).

Example 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();

Example 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();

Getting LOB Data

The way to get LOB type data is as follows:

  • Get data directly from ResultSet by using getBytes() or getString() method (example 1).
  • Get the java.sql.Clob object from ResultSet by calling getBlob() or getClob() method and then get data by using getBytes() or getSubString() method for this object (example 2).

Example 1

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

 

// Getting data directly from 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.);

  }

Example 2

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

 

//Getting Blob data from ResultSet and getting data from the Blob object

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());

  }