Handling Oracle Large Objects with JDBC

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

 }
}

BlobTest.java
Insert Blob into Oracle

The insertBlob method takes the image id and the image file name as arguments. If the image file is an empty string, then an empty blob is inserted into the table. The empty_blob() function returns an empty locator of type BLOB, this is used in the INSERT.

Read from a Blob

The getBinaryStream of java.sql.Blob class returns an InputStream, which can be used to read from the blob.

Write to a Blob

The writeBlob method writes to the first row retrieved from the table. The SQL statement uses FOR UPDATE. In the absence of FOR UPDATE, you will get an IOException

java.io.IOException: ORA-22920: row containing the LOB value is not locked
 at oracle.jdbc.driver.DatabaseError.SQLToIOException(DatabaseError.java:517)
 at oracle.jdbc.driver.OracleBlobOutputStream.flushBuffer(OracleBlobOutputStream.java:214)
 at oracle.jdbc.driver.OracleBlobOutputStream.close(OracleBlobOutputStream.java:179)
 at data.BlobTest.writeBlob(BlobTest.java:90)
 at data.BlobTest.main(BlobTest.java:111)

The following article describes how to handle CLOB using JDBC