Link to home
Create AccountLog in
Avatar of justin_316_sr
justin_316_sr

asked on

Please help me make a program about storing JPEG as BLOB

I need a complete program that converts my JPEG images as BLOB into MySQL database.
say, I have 'rockimages' table with fields 'num' <int>, and 'image' <blob>

i have quite a number of images to store... they are in C:/images...

please help me with the coding...

i also need to retrieve the images for other functions...
Avatar of ashok3sep
ashok3sep

Hi,

You can fins some Helpful codes in the site mentioned Below,
I think no body knows your exact requirements and so no one can code for you only thing is that we can help you providing Docs and small pieces of Logic Codes

http://java.sun.com/developer/onlineTraining/Database/JDBC20Intro/exercises/BLOBPut/help.html

regards,

Freedom
This is how you retreive the Image from Database in JSP

<%@ page contentType="text/html"%>
<%@ page import="oracle.jdbc.driver.OracleConnection,java.sql.DriverManager, java.sql.SQLException, java.sql.*, javax.sql.*,java.io.*, java.text.*, java.util.*, javax.naming.*;"%>
<html>
<head>
<title>IMAGE  BLOB</title>
</head>
<body>
<%
response.setContentType("image/jpeg");

char c;
byte [] b;
int i=0;

String rs_stafinfo__MMColParam = "1";
if (request.getParameter("stafid")!=null) {rs_stafinfo__MMColParam = (String)request.getParameter("stafid");}

try
{
Context initContext = new InitialContext();
DataSource ds = (DataSource)initContext.lookup("java:/comp/env/jdbc/smkview");
Connection con = ds.getConnection();
Statement stmt=con.createStatement();

ResultSet rs=stmt.executeQuery("select KK90PSTAFF from SMK.KK90FPSTAFF where SUBSTR(KK90UKMPER,3, 8)='"+rs_stafinfo__MMColParam+"'");
if (rs.next())
{
ServletOutputStream sout = response.getOutputStream();
InputStream in = rs.getBinaryStream("KK90PSTAFF");
int avlBytes = in.available();
b = new byte[avlBytes]; /*[0x7a120]; */
for(i = in.read(b); i >= 0;)
{
sout.write(b);
i = in.read(b);
}
in.close();
sout.flush();
sout.close();
}
stmt.close();
rs.close();
con.close();
}
catch (SQLException e)
{
System.err.println (e) ;
%>
<%=e%>
<%
}
%>
</body>
</html>




Regards,

Freedom,
import java.util.*;
import javax.sql.*;
import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.OracleResultSet;

public class testBlob
{
  public static void main (String [] args) throws Exception
  {
    String content;
    String url = "jdbc:oracle:thin:@localhost:1521:ORAST";
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection (url,"testClob","testClob");
    conn.setAutoCommit(false);
    String cmd ="";
    Statement stmt = conn.createStatement();

    stmt.execute("drop table my_blob_table");
    stmt.execute("Create table my_blob_table(x varchar(30), c blob)");

    PreparedStatement pstmt = conn.prepareStatement("insert into my_blob_table values('row1',empty_blob())");
    pstmt.execute();

    ResultSet rset = stmt.executeQuery("SELECT * FROM my_blob_table for update");
    if(rset.next())
    {
        // Get the Blob locator and open output stream for the Blob
        Blob zipBlob = rset.getBlob(2);
        OutputStream blobOutputStream = ((oracle.sql.BLOB)zipBlob).getBinaryOutputStream();

        // Open the sample file as a stream for insertion into the Blob column
        File zipFile = new File("c:\\marks.zip");
        InputStream sampleFileStream = new FileInputStream(zipFile);

        // Buffer to hold chunks of data to being written to the Blob.
        byte[] buffer = new byte[10* 1024];

        // Read a chunk of data from the sample file input stream, and write the
        // chunk to the Blob column output stream. Repeat till file has been
        // fully read.
        int nread = 0;   // Number of bytes read
        while( (nread= sampleFileStream.read(buffer)) != -1 ) // Read from file
          blobOutputStream.write(buffer, 0, nread);         // Write to Blob

        // Close both streams
        sampleFileStream.close();
        blobOutputStream.close();


        // Open a stream to read the Blob data  
        InputStream blobStream = zipBlob.getBinaryStream();

        // Open a file stream to save the Blob data
        FileOutputStream fileOutStream = new FileOutputStream("c:\\marks_zip.zip");

        // buffer holding bytes to be transferred
        byte[] nbuffer = new byte[10];
        int nbytes = 0; // Number of bytes read


        // Read from the Blob data input stream, and write to the
        // file output stream  
        while((nbytes = blobStream.read(nbuffer)) != -1) //Read from Blob stream    
          fileOutStream.write(nbuffer, 0, nbytes); // Write to file stream
         
        fileOutStream.close();
        blobStream.close();
    }
    conn.close();
    rset.close();
    stmt.close();
  }
}

