Insert Blob(Image) in Mysql table using JSP

In this Section, we will insert blob data(image) in Mysql database table using JSP code.

A Blob stores a binary large object in the database table's row. Blob object contains a logical pointer which points to the Blob data, data is not directly stored in the row of the database table. Blob object  is valid for the duration of the transaction. The "getBlob()" and  "setBlob()" method of "ResultSet" ,"CallableStatement", and "PreparedStatement" interface , is used for accessing Blob value.

The Method defined in " java.sql.Blob " interface is as follows--

Return Type    Method                                         Description 

InputStream        getBinaryStream()                       Retrieves the blob value designated
                                                                                 by this blob value as a stream 

byte[]                 
getBytes(long pos, int length)         Retrieve all or part of the blob value that 
                                                                                 this blob represents as an array of bytes.

long                   
 length()                                          Returns the no of bytes in the Blob 
                                                                                 value designated by this Blob object 

OutputStream     
setBinaryStream(long pos)           Retrieves a stream that can be used 
                                                                                 to write to the Blob value  

int                      
 setBytes(long pos, byte[] bytes)    Write the given array of bytes to the Blob 
                                                                                 value that this Blob object represent
                                                                                 starting at  position pos, and returns the no 
                                                                                 of bytes written  

void                   
 truncate(long len)                            Truncates the blob value that this blob 
                                                                                  object represents to be len bytes in len

 Blob data type can further be classified into four types--

Inserting Image

<%@ page import="java.sql.*"%>

<%@ page import="java.io.*"%>

<%

Connection con=null;

ResultSet rs=null;

PreparedStatement psmt=null;

FileInputStream fis;

String url="jdbc:mysql://localhost:3306/test";

try{

Class.forName("com.mysql.jdbc.Driver").newInstance();

con=DriverManager.getConnection(url,"root","root");

File image=new File("D:/house.jpg");

psmt=con.prepareStatement("insert into inimage(name,city,image)"+"values(?,?,?)");

psmt.setString(1,"Michael");

psmt.setString(2,"Chennai");

fis=new FileInputStream(image);

psmt.setBinaryStream(3, (InputStream)fis, (int)(image.length()));

int s = psmt.executeUpdate();

if(s>0) {

%>



<b><font color="Blue">

<% out.println("Image Uploaded successfully !"); %>

</font></b>

<%

}

else {

out.println("unsucessfull to upload image.");

}

con.close();

psmt.close();

}catch(Exception ex){

out.println("Error in connection : "+ex);

}

%>

Retrieve Image:

<%@ page import="java.sql.*" %>

<%@ page import="java.io.*" %>

<%

String url = "jdbc:mysql://localhost:3306/test";

ResultSet rs = null;

PreparedStatement psmnt = null;

Connection con=null;

InputStream sImage;

try {

Class.forName("com.mysql.jdbc.Driver").newInstance();

con=DriverManager.getConnection(url,"root","root");

psmnt = con.prepareStatement("SELECT image FROM inimage WHERE name = ?");

psmnt.setString(1, "Michael");

rs = psmnt.executeQuery();

if(rs.next()) {

byte[] bytearray = new byte[1048576];

int size=0;

sImage = rs.getBinaryStream(1);

response.reset();

response.setContentType("image/jpeg");

while((size=sImage.read(bytearray))!= -1 ){

response.getOutputStream().write(bytearray,0,size);

}

}

}

catch(Exception ex){

out.println("error :"+ex);

}

finally {

rs.close();

psmnt.close();

con.close();

}%>



Query

CREATE TABLE inimage (

ID INTEGER AUTO_INCREMENT,

IMAGE BLOB,

PRIMARY KEY (ID)

) ENGINE=InnoDB;