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();
    }
  }
}