-CE
My above post is for an Oracle DB. Please customize it acc. to ur needs and it shd work

-CE
Avatar of TimYates
If these images are to be displayed on a web-page, I would recommend not putting them in the db, and just adding their paths to the db...

Just so you know ;-)

Tim
Avatar of justin_316_sr

ASKER

>> If these images are to be displayed on a web-page, I would recommend not putting them in the db, and just adding their paths to the db...

thanks for the advice Tim but i won't be using it on a web-page but on a JFrame or an Applet.
Fair enough :-)
OutputStream blobOutputStream = ((oracle.sql.BLOB)zipBlob).getBinaryOutputStream();

>>how will I make this work for MySQL??
http://javaalmanac.com/egs/java.sql/InsertPs.html

Shows you that you should do:

try {
        // Prepare a statement to insert a record
        String sql = "INSERT INTO mytable ( blob_col ) VALUES( ? )";
        PreparedStatement pstmt = connection.prepareStatement(sql);
// Set the blob
        file = new File("infilename3");
        is = new FileInputStream(file);
        pstmt.setBinaryStream(1, is, (int)file.length());
        // Insert the row
        pstmt.executeUpdate();
    } catch (SQLException e) {
    } catch (FileNotFoundException e) {
    }
TimYates, how should I apply that to CodingExperts' code posted above?
I'm trying to manipulate his code..
errrrrr....

you've got a jdbc connection right?  To the mysql database?
yup!
here's what i've done so far (just manipulated the codes above)

error: C:\ITS\GUI\GUI\imageBlob.java:38: package sql does not exist
        OutputStream blobOutputStream = ((sql.BLOB)zipBlob).getBinaryOutputStream();

import java.util.*;
import javax.sql.*;
import java.sql.*;
import java.io.*;


public class imageBlob
{
private static final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
 
private static final String SOURCE = "jdbc:odbc:its";
   
private static Connection dbConn = null;
        
  public static void main (String [] args) throws Exception
  {
   
   
    String content;
    //String url = "jdbc:oracle:thin:@localhost:1521:ORAST";
    Class.forName(DRIVER);
    Connection conn = DriverManager.getConnection (SOURCE);
    conn.setAutoCommit(false);
    String cmd ="";
    Statement stmt = conn.createStatement();

    stmt.execute("drop table rock_images");
    stmt.execute("Create table rock_images(id varchar(30), image blob)");

    PreparedStatement pstmt = conn.prepareStatement("insert into rock_images values('row1',empty_blob())");
    pstmt.execute();

    ResultSet rset = stmt.executeQuery("SELECT * FROM rock_images for update");
    if(rset.next())
    {
        // Get the Blob locator and open output stream for the Blob
        Blob zipBlob = rset.getBlob(2);
        OutputStream blobOutputStream = ((sql.BLOB)zipBlob).getBinaryOutputStream();

        // Open the sample file as a stream for insertion into the Blob column
        File zipFile = new File("c:\\icons.zip");
        InputStream sampleFileStream = new FileInputStream(zipFile);

        // Buffer to hold chunks of data to being written to the Blob.
        byte[] buffer = new byte[10* 1024];

        // Read a chunk of data from the sample file input stream, and write the
        // chunk to the Blob column output stream. Repeat till file has been
        // fully read.
        int nread = 0;   // Number of bytes read
        while( (nread= sampleFileStream.read(buffer)) != -1 ) // Read from file
          blobOutputStream.write(buffer, 0, nread);         // Write to Blob

        // Close both streams
        sampleFileStream.close();
        blobOutputStream.close();


        // Open a stream to read the Blob data  
        InputStream blobStream = zipBlob.getBinaryStream();

        // Open a file stream to save the Blob data
        FileOutputStream fileOutStream = new FileOutputStream("c:\\icons_zip.zip");

        // buffer holding bytes to be transferred
        byte[] nbuffer = new byte[10];
        int nbytes = 0; // Number of bytes read


        // Read from the Blob data input stream, and write to the
        // file output stream  
        while((nbytes = blobStream.read(nbuffer)) != -1) //Read from Blob stream    
          fileOutStream.write(nbuffer, 0, nbytes); // Write to file stream
         
        fileOutStream.close();
        blobStream.close();
    }
    conn.close();
    rset.close();
    stmt.close();
  }
}
ASKER CERTIFIED SOLUTION
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
where:

1) "its" is the name of your database
2) USERNAME is set to your username
3) PASSWORD is set to your password
4) localhost is set to the machine that is running the db
5) "mytable" is the name of the table you want to insert into
6) "id_col" is a string field in that table
7) "blob_col" is a blob field in that same table
8) you have the drivers from that link I gave you in your classpath (see the instructions)

