Upload/Download Image in MySQL using JDBC

Upload/Download Image in MySQL using JDBC 

  

 PreRequisites :

Database Setting

1. MySql Database with username 'root' and password 'root'

2. create a database named 'test1'.

3. create a table 'stu' with 3 columns naming roll, name and photo. 

 

File Required:

Place a image named image.jpg into C:\practice folder.

Upload

1. Create a column in table whose datatype is BLOB(64 KB) or MEDIUMBLOB(16MB).

Exp:

CREATE TABLE  `stu` (

  `roll` int(11) default NULL,

  `name` varchar(10) default NULL,

  `photo` mediumblob NOT NULL

);

2. To insert image into DB through JDBC you should first get the FileInputStream for the image file. Follow the below Steps….
   
   a. Get the File Object for the input file.
               File f=new File(“d:/pactice/image.jpg”)

b. Get the FileInputStream Using above File object

            FileInputStream fis=new FIleInputStream(f);

      3. Prepare a statement for SQL Query. 

pst=con.prepareStatement("insert into stu values (?,?,?)");

            pst.setInt(1, 20);

            pst.setString(2, "Sushil");

            pst.setBinaryStream(3, fis,(int)f.length());

4. Execute the Query and your image is uploaded.    

                        int s=pst.executeUpdate();

Download image from MYSQL using JDBC

1. Get the contents of DB Row in a ResultSet.

pst=con.prepareStatement("select * from stu where name='Sushil'");

            rs=pst.executeQuery();

   2. To access the first row do

rs.next();

   

3. Suppose image is in 3rd column than to access 3rd column do below           
   
   Blob b=rs.getBlob(3);  ( b contains Bytes only)

4. convert b into array of Bytes
   

                 byte barr[]=new byte[(int)b.length

           barr=b.getBytes(1,(int)b.length());

5. Finally take FileOutputStream Object and write the file into it.

FileOutputStream fout=new FileOutputStream("c:/dbPhoto.jpg");

            fout.write(barr);

            fout.close();

           

6. Check your C: Drive you will find a image named dbPhoto.jpg which is same image you previously uploaded for Sushil

 -----------------------------------------------------------------------------------------------------------------------

Full Code for Image Upload/Download.
*********************************

package in.ibm;

import java.io.*;
import java.sql.*;

public class ImageInsertion {

    public static void main(String[] args) {

    FileInputStream fis;
    Connection con=null;
    PreparedStatement pst=null;
    ResultSet rs=null;

    try{
        

           Class.forName("com.mysql.jdbc.Driver");
           con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test1","root","root");
       
        File image= new File("d:/practice/image.jpg");
        pst=con.prepareStatement("insert into stu values (?,?,?)");
        pst.setInt(1, 20);
        pst.setString(2, "Sushil");
       
        fis=new FileInputStream(image);
       
        pst.setBinaryStream(3, fis,(int)image.length());
        int s=pst.executeUpdate();
   
    if(s>0){
        System.out.println("Uploaded");

       // DownLoading the same Image

        pst=con.prepareStatement("select * from stu where name='Sushil'");
        rs=pst.executeQuery();
        rs.next();
        Blob b=rs.getBlob(3);
        byte barr[]=new byte[(int)b.length()];//an array is created but contains no data
        barr=b.getBytes(1,(int)b.length());
                   
        FileOutputStream fout=new FileOutputStream("c:/dbPhoto.jpg");
        fout.write(barr);
                   
        fout.close();
        System.out.println("ok");

       
    }else{
        System.out.println("Failed");
    }
       
           
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        try{
        con.close();
        pst.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
   
    }

}