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