Tuesday, November 6, 2012

MySQL - JDBC example for BLOB storage

A blob (binary large object) is a collection of binary data stored as a single entity in a database. Blobs can be used to store images, audio or other multimedia files. There are four variation of blob datatype -
  • TINYBLOB - Maximum length of 255 (2^8 - 1) characters i.e 25 bytes
  • BLOB - Maximum length of 65535 (2^16 - 1) characters i.e 64 KB
  • MEDIUMBLOB - Maximum length of 16777215 (2^24 - 1) characters i.e 16 MB
  • LONGBLOB - Maximum length of 4294967295 (2^32 - 1) characters i.e 4GB

About the example: We will create a table in mySql and see how to insert/reterive an image using JDBC.

First lets create a table in MySQL. Please see the syntax below, we have used MEDIUMBLOB as it would be sufficient for medium size object stotage. You can use any other as per your requirement. We have just 3 columns pic_id as a auto increment primary key, pic_name will hold the picture name and pic_file a blob which will store actual picture.

CREATE TABLE blobtest (
PRIMARY KEY (pic_id),
pic_name VARCHAR(100) NOT NULL,

Now we will see a simple JDBC example to insert data into the blob column. Please see the self explanatory code below.

So far you have seen how to insert a file into the blob column. Now lets see how to retrive the files from blob storage.