Home > Articles > Programming > Java

Storing and Retrieving Images in JDBC

  • Print
  • + Share This
One of the most frequently asked questions is how to store and retrieve images from a database. Because images cannot be inserted into the database by using the SQL INSERT command, the only way to insert images into database is through embedded SQL programming. Y. Daniel Liang demonstrates in this article demonstrates how to use Java to insert images into a database.
This article is derived from the book Rapid Java Application Development Using JBuilder 4/5/6 (Prentice Hall PTR, 2001), by Y. Daniel Liang.
From the author of

One of the most frequently asked questions is how to store and retrieve images from a database. Images cannot be inserted into the database by using the SQL INSERT command. The only way to insert images into database is through embedded SQL programming. This article demonstrates how to use Java to insert images into a database.

Key Concepts

To insert images into a database, the database must support images. Images are stored in binary in a table cell. The data type for the cell is a binary large object (BLOB), which is a new SQL type in SQL3 for storing binary data. Another new SQL3 type is character large object (CLOB), for storing a large text in the character format. JDBC 2 introduced the interfaces java.sql.Blob and java.sql.Clob to support mapping for these new SQL types. JBDC 2 also added new methods, such as getBlob, setBinaryStream, getClob, setBlob, and setClob, in the interfaces ResultSet, PreparedStatement, and CallableStatement, to access SQL BLOB and CLOB values.

To store an image in a cell in a table, the corresponding column for the cell must be of the BLOB type. For example, the following SQL statement creates a table whose type for the flag column is BLOB:

create table Country(name varchar(30), flag blob,
 description varchar(500));

In the preceding statement, the description column is limited to 500 characters. The upper limit for the VARCHAR type is 32,672 bytes. For a large character field, you can use the CLOB type, which can store up to 2GB characters.

To insert a record with images to a table, define a prepared statement like this one:

PreparedStatement pstmt = connection.prepareStatement(
 "insert into Country values(?, ?, ?)"); 

Images are usually stored in files. You may first get an instance of InputStream for an image file and then use the setBinaryStream method to associate the input stream with cell in the table, as follows:

// Store image to the table cell
File file = new File(imageFilenames[i]);
InputStream inputImage = new FileInputStream(file);
pstmt.setBinaryStream(2, inputImage, (int)(file.length()));

To retrieve an image from a table, use the getBlob method, as shown here:

// Store image to the table cell
Blob blob = rs.getBlob(1);
ImageIcon imageIcon = new ImageIcon(
 blob.getBytes(1, (int)blob.length()));
  • + Share This
  • 🔖 Save To Your Account