Storing Images with BLOB in db2 , How to insert Large Objects (audio , photo, etc) in db2 using java

           Do you need to store Large data objects such as Images (jpg, jpeg, gif , bmp , etc ..) and audios (MP3, etc..) in db2 database. DB2 provides specialized data types called LOBs (Large Objects) for storing large data objects. For storing large text, CLOB data type is used. For storing binary data ( photo images, audio songs), you can use BLOB type.

          BLOB is Binary Large Object which can store binary data and the default size for BLOB is 1 mb. It can contain up to 2 gigabytes of binary data. BLOB values are not stored in a database table , instead a descriptor (locator or pointer) is stored. This descriptor points to the physical location of the LOB. The actual LOB values are stored in tablespaces. This topic covers how to put BLOB data into table in the database.

          JDBC 2.0 provides methods for writing BLOBs. Binary Data can be stored in BLOB field using setBytes, setBinaryStream, setBlob methods of the PreparedStatement interface. In the following example, Employee details are stored along with his / her photos. For that excise, we have to create a table with BLOB field for storing employees photos. In our example, Employee table is created which has the fields of EmpId, EmpName, EmpPhoto, filePath. In this, EmpPhoto is a BLOB field. filePath field is used to store original physical file path and its name. This field is not mandatory. This field may be used  to create the image file with the same filename in the same path when reading blob data back. If the file name is not available, then you have to mention the file name with path in the application itself while reading blob data back. Now how to store Employee's photo in the blob field which is  kept as a physical file in a path (f:\temp\images\) in the machine. So we have to read the file as binary data, that can be done  by the below statements

              file = new FileInputStream(filename);

              int size = file.available();

              imageData = new byte[size];

              file.read(imageData); 

             In our example ,we have written readImage function which is used to read the image file and returns as array of byte data to the setBytes method.

package com.javaonline;
import java.sql.*;
import java.io.*;

public class Db2Blob {
    public static void main(String[] args)    throws SQLException 
    {
        try {
          // Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
           Class.forName("com.ibm.db2.jcc.DB2Driver");
       } catch (Exception e) {
  System.out.println(e);
                  System.exit(0); 
                       }
       Connection con = DriverManager.getConnection("jdbc:db2:test");    
       Statement stmt=null;
 try {     
        stmt = con.createStatement();
       //to create table with blob field (One time only)
         stmt.executeUpdate("CREATE TABLE Employee (EmpId varchar (10) , EmpName varchar (50) , EmpPhoto BLOB (5M), filePath varchar(100))");
   
         } catch (Exception e) {
  System.out.println("Tables already created, skipping table creation process");
           }
 String empId="1222";
 String empName="John";
 String empPhotoFile="f:/temp/images/"+empId+".jpg"; // photo file name
 int success=0;
         PreparedStatement pstmt = con.prepareStatement("INSERT INTO Employee VALUES(?,?,?,?)");
         pstmt.setString(1, empId);
         pstmt.setString(2, empName);
         pstmt.setBytes(3,readImage(empPhotoFile));
         pstmt.setString(4,empPhotoFile);
         success =    pstmt.executeUpdate();
         if(success>=1)  System.out.println("New Employee detail added");
          con.close(); 
 }
    
    
 //to read image from the physical file. 
 private static byte[] readImage(String filename) {

      byte[]  imageData = null;
      FileInputStream file = null;
      try {
           file = new FileInputStream(filename);
           int size = file.available();
           imageData = new byte[size];
           file.read(imageData);
      } catch (FileNotFoundException e) {
           e.printStackTrace();
               } catch (IOException e) {
           e.printStackTrace();
      } finally {
           try {
                if (file != null) file.close();
               } catch (IOException e) {
                e.printStackTrace();
                 }
             }
          return imageData;
      }
 }