Good luck!!

Tim
PS:  I haven't tested that code, I just typed it in here...but it should be ok...or *nearly* ok ;-)
encountered error:

C:\ITS\GUI\GUI\ImageBlob2.java:28: cannot resolve symbol
symbol  : variable file
location: class ImageBlob2
      pstmt.setBinaryStream( 2, is, (int)file.length() );


8) you have the drivers from that link I gave you in your classpath (see the instructions)
>>which link?

I'm getting confused!!!waaah :((
Whoops...I didn't post the link ;-)

LOL

http://dev.mysql.com/downloads/connector/j/3.1.html  <-- drivers
http://dev.mysql.com/doc/connector/j/en/cj-installation.html  <--- installation
http://dev.mysql.com/doc/connector/j/en/cj-app-development.html  <-- quick setup instructions
http://dev.mysql.com/doc/connector/j/en/index.html <--- main manual

>> encountered error:

Whoops!  Change:

      is = new FileInputStream( new File( "INPUT_FILENAME" ) );
      pstmt.setBinaryStream( 2, is, (int)file.length() );

to

      java.io.File file = new File( "INPUT_FILENAME" ) ;
      is = new FileInputStream( file );
      pstmt.setBinaryStream( 2, is, (int)file.length() );
oh, and "INPUT_FILENAME" should be the name of the  file you want to store as a BLOB...
java.sql.SQLException: General error message from server: "Unknown database 'its'"
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1906)
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:954)
        at com.mysql.jdbc.Connection.createNewIO(Connection.java:1786)
        at com.mysql.jdbc.Connection.<init>(Connection.java:450)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:411)
        at java.sql.DriverManager.getConnection(DriverManager.java:512)
        at java.sql.DriverManager.getConnection(DriverManager.java:193)
        at ImageBlob2.main(ImageBlob2.java:22)
ok i think I got it to work..thanks

Now, how could I retrieve that blob image to display in an ImageIcon?
ok...i have already created my blob images...
now, i have to retrieve it...
how can I get them collectively?
and place them into pic2,pic3,pic4,pic5 here in my code:


import java.io.*;
import java.awt.*;
import java.awt.event.*;
import java.awt.datatransfer.*;
import java.awt.dnd.*;
import javax.swing.*;
import java.sql.*;

public class DragPictureDemo2 extends JPanel
{

private static final String DRIVER = "com.mysql.jdbc.Driver";
private static Connection dbConn = null;

DTPicture pic1, pic2, pic3, pic4, pic5;
PictureTransferHandler picHandler;

Statement stmt;
        ResultSet rs;
        Image i;

public DragPictureDemo2() {
try
    {
      Class.forName(DRIVER);
      dbConn = DriverManager.getConnection("jdbc:mysql://localhost/expert?user=root&password=root");
     
      stmt = dbConn.createStatement();
      rs = stmt.executeQuery("SELECT blob_col FROM rocksimages");
      if (rs.next())
      {
          Blob blob = rs.getBlob("blob_col");
          long n = blob.length();
          byte[] bytes = blob.getBytes(1, (int)blob.length());
          i = Toolkit.getDefaultToolkit().createImage(bytes);
      }
      stmt.close(  );
      } // end try

    catch (ClassNotFoundException cnfe)
    {
      System.err.println(cnfe);
      System.exit(1);
    } // end catch

    catch (SQLException sqle)
    {
      System.err.println(sqle);
      System.exit(1);
    }


      picHandler = new PictureTransferHandler();
      JPanel mugshots = new JPanel(new GridLayout(8, 2));
      mugshots.setBorder(BorderFactory.createTitledBorder(BorderFactory.createEtchedBorder(),"Rocks"));

      pic1 = new DTPicture(i);
                pic1.setTransferHandler(picHandler);
      mugshots.add(pic1);

                //pic2
                //pic3
                //pic4
                //pic5


mugshots.setPreferredSize(new Dimension(140, 550));
add(mugshots);
setBorder(BorderFactory.createEmptyBorder(20,20,20,20));
}
urgent help needed! Points: 500!!!
Btw, thanks TimYates. you are guaranteed 250 points. =)
I just need an answer for my other question before I approve your comment =)
I gotta go out, but if you do a select statement to real the blob field, then get an inputstream on the Blob object the resultset returns, you should be able to use ImageIO like this http://javaalmanac.com/egs/javax.imageio/BasicImageRead.html to read the inputstream into a BufferedImage
sorry I can't be of huge help atm...it's Friday night ;-)

I'll try and get some time over the weekend if this is still open and noone else has jumped in to help :-)

Good luck!

