storing and loading images in a java application using mysql

Firstly the examples are in a program that takes part of a video capture image of employee photos from the screen and saves it to MySQL.

The retrieve portion is used the display the captured photo for the employee from the database.

> i just want to get this right.so in the database we just define a blob field

Yes - for example here is the structure of the table I use.

Structure of Table employeePhoto

********************************

'Employee_ID','varchar(15)'primary key

'Binary_Photo','mediumblob'

'LastUser','varchar(100)'

'LastMod','datetime'

'Created','datetime'

> and add the image as a string specifying the path and filename right?

In the examples the image is not in a string - it starts in a buffered image (the partial screen capture).

You do not need to capture the image from the screen.

It is then converted to a jpeg in a byte array.

You may not need to do this.

The byte array is funneled through a byte input stream to create the blob.

You will need to present your image as a byte input stream to create the blob.

> the code is used in a swing application

> is that all supposed to be coded in one class

As the code is only an example I am not suggesting I did it the most efficent manner or the best coding.

>I have seen other examples just using the JDBC blob functions when retrieving the image but I could not get them to work.

All I can say is this works for me.

Note for you it may be easier to just save the location of the image (c:\images\sample.jpg) in a varchar field in the database and save the images as gifs or jpgs in a directory on the disk.

Then query the database and get the location and load the image.

rykk

rykk. at 2007-6-29 >

Assuming you retrieved the image - it looks like you did.

You must return image you got from the blob.

public static ImageIcon dbImage() {

ImageIcon dPhoto = null;

DBAccess dba = new DBAccess(); // this dontains code for connecting to database

res = dba.query("Select TCBlob from teecolor where TColor = '1';");

try {

if (res.next()) {

Blob image = res.getBlob("TCBlob");

InputStream input = image.getBinaryStream();

ByteArrayOutputStream output = new ByteArrayOutputStream();

byte[] rb = new byte[1024]; // set read buffer size

int ch = 0;

while ((ch=input.read(rb)) != -1){output.write(rb, 0, ch);}

byte[] b = output.toByteArray(); // transfer to byte buffer

input.close();

output.close();

dPhoto = new ImageIcon(b); // load final buffer to image icon to return

System.out.println("Got the image");

}

else {System.out.println("Missed the image");}

}

catch (Exception e){e.printStackTrace();}

return dPhoto; // return the image as an image icon

}

You can then dosplay the imageicon you got back

I would actually suggest you pass in the selection.

[code]

public static ImageIcon dbImage(String selectionColor) {

...

res = dba.query("Select TCBlob from teecolor where TColor = '"+selectionColor+"';");

...

}

[code]

rykk

rykk. at 2007-6-29 >

hey

thanks so much for ur help, im getting there slowly :)

i did as u advised and it compiles but doesnt actually load the image. when i run it, database connection is achieved and it also prints 'Got the image' on the msdos screen. however, it doesnt display the pic. i think for some reason its not exiting the if (res.next()) loop.

can you please have a look again...thanks a lot.

public class dbImage extends JFrame

{ //ResultSet res;

JPanel p;

public dbImage()

{

Container contentPane=getContentPane();

//JFrame dbImage();

ImageIcon iIcon = dbImage("1");

if (iIcon == null)

{System.out.println("No image");}

else

{ JLabel l = new JLabel(iIcon);

p= new JPanel();

p.setLayout(new BorderLayout());

p.add(l);

}

contentPane.add(p, "Center");

}

public static ImageIcon dbImage(String selectionColor)

{ResultSet res;

//Container contentPane=getContentPane();

ImageIcon dPhoto = null;

DBAccess dba = new DBAccess();

res = dba.query("Select TCBlob from teecolor where TColor = '"+selectionColor+"';");

try

{if (res.next())

{Blob image = res.getBlob("TCBlob");

InputStream input = image.getBinaryStream();

ByteArrayOutputStream output = new ByteArrayOutputStream();

// set read buffer size

byte[] rb = new byte[1024];

int ch = 0;

while ((ch=input.read(rb)) != -1)

{output.write(rb, 0, ch);

}

// transfer to byte buffer

byte[] b = output.toByteArray();

input.close();

output.close();

// load final buffer to image icon

dPhoto = new ImageIcon(b);

System.out.println("Got the image");

}

else {System.out.println("Missed the image");}

}

catch (Exception e)

{e.printStackTrace();

}

return dPhoto;

// return the image as an image icon

//contentPane.add(p, "Center");

//setContentPane(p);

//this.repaint();

//return iIcon; // return the image as an image icon

}

public static void main(String[]args)

{Frame frame = new dbImage();

Toolkit toolkit = Toolkit.getDefaultToolkit();

Dimension scrnsize = toolkit.getScreenSize();

frame.setSize( (int) scrnsize.getWidth(),(int)scrnsize.getHeight());

frame.show();

}

}

