Handling Oracle Large Objects with JDBC,LOBs (Large OBjects),Character Large Object (CLOB) and Binary Large Object(BLOB)

LOBs (Large OBjects) are are designed to support large unstructured data such as text, images, video etc. Oracle supports the following two types of LOBs:

• Character Large Object (CLOB) and Binary Large Object(BLOB) are stored in the database either in-line in the table or in a separate segment or tablespace.
• BFILEs are large binary data objects stored in operating system files outside of database tablespaces.

Oracle extension classes are provided to support these types objects in JDBC like oracle.sql.CLOB, oracle.sql.BLOB. While you can use java.sql.Blob and java.sql.Clob, oracle extensions provide added functionalities, such as adding bytes specific positions (getBytes(int pos, byte[] data) etc.


Working with LOB Data

CLOB and the BLOB objects are not created and managed in the same way as the ordinary types such as VARCHAR. To work with LOB data, you must first obtain a LOB locator. Then you can read or write LOB data and perform data manipulation. Use the ResultSet's getBlob method to obtain the LOB locator, and then you can obtain the a Stream of the blob to read/write to the Blob

Blob blob = rs.getBlob(1);
InputStream is = blob.getBinaryStream();
OutputStream os = blob.setBinaryStream(1);

The following example shows how to insert, read and write Blobs to Oracle from Java. The table here has only two columns (IMAGE_ID and IMAGE) IMAGE is a BLOB.

package data;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class BlobTest {

 public void insertBlob(String imageId, String fileName) {
   Connection conn = null;
   try {
     conn = getConnection();
     if (!fileName.equals("")) {
       PreparedStatement ps = conn.prepareStatement("INSERT INTO IMAGES VALUES(?, ?)");
       ps.setString(1, imageId);
       FileInputStream fis = new FileInputStream(fileName);
       ps.setBinaryStream(2, fis, fis.available());
       ps.execute();
       ps.close();
     } else {
       PreparedStatement ps = conn.prepareStatement("INSERT INTO IMAGES VALUES (?, empty_blob())");
       ps.setString(1, imageId);
       ps.execute();
       ps.close();

     }
     conn.close();
   } catch (Exception e) {
     e.printStackTrace();
   }
 }

 public void readBlob(String fileName) {
   Connection conn = null;
   try {
     conn = getConnection();
     Statement st = conn.createStatement();
     ResultSet rs = st.executeQuery("SELECT IMAGE FROM IMAGES");
     while (rs.next()) {
        // The following two lines can be replaced by
        // InputStream is = rs.getBinaryStream(1);
       Blob blob = rs.getBlob(1);
       InputStream is = blob.getBinaryStream();
       FileOutputStream fos = null;

       fos = new FileOutputStream("c:/TEMP/" + fileName);
       byte[] data = new byte[1024];
       int i = 0;
       while ((i = is.read(data)) != -1) {
         fos.write(data, 0, i);
       }
     }
     conn.close();

   } catch (Exception e) {
     e.printStackTrace();
   }
 }

 public void writeBlob(String fileName) {
   Connection conn = null;
   try {
     conn = getConnection();
     Statement st = conn.createStatement();
     ResultSet rs = st.executeQuery("SELECT IMAGE FROM IMAGES FOR UPDATE");
     while (rs.next()) {
       Blob blob = rs.getBlob(1);
       System.out.println(blob);
        OutputStream os = blob.setBinaryStream(1);
       FileInputStream fis = null;
       fis = new FileInputStream("c:/TEMP/" + fileName);
       byte[] data = new byte[1];
       int i;
       while ((i = fis.read(data)) != -1) {
         os.write(data, 0, i);
       }
       os.close();
       break;
     }
     conn.close();

   } catch (Exception e) {
     e.printStackTrace();
   }
 }

 private Connection getConnection() throws ClassNotFoundException, SQLException {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");
   return conn;
 }

 public static void main(String[] args) {
   BlobTest blobTest = new BlobTest();
   blobTest.insertBlob("img1", "");
   blobTest.writeBlob("2.gif");

 }
}