Insert image into MySQL database

[Aravind Prasad]

Dear Ranchers,
I'm doing a HR System in JSP and MySQL. In some point i want to insert the employee photograph in to the database and also want to retrieve it back and display it in the jsp page.

I tried looking into the old POSTs and also in google search. I failed to get a solution for this. I will be thankful, if you could send me a sample code for inserting image into the database and also to retrieve it. I will be very thankful..

Regards

Aravind Prasad

[Christian Nash]

try inserting it as a BLOB[binary large object]

[Aravind Prasad]

Dear Ranchers,
I got the code for inserting and retrieving image to and from the database. I would like to share the code and also have a small doubt..

Inserting image to database

 
import java.awt.Graphics;
import java.awt.Image;
import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.sql.Connection;
import javax.imageio.ImageIO;
import java.sql.*;
import javax.swing.ImageIcon;
 
public class test
{public test()
    {System.out.println("Executing");
try
        {   ImageIcon icon = new ImageIcon("c:/a.jpg");
String empID="aravind";
Image image = icon.getImage();
            BufferedImage bImage = new BufferedImage(image.getWidth(null),image.getHeight(null),BufferedImage.TYPE_INT_RGB);
            Graphics bg = bImage.getGraphics();
            bg.drawImage(image,0,0,null);
            bg.dispose();
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            ImageIO.write(bImage,"jpeg",out);
            byte[] buf = out.toByteArray();
            // setup stream for blob
            ByteArrayInputStream inStream = new ByteArrayInputStream(buf);
            // get or create a connection here
try
{
try
{
 Class.forName("com.mysql.jdbc.Driver").newInstance();
}
catch(ClassNotFoundException e)
{System.out.println(e);
}
String dbURL = "jdbc:mysql:///test";
Connection dbCon;
dbCon = DriverManager.getConnection(dbURL,"root","root");
 

//Connection photoConnection = startup.connectionPool.getConnection();
            String query = "insert into employeephoto (Employee_ID,Binary_Photo,LastUser,LastMod,Created) values ('"+empID+"', ?, 'chandu', NOW(), NOW())";
            PreparedStatement ps = dbCon.prepareStatement(query);
            ps.setBinaryStream(1,inStream,inStream.available());
            int n = ps.executeUpdate();
            System.out.println(n);
            // release or close connection here
            dbCon.close();
            //startup.connectionPool.releaseConnection(photoConnection);
 
}
catch(SQLException e)
{System.out.println(e);
}
        }
        catch (Exception exc)
        {  // process error
        System.out.println("process error" + exc);
        }
    }
    public static void main(String args[])
    {System.out.println("Main Executing");
test t = new test();
}
}
 

Retrieving from the database to JSP Page

<%@  page language="java" session="true"  
import= "com.sun.image.codec.jpeg.JPEGCodec,com.sun.image.codec.jpeg.JPEGEncodeParam,com.sun.image.codec.jpeg.JPEGImageEncoder,
java.awt.image.BufferedImage,java.io.ByteArrayOutputStream,java.io.InputStream,java.sql.Blob,java.sql.Connection,
java.sql.ResultSet,javax.swing.ImageIcon,java.sql.*" %>
 
<%
String employeeID="aravind";
out.println("reached here");
try
{
try
{
 Class.forName("com.mysql.jdbc.Driver").newInstance();
}
catch(ClassNotFoundException e)
{System.out.println(e);
}
String dbURL = "jdbc:mysql:///test";
Connection dbCon;
dbCon = DriverManager.getConnection(dbURL,"root","root");
String query = "select Binary_Photo from employeephoto where Employee_ID = '"+employeeID+"'";
out.println(query);
ResultSet rs = dbCon.createStatement().executeQuery(query);
if(rs.next()) 
{byte[] bytearray = new byte[4096];
             int size=0;
            InputStream sImage;
              sImage = rs.getBinaryStream(1);
              response.reset();
              response.setContentType("image/jpeg");
              response.addHeader("Content-Disposition","filename=logo.jpg");
              while((size=sImage.read(bytearray))!= -1 )
                {
                  response.getOutputStream().write(bytearray,0,size);
                }
              response.flushBuffer();
              sImage.close();
              rs.close();
           }    
  }
  catch (Exception exc)
  {  System.out.println(exc);
  }
  out.println("Returning Photo");
  //return dPhoto;
%>

Also I have a small doubt, When I retrieve the image, I would like to give it a standard height and width, so that it can fit in a table data to make the HR System good looking..

How can I put the height and width in this code..

please help me with your enriched ideas.

Thanks/Regards

Aravind Prasad

[Ulf Dittmer]

When I retrieve the image, I would like to give it a standard height and width, so that it can fit in a table data to make the HR System good looking..

How can I put the height and width in this code..[/QB]

The ImageInfo class can determine width and height of a number of image formats.

[Aravind Prasad]

Dear Ranchers,
I've tried converting the code for inserting image into the database in JSP and tried running it as localhost. It is working fine there. But when i went to another computer and used tried to access the same page, it is giving me error. The code is as follows

put_pic.jsp

<form name="form1" method="POST" action="putpic.jsp">
<input type="file" name="F1" size="55">
<br>
<input type="submit" value="Submit" name="B1">
</form>

putpic.jsp

