Showing posts with label insert image into database. Show all posts
Showing posts with label insert image into database. Show all posts

Wednesday, 23 January 2013

Inserting and retrieving image from MySql

Insertion and retrieval of image from MySql

Step 1: Create a table in MySql for storing images.

mysql >connect test;
mysql >create table MyPictures
          >(id int(11),
          >name varchar(25),
          >photo blob);

Step 2: Run the following Java program to insert id, name & image into table MyPictures .
             The source image is present in E:\\Mayank.jpg  and again after retrieval the image is 
             saved in G:\\DatabasePhotos folder

         table name = MyPictures    (table to be connected)
         username = root                  (default username of MySql database)
         password = root                   (password is set by user during installation)
         database = test                    (database name within which the table to be connected lies)

Java Program :

import java.io.*;
import java.sql.*;
public class InsertPictureToMySql2 {
  public static void main(String[] args) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");
    FileInputStream fis = null;
    PreparedStatement ps = null;
    try {
            conn.setAutoCommit(false);
            File file = new File("E:\\Mayank.jpg");
      fis = new FileInputStream(file);
      ps = conn.prepareStatement("insert into MyPictures(id, name, photo) values (?, ?, ?)");
      ps.setString(1, "7");
      ps.setString(2, "Mayank");
      ps.setBinaryStream(3, fis, (int) file.length());
      ps.executeUpdate();
      conn.commit();
    }catch(Exception ae)
{System.out.println("Problem in saving in database !! ");
}

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select photo from MyPictures");
int i=1;
try{
if(rs.next())
{
String len1 = rs.getString("photo");
int len = len1.length();
byte [] b = new byte[len];
InputStream in = rs.getBinaryStream("photo");
int index = in.read(b, 0, len);
OutputStream outImej = new FileOutputStream("G:\\DatabasePhotos\\Photo"+i+".JPG");
while (index != -1)
{
outImej.write(b, 0, index);
index = in.read(b, 0, len);
System.out.println("========================================");
System.out.println("Photo added to database successfully");
System.out.println("Photo saved in DatabasePhotos folder");
System.out.println("========================================");
}
outImej.close();
i++;
}
}catch(Exception e)
{System.out.println("Problem in retrieving image from database !!");
}
 finally {
      ps.close();
      fis.close();
    }
  }
}