Architecture & DesignWorking with the JDBC API for Large Objects

Working with the JDBC API for Large Objects

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

The JDBC API provides the necessary support to work with Large Objects (LOB), such as storing an image file or a large text document in the database. The requirements of data persistence may vary both by its size and type of data content; a LOB can be of these types: BLOB (Binary Large Object), CLOB (Character Large Object), and NCLOB (National Character Large Object). These are the LOB variations supported by the JDBC API. However, there is a difference on how a RDBMS treats and handles LOB objects internally. They are very vendor specific, but at the application level, specifically from JDBC APIs out treatment assumes it to be BLOB or CLOB, or perhaps NCLOB.

On Large Object (LOB) Storage

LOB data are a different breed of data type. They are not stored in the database in the way a VARCHAR or some other common data types are stored. Actually, a database simply stores a pointer to the actual data location in the table and refers to the data stored in some other location. The reference pointer is called a locator. The locator acts as a missing link between the application/user that fires an SQL query to fetch data from the database and the actual location of the data file. This is kind of a weird behaviour on the part of the RDBMS package. But then, RDBMS is not designed to handle large objects. The tabular structure is not fit to store data whose size may vary ridiculously from, say, 10K to 10M. But still, RDBMS have some way to deal with LOB, provided we play according to its rule. In fact, it is the sole discretion of the RDBMS package to decide whether to store the data in the table itself or store only the locator in lieu of actual data. This decision depends on the size of the LOB data. For example, it may decide to store data in the table only if the size is less than, say 10K; otherwise follow plan B—store only its locator.

Binary Large Object (BLOB)

A Blob interface is a variation of LOB that maps to the SQL BLOB value. It is used to store binary data. It is found in the java.sql package. A blob interface implements some useful methods, such as finding the length of SQL BLOB or transforming into byte arrays that Java can leverage, such as displaying image information into an image viewer control. Binary storage is particularly useful for persisting non-alphanumeric information such as images. We can load an image file and transform it into a stream of bytes and store that into the database. Although data insertion is performed pretty much the same way of old school SQL such as “insert into …”, there is a subtle difference. Let’s illustrate it with an example.

Suppose that a table we have created to store BLOB data is as follows:

create table image_data (
   image_id int not null,
   BLOB image,
   primary key(image_id)
);
Note: I have used Apache Derby DB because it provides BLOB and CLOB data types directly. Databases such as MySQL, PostgreSQL, and the like may deal with LOB data types differently. For example, CLOB objects are dealt differently in MySQL and do not have a CLOB data type. The best idea is to refer to the database documentation for relevant information and support.

The Java code to store an image would be as follows.

Connection con =
   DriverManager.getConnection(DATABASE_URL,
                               USER_ID, PASSWORD);

con.setAutoCommit(true);
String sql =
   "insert into image_data (image_id, image)
   values (?,?)";

PreparedStatement pstmt = null;
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, 101);

File f1 = new File("pic1.jpg");
Blob blob = con.createBlob();
FileInputStream fis = new FileInputStream(f1);
OutputStream out = blob.setBinaryStream(1);
int i = -1;
while ((i = fis.read()) != -1) {
               out.write(i);
}

pstmt.setBlob(3, blob);
pstmt.executeUpdate();

Character Large Object (CLOB)

Clob is an interface that enables storing character data by mapping SQL CLOB values. It has another variation to store Unicode character data, called NClob. SQL NCLOB is one of the ways to support internationalization; otherwise, it is similar to SQL CLOB. Both Clob and Nclob interfaces are defined in the java.sql package. With the help of length operation defined in the interface, we can find out the size of a SQL BLOB value and even search for a substring within the Clob value. Because SQL CLOB persists large string values, some RDBMS treats them as large textual objects and maps CLOB as a text file linked into the database.

To store Clob, the table may be re-created as follows:

create table image_data (
   image_id int not null,
   BLOB image,
   CLOB description,
   primary key(image_id)
);

And, the Java code to store Clob data is as follows.

Connection con =
   DriverManager.getConnection(DATABASE_URL,
                               USER_ID, PASSWORD);
con.setAutoCommit(true);
String sql = "insert into image_data
   (image_id, image, description) values (?,?,?)";

