> I'm working on a project using Netbeans IDE 5.5 with Visual Web Pack
> and MySQL as its database.
[quoted text clipped - 13 lines]
>
> 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 - 01 Aug 2007 21:06 GMT
>> I'm working on a project using Netbeans IDE 5.5 with Visual Web Pack
>> and MySQL as its database.
[quoted text clipped - 20 lines]
> 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.

Signature
Lew
Wojtek - 01 Aug 2007 21:13 GMT
Lew wrote :
> Extrinsic factors often force pragmatic compromises.
Hah. I read that as eccentric, and it made even more sense :-)

Signature
Wojtek :-)
Arne Vajhøj - 02 Aug 2007 00:46 GMT
> 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 - 02 Aug 2007 12:38 GMT
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',
'r_freeman@yahoo.com', '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 - 02 Aug 2007 12:52 GMT
> 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
> I'm working on a project using Netbeans IDE 5.5 with Visual Web Pack
> and MySQL as its database.
[quoted text clipped - 9 lines]
> 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();
}
}