Tim
Help! help! help!
    stmt = dbConn.createStatement();
     rs = stmt.executeQuery("SELECT blob_col FROM rocksimages");
     while( rs.next() )
     {
         Blob blob = rs.getBlob("blob_col");
         BufferedImage img = ImageIO.read( blob.getBinaryStream() ) ;
         System.out.println( "Loaded an image of size " + img.getWidth() + "x" + img.getHeight() ) ;
     }
should do it :-)
You will need to:

import java.awt.image.* ;
import javax.imageio.* ;
i should change this part of my code:

stmt = dbConn.createStatement();
      rs = stmt.executeQuery("SELECT blob_col FROM rocksimages");
     if (rs.next())
     {
         Blob blob = rs.getBlob("blob_col");
         long n = blob.length();
         byte[] bytes = blob.getBytes(1, (int)blob.length());
         i = Toolkit.getDefaultToolkit().createImage(bytes);
     }

to this:

stmt = dbConn.createStatement();
     rs = stmt.executeQuery("SELECT blob_col FROM rocksimages");
     while( rs.next() )
     {
         Blob blob = rs.getBlob("blob_col");
         BufferedImage img = ImageIO.read( blob.getBinaryStream() ) ;
         System.out.println( "Loaded an image of size " + img.getWidth() + "x" + img.getHeight() ) ;
     }



right???

uhmm...can you please explain to me what that code is actually doing? tnx! =)
DragPictureDemo2.java:72: unreported exception java.io.IOException; must be caught or declared to be thrown
        BufferedImage img = ImageIO.read( blob.getBinaryStream() ) ;
1 error
Not sure what DTPicture is...

But you'll probably want something like:

     stmt = dbConn.createStatement();
     rs = stmt.executeQuery("SELECT blob_col FROM rocksimages");
     while( rs.next() )
     {
         Blob blob = rs.getBlob("blob_col");
         BufferedImage img = ImageIO.read( blob.getBinaryStream() ) ;
         System.out.println( "Loaded an image of size " + img.getWidth() + "x" + img.getHeight() ) ;
         mugshots.add( img );
     }

so that each row in the db adds a mugshot to the "mugshots" object...

that code basically loops through every pic in the database, gets the Blob for it, gets an inputstream to the data (original file in the blob), and then creates a BufferedImage out of it...

Tim
>> DragPictureDemo2.java:72: unreported exception java.io.IOException; must be caught or declared to be thrown
 
Ok then...catch the exception:

     while( rs.next() )
     {
         Blob blob = rs.getBlob("blob_col");
         try
         {
             BufferedImage img = ImageIO.read( blob.getBinaryStream() ) ;
             System.out.println( "Loaded an image of size " + img.getWidth() + "x" + img.getHeight() ) ;
             mugshots.add( img );
         }
        catch( IOException ex )
         {
               ex.printStackTrace() ;
         }
     }
can't I use an array to store the images I retrieved from the database?

I tried doing this but didn't work out...it had errors:

Statement stmt;
ResultSet rs;

 try
    {
      Class.forName(DRIVER);
      dbConn = DriverManager.getConnection("jdbc:mysql://localhost/expert?user=root&password=root");
     
      stmt = dbConn.createStatement();
      rs = stmt.executeQuery("SELECT id_col, blob_col FROM rock_icons");
     

while (rs.next())
      {
          String [] rock = rs.getString("id_col");
          Blob blob = rs.getBlob("blob_col");
          long n = blob.length();
          byte[] bytes = blob.getBytes(1, (int)blob.length());
          Image [] img = Toolkit.getDefaultToolkit().createImage(bytes);
      }

====================================================================
DragPictureDemo2.java:86: incompatible types
found   : java.lang.String
required: java.lang.String[]
          String [] rock = rs.getString("id_col");
                                                            ^
DragPictureDemo2.java:90: incompatible types
found   : java.awt.Image
required: java.awt.Image[]
          Image [] i = Toolkit.getDefaultToolkit().createImage(bytes);
                                                                                              ^
2 errors
====================================================================

I was trying to do those above, so that I can do this to display the images:

pic1 = new DTPicture(img[0]);
pic1.setTransferHandler(picHandler);
pic1.setName(rock[0]);

pic2 = new DTPicture(img[1]);
pic2.setTransferHandler(picHandler);
pic2.setName(rock[1]);
>>Not sure what DTPicture is...

this supports the data transfer (drag and drop) of the Picture component (where the image is painted)
> it had errors:

because getString returns a String, not a String Array...
>  can't I use an array to store the images I retrieved from the database?

Spose so...  but you'd need to do 2 selects...1 to count the rows, then the other to get them
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
OMG it's working now!
yey! yey! yey!
Thanks so much TimYates!
you're the best! =D
:-)  Glad I could help :-)

Good luck!

Tim