<%@page language="java" session="true" 
import="java.awt.Graphics,
java.awt.Image,
java.awt.image.BufferedImage,
java.io.ByteArrayInputStream, 
java.io.ByteArrayOutputStream, 
java.sql.Connection, 
javax.imageio.ImageIO,
java.sql.*,
javax.swing.ImageIcon" %>
<%
String f1 = request.getParameter("F1");
if(f1!=null)
out.println(f1);

 
try
{   ImageIcon icon = new ImageIcon(f1);
String empID="dha";
Image image = icon.getImage();
    BufferedImage bImage = new BufferedImage(image.getWidth(null),image.getHeight(null),BufferedImage.TYPE_INT_RGB);
    Graphics bg = bImage.getGraphics();
    bg.drawImage(image,0,0,null);
    bg.dispose();
    ByteArrayOutputStream ou = new ByteArrayOutputStream();
    ImageIO.write(bImage,"jpeg",ou);
    byte[] buf = ou.toByteArray();
    // setup stream for blob
    ByteArrayInputStream inStream = new ByteArrayInputStream(buf);
    // get or create a connection here
try
{
try
{
 Class.forName("com.mysql.jdbc.Driver").newInstance();
}
catch(ClassNotFoundException e)
{out.println(e);
}
String dbURL = "jdbc:mysql:///test";
Connection dbCon;
dbCon = DriverManager.getConnection(dbURL,"root","root");
 
//Connection photoConnection = startup.connectionPool.getConnection();
    String query = "insert into employeephoto (Employee_ID,Binary_Photo,LastUser,LastMod,Created) values ('"+empID+"', ?, 'chandu', NOW(), NOW())";
        PreparedStatement ps = dbCon.prepareStatement(query);
    ps.setBinaryStream(1,inStream,inStream.available());
    int n = ps.executeUpdate();
    out.println(n);
    // release or close connection here
    dbCon.close();
    //startup.connectionPool.releaseConnection(photoConnection);
 
}
catch(SQLException e)
{out.println(e);
}
}
catch (Exception exc)
{  // process error
out.println("process error" + exc);
}
%>

Ranchers, this code is working with http://localhost:8080/put_pic.jsp
But when i try http://192.168.0.10:8080/put_pic.jsp It is showing me an error


Error I'm getting

C:\Users\Aravind Prasad\aravind\aravind.jpg 
process error java.lang.illegalArguementException : Width(-1) and height(-1) cannot be <=0

Please help me ranchers to solve this

Regards

Aravind Prasad

[Aravind Prasad]

Please help me ranchers...

[Rob Spoor]

When an image has not been loaded yet it returns -1 for both width and height. This normally does not happen with ImageIcons, so probably the file is not valid. Possibly the file is incorrect, you'll probably have to convert it to something the remote file system understands.

[Aravind Prasad]

Thanks for the reply.
Can you please find for me a solution.
I too know the same actully.

Regards

Aravind Prasad

[Aravind Prasad]

Ranchers..
No reply for my doubt.. expecting and waiting for a solution!!

Regards

Aravind Prasad

[Tarun Yadav]

Originally posted by Aravind Prasad:
Ranchers..
No reply for my doubt.. expecting and waiting for a solution!!

Regards

Aravind Prasad

Maybe if you worked on it on your own rather than expecting the code to be given to you you'd move faster towards your goal, all your posts seem to be asking for ready made solutions.

Thanks for the reply.
Can you please find for me a solution.
I too know the same actully.

Regards

Aravind Prasad

Rob Prime already hinted at what the problem is and judging from your reply you already know what the problem is right? When you're running it on localhost, the server and the client from where you're uploading your image are the same machine so when the server looks for "c:\imageName.jpg" it finds it.

But when you access the app from a remote machine, "c:\remoteImageName.jpg" doesn't exist on the server, it's on the client. And the server doesn't have access to the client's filesystem. So it fails to locate and insert the image into the DB.

So, now that you know the problem, what could be a solution? You need to get the image onto the server's file system. You could look into uploading the image onto the server rather than simply using the filename to access the file; that would take care of this problem.
[ September 24, 2007: Message edited by: Tarun Yadav ]

[Annahita Moshiri]

Hi everybody

I tried to use this method to upload an image and store it into my db:

         query = "INSERT INTO specimanPictures (herbariumNo, specimanPictureData) " +
                 "VALUES (" +
                 " '" + newSpecimanPicture.getHerbariumNo() + "' ," +
                 " ? " + ")";
 

 

         PreparedStatement pst = con.prepareStatement(query);
         pst.setBinaryStream(1,inStream,inStream.available());
         result = st.executeUpdate(query);
 

but I get the following error form db:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? )' at line 1

Why am I getting this?

[Jeanne Boyarsky]

Annahita,
Is herbariumNo a number or a string? The SQL statement implies it is a string, but it sounds like a number. If it is a number the quotes around it would cause an error in your SQL statement.

Also, is there any reason you aren't using a "?" for the number as well as the BLOB?

[Annahita Moshiri]

Originally posted by Jeanne Boyarsky:
Annahita,
Is herbariumNo a number or a string? The SQL statement implies it is a string, but it sounds like a number. If it is a number the quotes around it would cause an error in your SQL statement.

Also, is there any reason you aren't using a "?" for the number as well as the BLOB?

Hi

herbariumNo is a string. Anyway my problem is solved with a friend's help:

I should have written:
result = st.executeUpdate();

not:
result = st.executeUpdate(query);

Thank you Jeane.