Thursday, March 21, 2013

Insert and Retrieve Images from MySql Table Using Java

Demonstrate to insert and retrieve the images from database table. Mostly the images are stored from outside the database( some folders) and store the path of the images into database tables . but in some scenario we need to insert the images into database tables in binary format.

Requirements : 

  1. Download and Install JDK on your System.
  2. Download and Install MySql server and install.
  3. Download MySQL Connector or Drivers for use MySQL with Java.

Create Table in MySQL : 

CREATE TABLE `image` (
  `id` varchar(45) DEFAULT NULL,
  `size` int(11) DEFAULT NULL,
  `image` longblob
);

In MySQL when we use the blob type to store the data , it support only 5 kb image capacity. its depends on different database vendors . according to some more database vendors the blob type support large capacity. So the type of image type is depends on database vendors.

Insert The Image In Database : 

import java.sql.*;
import java.io.*;
public class InsertImagesMysql{
public static void main(String[] args){
System.out.println("Insert Image Example!");
String driverName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/";
String dbName = "test";
String userName = "root";
String password = "root";
Connection con = null;
try{
  Class.forName(driverName);
  con = DriverManager.getConnection(url+dbName,userName,password);
  Statement st = con.createStatement();
  File imgfile = new File("pic.jpg");
 
 FileInputStream fin = new FileInputStream(imgfile);
 
  PreparedStatement pre =
  con.prepareStatement("insert into Image values(?,?,?)");
 
  pre.setString(1,"test");
  pre.setInt(2,3);
  pre.setBinaryStream(3,(InputStream)fin,(int)imgfile.length());
  pre.executeUpdate();
  System.out.println("Successfully inserted the file into the database!");

  pre.close();
  con.close(); 
}catch (Exception e1){
System.out.println(e1.getMessage());
}
}
}

Compile & Run The Task : 

  1.  javac -cp mysql-connector-java-5.0.5-bin.jar;. InsertImagesMysql.java
  2.   java -cp mysql-connector-java-5.0.5-bin.jar;. InsertImagesMysql

Retrieve The Image From Database : 

import java.sql.*;
import java.io.*;
public class RetriveImagesMysql{
public static void main(String[] args){
System.out.println("Retrive Image Example!");
String driverName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/";
String dbName = "test";
String userName = "root";
String password = "root";
Connection con = null;
try{
Class.forName(driverName);
con = DriverManager.getConnection(url+dbName,userName,password);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select image from image");
int i = 0;
while (rs.next()) {
InputStream in = rs.getBinaryStream(1);
OutputStream f = new FileOutputStream(new File("test"+i+".jpg"));
i++;
int c = 0;
while ((c = in.read()) > -1) {
f.write(c);
}
f.close();
in.close();
}
}catch(Exception ex){
System.out.println(ex.getMessage());
}
}
}

Compile & Run The Task : 


  1.  javac -cp mysql-connector-java-5.0.5-bin.jar;. RetriveImagesMysql.java
  2.   java -cp mysql-connector-java-5.0.5-bin.jar;. RetriveImagesMysql

Click Here To Download The Source Code



33 comments:

  1. which SQL you for storing images ?

    ReplyDelete
  2. hey, you want to store images in database or store images path in database ?

    ReplyDelete
  3. Hey i think, this will help you http://stackoverflow.com/questions/5613898/storing-images-in-sql-server

    ReplyDelete
  4. i think this is the problem of repaint in java swings. Please verify that second image is fetched from database by using image size or name.

    ReplyDelete
  5. Actually my meaning is that, please verify that, the second image is retrieved from database. how we verify image is retrieve or not, from image size or name in while block. so, please check your second image is get from database or not/

    ReplyDelete
  6. Hello Harmeet,How can I upload files greateer than 1 mb to myswl database..? I am using a mediumblob..but still cannot upload files greater than 1 mb..also with longblob it is not working ? please guide me
    Bharani

    ReplyDelete
  7. Harmeet Singh please I want to save image into MYSQL database and load into the jtable then click on jtable for the image to fill the jlabel on the jframe form.

    my name is Jones

    ReplyDelete
  8. harmeet singh can you help in retrieving data from sql server 2008 r2, using front end ?

    ReplyDelete
  9. your source is good. but this part :
    InputStream in = rs.getBinaryStream(1);
    while ((c = in.read()) > -1) {
    f.write(c);
    }
    is very costly especially if the image is big. i tried above approach and it took about 27 seconds to write one file of 7.2MB.

    I found an alternative approach as below :

    Blob b=rs.getBlob(2);
    byte barr[]=b.getBytes(1,(int)b.length());
    FileOutputStream fout=new FileOutputStream("./sample/sample.jpg");
    fout.write(barr);

    This is significantly faster. It took about 0.1 seconds to write the same image of 7.2MB.

    ReplyDelete
  10. Also , there are 5 variable types with respect to blob. blob, medium blob ,large blob,block blob and page blob , which allow storage of 65kb , 16mb and 4.2gb,200gb , 1TB respectively

    ReplyDelete
  11. Hi, anybody can help me on how to save/ retrieve image using Java Servlets from MySQL DB in JSP / HTML5 as view ?

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  12. harmeet sir ! according to your code, where should be placed my image before inserting??

    ReplyDelete
  13. After Insertion done successfully put filepath as null otherwise it take same file again and again.

    ReplyDelete
  14. sir can i resize any image by using mysql or java OR connecting both each other.
    If we can please let me know sir i want to make a project sit.
    thankyu

    ReplyDelete
    Replies
    1. package image.resizing;

      import java.awt.Graphics2D;
      import java.awt.Image;
      import java.awt.RenderingHints;
      import java.awt.Robot;
      import java.awt.event.ActionEvent;
      import java.awt.event.ActionListener;
      import java.awt.image.BufferedImage;
      import java.io.File;
      import java.io.FileInputStream;
      import java.io.FileNotFoundException;
      import java.io.IOException;
      import java.util.logging.Level;
      import java.util.logging.Logger;
      import javax.imageio.ImageIO;
      import javax.swing.ImageIcon;
      import javax.swing.JButton;
      import javax.swing.JFileChooser;
      import javax.swing.JFrame;
      import javax.swing.JLabel;
      import javax.swing.JOptionPane;
      import javax.swing.filechooser.FileNameExtensionFilter;

      /**
      *
      * @author Pratyush Aaditya
      */
      public class ImageResizing extends JFrame{

      /**
      * @param args the command line arguments
      */
      ImageResizing(){
      GUI();
      }

      public static void main(String[] args) {
      ImageResizing ob=new ImageResizing();
      }

      private Image resizeImage(int width, int height, Image img){

      BufferedImage resizedImage=new BufferedImage(width,height,BufferedImage.TYPE_INT_RGB);
      Graphics2D g2=resizedImage.createGraphics();
      g2.setRenderingHint(RenderingHints.KEY_INTERPOLATION, RenderingHints.VALUE_INTERPOLATION_BILINEAR);
      boolean drawImage = g2.drawImage(img, 0, 0, width, height, null);
      return resizedImage;
      }

      private File filechooser(){
      File selectedFile=null;
      String path=System.getProperty("user.home");
      JFileChooser chooser=new JFileChooser(path);
      //Only file will be selected. Not Folders.
      chooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
      //Only images will be selected not all.
      String [] typeOfFile={"jpg", "png", "jpeg"};
      String descriptionOfFile="Images only!!!";
      chooser.setFileFilter(new FileNameExtensionFilter(descriptionOfFile, typeOfFile));
      chooser.setAcceptAllFileFilterUsed(false); //All files not allowed.
      int result=chooser.showOpenDialog(this);
      if(result==JFileChooser.APPROVE_OPTION){
      selectedFile=chooser.getSelectedFile();
      }
      return selectedFile;
      }

      private void GUI(){
      setVisible(true);
      setSize(600,500);
      setLayout(null);
      setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
      setLocationRelativeTo(null);

      JLabel imageLabel=new JLabel();
      imageLabel.setBounds(0,0,getWidth(),getHeight()-70);
      add(imageLabel);

      JButton resizeButton=new JButton("Resize pic");
      resizeButton.setBounds(400,434,180,20);
      add(resizeButton);

      File file=filechooser();
      if (file!=null) {
      System.out.println("Selected File is : "+file.getName());
      ImageIcon img=new ImageIcon(file.getAbsolutePath());
      imageLabel.setIcon(img);
      }else{
      System.out.println("Not selected!");
      }

      resizeButton.addActionListener(new ActionListener(){
      @Override
      public void actionPerformed(ActionEvent e) {
      if(file!=null){
      try {
      Image im=ImageIO.read(file);
      im=resizeImage(getWidth(),getHeight()-70, im);
      //ImageIO.write(im, "png", new File("D:\\scrn.png"));
      imageLabel.setIcon(new ImageIcon(im));
      } catch (IOException ex) {
      System.out.println(ex);
      }
      }else{JOptionPane.showMessageDialog(null, "No File Selected");}
      }

      });
      }
      }

      Delete
  15. Image is not getting store in the My sql DB, anyone please help me ?

    ReplyDelete
  16. I followed the above code, there is no error msg is appearing, but it;s not getting store in to the database. please help ASAP..

    ReplyDelete
  17. i have follow your code it compile successfully by image do not show

    ReplyDelete
  18. just add repaint(); at the last of your code.

    ReplyDelete
  19. Hey, I worked brilliantly but I want to Use this File in JSP.
    But file should not to be saved in host(in computer).any suggestions?
    If you can mail me

    ReplyDelete
    Replies
    1. Hello @Adam i am not getting you point, which code are you want to use in JSP?

      Delete
    2. This comment has been removed by the author.

      Delete
  20. Dear, I saved pic as ur code. But I receive a msg as "pic.jpg (no such file or directory)" msg on my compiler, where should I save my pic.jpg file?

    ReplyDelete
  21. This comment has been removed by the author.

    ReplyDelete
  22. Uploading image inside mysql database is perfect but after downloading image size not proper....
    how to resole it?

    ReplyDelete
  23. This comment has been removed by the author.

    ReplyDelete
  24. HI sir...my self kapil kashyap. I am working on java web application. This is my first project and now i got confused in a case that where i have to store profile image. What is the best way
    to store and retrieve image in java project. Pls give me suggestion .

    ReplyDelete
  25. Hi guys please
    Would u plse show me how to store image URL in db and retreve uaing jsp page whithin table

    ReplyDelete
  26. thank you so much sir, it really help and works as it is..

    ReplyDelete