PreparedStatement pstmt = null;
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, 

//...

File f2 = new File("pic1_desc.txt");
Clob clob = con.createClob();
FileReader reader = new FileReader(f2);
Writer writer = clob.setCharacterStream(1);
int j = -1;
while ((j = reader.read()) != -1) {
   writer.write(j);
}

pstmt.setClob(4, clob);

A Complete Example

Let’s create a very rudimentary (overlooking some of the best practices of coding) Java code to insert Blob and Clob data and fetching the same. Because for Blob we’ll store an image file, after fetching, viewing the same requires a GUI control. So, we have used Swing to implement our GUI needs.

package org.mano.app;

import java.io.*;
import java.sql.*;
import javax.swing.*;
import org.apache.derby.jdbc.EmbeddedDriver;

public class SimpleMain {

   private static final String DATABASE_URL =
      "jdbc:derby://localhost:1527/testdb";
   private static final String USER_ID = "user1";
   private static final String PASSWORD = "secret";

   public static void insertData(File picFile, File descFile)
         throws Exception {

      Driver driver = new EmbeddedDriver();
      DriverManager.registerDriver(driver);
      Connection con = DriverManager.getConnection(DATABASE_URL,
         USER_ID, PASSWORD);
      con.setAutoCommit(true);

      String sql = "insert into image_data(image_id, ,
         image, description)"
         + " values (?,?,?)";

      PreparedStatement pstmt = null;
      pstmt = con.prepareStatement(sql);
      pstmt.setInt(1, 102);

      Blob blob = con.createBlob();
      FileInputStream fis = new FileInputStream(picFile);
      OutputStream out = blob.setBinaryStream(1);
      int i = -1;
      while ((i = fis.read()) != -1) {
         out.write(i);
      }
      pstmt.setBlob(3, blob);

      Clob clob = con.createClob();
      FileReader reader = new FileReader(descFile);
      Writer writer = clob.setCharacterStream(1);
      int j = -1;
      while ((j = reader.read()) != -1) {
         writer.write(j);
      }

      pstmt.setClob(3, clob);
      pstmt.executeUpdate();
      pstmt.close();
      con.close();
   }

   public static void fetchData() throws Exception {

      Driver driver = new EmbeddedDriver();
      DriverManager.registerDriver(driver);
      Connection con =
         DriverManager.getConnection(DATABASE_URL,
         USER_ID, PASSWORD);
      con.setAutoCommit(true);

      String sql = "select image_id, image, description from
         image_data where image_id=?";

      PreparedStatement pstmt = con.prepareStatement(sql);
      pstmt.setInt(1, 102);
      ResultSet rs = pstmt.executeQuery();

      int id = 0;
      Blob img=null;
      Clob txt = null;

      if (rs.next()) {
         id = rs.getInt("image_id");
         img = rs.getBlob("image");
         txt = rs.getClob("description");
      }


      JScrollPane scroll=new JScrollPane();
      JTextArea txtArea=new JTextArea(txt.getSubString(1,
         (int)txt.length()),10,15);

      scroll.getViewport().add(txtArea);
      JLabel pic=new JLabel(new ImageIcon(img.getBytes(1,
         (int)img.length())));

      Box box=Box.createHorizontalBox();
      box.add(pic);
      box.add(scroll);


      JFrame f = new JFrame();
      f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
      f.setSize(600, 400);
      f.setTitle("Photo ID = " + id);

      f.add(box);
      f.pack();
      f.setVisible(true);

      rs.close();
      pstmt.close();
      con.close();

   }

   public static void main(String[] args) throws Exception {

      File f1 = new File("/home/mano/images/toon1.jpg");
      File f2 = new File("/home/mano/images/lorem_ispum.txt");

      // insertData(f1, f2);
      fetchData();

   }

}

Output

LOB
Figure 1: The result of our coding

Conclusion

Once we get hold of Blob and Clob objects, we either can use the setBytes() method to write data to a LOB object or we can use OutputStream. The preceding Java code shows the simplest way to write and read LOB objects to and from an application. It reads text data and a image from a file, and writes all bytes to a Blob or Clob object. The while-loop reads one byte at a time from the file to keep the code simple and readable. However, in real-world programs, LOB deals with much larger chunks of data at a time.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories