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



40 comments:

  1. hi...im not getting how to store and retrieve image from sql database and how to update that image ...im using image datatype

    ReplyDelete
    Replies
    1. which SQL you for storing images ?

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

      Delete
  2. when im updating image it not taking new image path its taking older image path... let me know ..how to fetch and update image

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

      Delete
  3. Actually i wanted to store image directly in ms sql database because we aremaking online database so...i have stored image in database but when im updating image its taking old image only its not taking updated image so ...please help me...

    ReplyDelete
  4. please tell me how to store ,retrieve and update image in ms sql database using corejava

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

      Delete
  5. Actually i have inserted image and even i have retrieved image from ms sql database ......and im displaying image on jlabel using image path.....but ma problem is when first time i fetch image from database and i displayed image on jlabel....but when fech second image from database and its not displaying second image its displaying first image only....

    ReplyDelete
    Replies
    1. 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.

      Delete
    2. im fetching image by nid...

      Delete
    3. 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/

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

      Delete
  6. String sql = "Select * from tabimg where Nid='"+Integer.parseInt(img.getText())+"'";
    Statement stmt2 = con.createStatement();
    rs = stmt2.executeQuery(sql);
    while(rs.next())
    {
    lblnid.setText(String.valueOf(rs.getInt("Nid")));
    byte[] fileBytes;
    fileBytes = rs.getBytes(2);
    tmpFile = new File("tmpImage");
    OutputStream targetFile=
    new FileOutputStream(tmpFile);
    targetFile.write(fileBytes);
    targetFile.close();
    ImageIcon icon = new ImageIcon(ImageIO.read(f2));
    String ss=f2.getAbsolutePath();
    img.setText(ss);
    jLabel1.setIcon(new ImageIcon(ss));f.close();
    in.close();
    f2.delete();
    f2.deleteOnExit();

    targetFile.flush();
    targetFile.close();
    //
    tmpFile.delete();

    ReplyDelete
  7. this is my code please help me how to solve this problem

    ReplyDelete
  8. try
    {
    String sql = "Select * from tabimg where Nid='"+Integer.parseInt(img.getText())+"'";
    Statement stmt2 = con.createStatement();
    rs = stmt2.executeQuery(sql);
    while(rs.next())
    {
    lblnid.setText(String.valueOf(rs.getInt("Nid")));
    byte[] fileBytes;
    fileBytes = rs.getBytes(2);
    int i = 0;

    tmpFile = new File("tmpImage");
    OutputStream targetFile=
    new FileOutputStream(tmpFile);
    targetFile.write(fileBytes);
    targetFile.close();
    ImageIcon icon = new ImageIcon(ImageIO.read(tmpFile));
    String ss=tmpFile.getAbsolutePath();
    img.setText(ss);
    jLabel1.setIcon(new ImageIcon(ss));


    targetFile.flush();
    targetFile.close();
    //
    tmpFile.delete();


    }}
    catch(Exception e)
    {
    JOptionPane.showMessageDialog(null,e);
    }
    Sorry this is my code....

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

      Delete
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. harmeet sir ! according to your code, where should be placed my image before inserting??

    ReplyDelete
  15. 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
  16. Image is not getting store in the My sql DB, anyone please help me ?

    ReplyDelete
  17. 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
  18. i have follow your code it compile successfully by image do not show

    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