Insert picture in MySQL using JDBC

[reexana]

I'm working on a project using Netbeans IDE 5.5 with Visual Web Pack
and MySQL as its database.

The project is about displaying the information about an item selected
by a user from a drop-down list.

I managed to display the information in the database when a user
selected the keyword but don't know how to store picture in the
database and retrieve it to display to the user along with the
information.

I tried to use BLOB, but it didn't work out. Maybe, I did the wrong
way.

Anyone please help me? I really need help.

Thank you in advance.

 

[Manish Pandit]

I'm working on a project using Netbeans IDE 5.5 with Visual Web Pack
and MySQL as its database.

The project is about displaying the information about an item selected
by a user from a drop-down list.

I managed to display the information in the database when a user
selected the keyword but don't know how to store picture in the
database and retrieve it to display to the user along with the
information.

I tried to use BLOB, but it didn't work out. Maybe, I did the wrong
way.

Anyone please help me? I really need help.

Thank you in advance.

IMO you should be storing images on the file system, and serve them
via HTTP or local file system reading - based on the security
requirements.

Can you copy/paste the set of JDBC calls you made to store and
retrieve the files in a MySQL BLOB?

-cheers,
Manish

 

[Lew]

IMO you should be storing images on the file system, and serve them
via HTTP or local file system reading - based on the security
requirements.

Can you copy/paste the set of JDBC calls you made to store and
retrieve the files in a MySQL BLOB?

I've stored and retrieved images using PostgreSQL and Oracle. There are
"setBytes()" commands to the PreparedStatement that you can use to fill the
column.

Sometimes organizational policy prohibits the use of local files on an
application server. There are other reasons why one might choose to store
image or other binary data in a database. Extrinsic factors often force
pragmatic compromises.

 

[Wojtek]

Lew wrote :

Extrinsic factors often force pragmatic compromises.

Hah. I read that as eccentric, and it made even more sense

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

I'm working on a project using Netbeans IDE 5.5 with Visual Web Pack
and MySQL as its database.

The project is about displaying the information about an item selected
by a user from a drop-down list.

I managed to display the information in the database when a user
selected the keyword but don't know how to store picture in the
database and retrieve it to display to the user along with the
information.

I tried to use BLOB, but it didn't work out. Maybe, I did the wrong
way.

BLOB should work for pictures.

Attached below is a small demo program that stores and
retrieves a picture in a BLOB field in MySQL.

Arne

import java.io.*;
import java.sql.*;

public class BlobTest {
public static void main(String[] args) throws Exception {
byte[] b = new byte[(int)(new File("C:\\elogo.png")).length()];
InputStream is = new FileInputStream("C:\\elogo.png");
is.read(b);
is.close();
Class.forName("com.mysql.jdbc.Driver");
Connection con =
DriverManager.getConnection("jdbc:mysql://localhost/Test", "", "");
PreparedStatement ins = con.prepareStatement("INSERT INTO
BlobTest VALUES (?,?)");
ins.setInt(1, 123);
ins.setBytes(2, b);
ins.executeUpdate();
PreparedStatement sel = con.prepareStatement("SELECT Picture FROM
BlobTest WHERE ID=?");
sel.setInt(1, 123);
ResultSet rs = sel.executeQuery();
rs.next();
byte[] b2 = rs.getBytes(1);
con.close();
OutputStream os = new FileOutputStream("C:\\elogo2.png");
os.write(b2);
os.close();
}
}

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

IMO you should be storing images on the file system, and serve them
via HTTP or local file system reading - based on the security
requirements.

That is often stated.

It was definitely true 10-12 years ago with a MS Access database.

It is not necessarily true with todays database (as long as the
pictures have a reasonable size).

I actually once made a test with ASP.NET and MySQL (the fact
that it is ASP.NET and not J2EE should not matter much). The
results were:

File (1 threads): 1,9 get per second
File (10 threads): 2,1 get per second
File with web app cache (1 threads): 16,8 get per second
File with web app cache (10 threads): 17,8 get per second
File directly by web server (1 threads): 17,6 get per second
File directly by web server (10 threads): 20,1 get per second
Database (1 threads): 10,7 get per second
Database (10 threads): 11,6 get per second
Database with web app cache (1 threads): 17,1 get per second
Database with web app cache (10 threads): 19,5 get per second

And storing the files in the database together with the meta
data is much easier to manage.

Arne

 

[reexana]

I'm using Visual Web Application in Netbeans 5.5 to build the project.
Therefore, every single thing including to create the database is done
by interacting with the interface provided and no JAVA scripts
involved.

Once I dragged the component onto the layout, it will automatically
create the script.

To assign the datatype for picture, I simply selected BLOB but don't
know how it works. Because, to insert text information, I used the
basic way which is

INSERT INTO members
VALUES (1, 'Ricky', '"The Dragon"', 'Freeman','334 612-5678',
'(e-mail address removed)', '1996-01-01')

The problem is, how to insert the picture by using the MySQL command?

Any help and suggestion is appreciated.

Thank you

 

[Thomas Kellerer]

IMO you should be storing images on the file system, and serve them
via HTTP or local file system reading - based on the security
requirements.

Only if you do not need transaction control when updating/accessing the
images.

And I agree Arne: storing the images in the database seems to be the
better solution nowadays. When putting them into the filesystem you need
to distribute them accross several directories because even modern
filesystems have problems with handling thousands if not millions of
files in a single directory.

Thomas