Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsWhite Papers
Discussion GroupsFirst AidDatabasesJavaBeansGUIJava 3DVirtual MachineCORBASecurityToolsGeneral
Java DirectoryOpen Source ProjectsSample Book ChaptersUser GroupsWeb Resources
Related Topics
Databases.NETMore Topics ...

Java Forum / General / August 2007

Tip: Looking for answers? Try searching our database.

Insert picture in MySQL using JDBC

Thread view: 
reexana - 01 Aug 2007 16:22 GMT
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 - 01 Aug 2007 18:45 GMT
> 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
Arne Vajhøj - 02 Aug 2007 00:41 GMT
> 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();
   }
}


Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.