Recent Pos

The SQL 99 Types – BLOB (Binary Large Object)

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmail

⇒ The SQL 99 specification introduces new datatypes which are very useful in creating complex database model:

⇒ The JDBC 2.0 introduces support for working with these types.

 

 

Binary Large Object(BLOB):

binary large object (BLOB)
⇒ This type allows us to store large binary content (like upto 4GB) into a single column. This useful to store media files like .jpg, .gif, .wmv etc.

⇒ It increases the memory requirements in the database server and may sligtly increase the load compared to managing these binary files into the disc separately and store the file name into database.

⇒ But to store sensitive data like photograph and specimen signature of the account holder of a bank account is preferred storing into the database.

⇒ In such cases we find the use of BLOB type column in a table.

⇒ Anyways now databases are implementing pointer mechanism to access the BLOB type data which can make an effective memory management in database.

We can create a BLOB type column in the DB as shown below:


 

 

How to store image into database? OR How to store BLOB content into database?

⇒ We need to have a BLOB type column defined into the DB table.

⇒ The JDBC PreparedStatement support two options for storing the BLOB content into the DB:

  • setBinaryStream(int param_index, InputStream data, int len)
  • setBlob(int param_index, Blob b)

⇒ Example application using setBinaryStream() to store image:

 


 

The java.sql.Blob:

⇒ This is an interface of JDBC API describing the Blob object implemented by the 3rd party vendor.

⇒ The Blob object is used to represent the DB BLOB content.

⇒ The java.sql.Blob interface declares the following methods:

-This content to Blob at the given index.

 

-This is same as above except that instead of writing all the byte[], it just writes the given data_len number of bytes starting from the data_pos.

 

-This will return an OutputStream that is used to write the content to the DB BLOB.

 

-This is used to read the BLOB content from the database. Returns byte[] with the content from the specified index and len number of bytes.

 

-Returns an InputStream to read the BLOB content from the database.

 

-Returns the number of bytes in this BLOB.

 

-This is new in JDBC 4.0. This method is used to release the resources of this BLOB. That is, it is like close.

 

 

Example:

⇒ Lets consider we have got the binary data into an byte[] with the variable name data which we want to store into the database.

 

-Here ps is considered as PreparedStatement object ref and parameter 4 is of BLOB type.

 

How to create BLOB object?

⇒ The JDBC 2.0 and 3.0 doesn’t includes standard abstraction for creating BLOB object. We need to use vendor specific way to create BLOB object. In JDBC 4.0 we have a new method in Connection interface declared to create a BLOB object.

-That means in JDBC 4.0 we can write the code as shown below:

 


 

How to read the image from database?

⇒ We know that the images in the database are stored into a BLOB column.

⇒ The following method of ResultSet can be used to read the Blob column value:

  • 1. InputStream getBinaryStream(int col_index): This returns an InputStream using which we can read the Blob content from the DB.
  • 2. Blob getBlob(int col_index): This returns a BLOB object using which we can get the Blob content.

 

 
Example: Now we want to read the photo of the given emp from the empBlob table and store it into the current directory with the file name photo.jpg


 
Note: The examples and other utility files are available in the downloadable .zip, download link available below.

 
Download this example: LOBExample.zip

 
<-Previous: Reading Auto Generated Key Using JDBC | | Next->The SQL 99 Types – CLOB (Character Large Object)

No Comments

Post a Comment