thanks :)

nat!

mystified_natasha at 2007-6-29 >

Ok I will try to describe what you have accomplished with the insert you are using.

insert into teecolor values('1', '(j:/micky.jpeg)');

Will not work.

Firstly Tcolor is an integer field therefore you do not quote the value.

For even a chance to work it would have to be

insert into teecolor values(1, '(j:/micky.jpeg)');

Now with this statement:

The integer value 1 will end up in Tcolor integer as you think.

The text value "(j:/micky.jpeg)" will end up in the TCBlob blob field.

This is a text value not the jpeg image.

To save a jpg you would have do something like the following.

PreparedStatement ps = dba.prepareStatement("insert into teecolor (Tcolor,TCBlob) values(1, ?);

File fileIn = new File("j:/micky.jpeg");

int fileLength = (int)fileIn.length();

InputStream streamedJpg = new FileInputStream(fileIn);

ps.setBinaryStream(1,streamedJpg,fileLength);

ps.executeUpdate();

You must use a file stream to load the blob.

rykk

rykk. at 2007-6-29 >

Looking at your current code:

// the code is returning a PreparedStatement but you are executing it - why return it?

public PreparedStatement prepSt(String dbQuery){

PreparedStatement ps = null;

if (con != null){

try {

// this statement would work

PreparedStatement ps1 = con.prepareStatement(dbQuery);

// this will not - first it is ps not ps1 secondly it has dbQuery as a parameter

// you did not set the PreparedStatement parameter values here

//so it will not work anyway

ps.executeUpdate(dbQuery);

// it s/b ps1.executeUpdate();

}

catch(SQLException sqle){System.out.println(sqle);}

}

else{

getCon();

ps = prepSt(dbQuery);

}

return ps;

}

The method you have set up will not work as you must set the prepared statement parameters for each different prepared statement related to the parameters it expects.

Or you can pass them to the method.

String dbQuery = "insert into teecolor (Tcolor,TCBlob) values(1, ?)"

PreparedStatement ps1 = con.prepareStatement(dbQuery);

// Setup to create parameter

File fileIn = new File("j:/micky.jpeg");

int fileLength = (int)fileIn.length();

InputStream streamedJpg = new FileInputStream(fileIn);

// Set parameters

ps1.setBinaryStream(1,streamedJpg,fileLength);

// now execute prepared statement

ps1.executeUpdate();

For example if you pass the image parameter in you would need to do the following:

// set query string

String dbQuery = "insert into teecolor (Tcolor,TCBlob) values(1, ?)"

// Setup to create parameter

File fileIn = new File("j:/micky.jpeg");

int fileLength = (int)fileIn.length();

InputStream streamedJpg = new FileInputStream(fileIn);

prepSt(dbQuery, fileLength, streamedJpg);

...

public void prepSt(String dbQuery, int fileLength, InputStream streamedJpg){

PreparedStatement ps = null;

if (con != null){

try {

PreparedStatement ps1 = con.prepareStatement(dbQuery);

// set parameter

ps1.setBinaryStream(1,streamedJpg,fileLength);

// execute

ps1.executeUpdate();

}

catch(SQLException sqle){System.out.println(sqle);}

}

else{

getCon();

ps = prepSt(dbQuery, fileLength, streamedJpg);

}

}

I think you are trying to hard to be fancy before you have things working.

Get each part of your code working first - then when you know it works refine it.

rykk

rykk. at 2007-7-1 >