Retrieve image from mysql database through jsp

In this section we will see how to retrieve image from mysql database through jsp code. First create a database of structure given below and save images. Here we will use table 'save_image' of database 'mahendra'.

Structure of table 'save_image'

First create database named 'mahendra' by query given below....

CREATE TABLE save_image (             
              id int(5) NOT NULL auto_increment,  
              name varchar(25) default NULL,      
              city varchar(20) default NULL,      
              image blob,                         
              Phone varchar(15) default NULL,     
              PRIMARY KEY  (`id`)                   
            );

Create application directory named "user" in the tomcat-6.0.16/webapps. Before running this java code you need mysql connector jar file in the Tomcat-6.0.16/webapps/user/WEB-INF/lib.

mysql> create database mahendra;

Note : In the jsp code given below, image will be retrieved from database on the basis of 'id' field of the table. So code will retrieve image of specified 'id' value. In this example we will retrieve image with 'id' value 11. So before running this jsp code first check whether image for the specified 'id' value is present.

retrieve_image.jsp

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

<% // declare a connection by using Connection interface Connection connection = null;
/* Create string of connection url within specified format with machine
name, port number and database name. Here machine name id localhost
and database name is mahendra. */
String connectionURL = "jdbc:mysql://localhost:3306/mahendra";
/*declare a resultSet that works as a table resulted by execute a specified
sql query. */
ResultSet rs = null;
// Declare statement.
PreparedStatement psmnt = null;
// declare InputStream object to store binary stream of given image.
InputStream sImage;
try {
//�Load JDBC driver "com.mysql.jdbc.Driver"
Class.forName("com.mysql.jdbc.Driver").newInstance();
/* Create a connection by using getConnection() method that takes
parameters of string type connection url, user name and password to
connect to database. */
connection = DriverManager.getConnection(connectionURL, "root", "root");
/* prepareStatement() is used for create statement object that is
used for sending sql statements to the specified database. */
psmnt = connection.prepareStatement("SELECT image FROM save_image WHERE id = ?");
psmnt.setString(1, "11"); // here integer number '11' is image id from the table
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 {
// close all the connections.
rs.close();
psmnt.close();
connection.close();
}
%>

Save this code as a .jsp file named "retrieve_image.jsp" in the application directory in Tomcat-6.0.16 and run this jsp page with following url in address bar of the browser http://localhost:8080/user/retrieve_image.jsp

Download Source Code

Retrieve image from mysql database through jsp

Retrieve image from mysql database through jsp

Retrieve image from mysql database through jsp

Tutorials