Storing and Retrieving Images in JDBC
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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()));