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...
Java

Avatar of undefined
Last Comment
TimYates

8/22/2022 - Mon
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
ashok3sep

ashok3sep

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,
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
CodingExperts

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
CodingExperts

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

-CE
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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.
TimYates

Fair enough :-)
justin_316_sr

ASKER
OutputStream blobOutputStream = ((oracle.sql.BLOB)zipBlob).getBinaryOutputStream();

>>how will I make this work for MySQL??
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
TimYates

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) {
    }
justin_316_sr

ASKER
TimYates, how should I apply that to CodingExperts' code posted above?
I'm trying to manipulate his code..
TimYates

errrrrr....

you've got a jdbc connection right?  To the mysql database?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
justin_316_sr

ASKER
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
TimYates

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
TimYates

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
TimYates

PS:  I haven't tested that code, I just typed it in here...but it should be ok...or *nearly* ok ;-)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
justin_316_sr

ASKER
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 :((
TimYates

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() );
TimYates

oh, and "INPUT_FILENAME" should be the name of the  file you want to store as a BLOB...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
justin_316_sr

ASKER
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)
justin_316_sr

ASKER
ok i think I got it to work..thanks

Now, how could I retrieve that blob image to display in an ImageIcon?
justin_316_sr

ASKER
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));
}
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
justin_316_sr

ASKER
urgent help needed! Points: 500!!!
justin_316_sr

ASKER
Btw, thanks TimYates. you are guaranteed 250 points. =)
I just need an answer for my other question before I approve your comment =)
TimYates

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
TimYates

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
justin_316_sr

ASKER
Help! help! help!
TimYates

    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() ) ;
     }
Your help has saved me hundreds of hours of internet surfing.
fblack61
TimYates

should do it :-)
TimYates

You will need to:

import java.awt.image.* ;
import javax.imageio.* ;
justin_316_sr

ASKER
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! =)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
justin_316_sr

ASKER
DragPictureDemo2.java:72: unreported exception java.io.IOException; must be caught or declared to be thrown
        BufferedImage img = ImageIO.read( blob.getBinaryStream() ) ;
1 error
TimYates

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
TimYates

>> 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() ;
         }
     }
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
justin_316_sr

ASKER
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]);
justin_316_sr

ASKER
>>Not sure what DTPicture is...

this supports the data transfer (drag and drop) of the Picture component (where the image is painted)
TimYates

> it had errors:

because getString returns a String, not a String Array...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
TimYates

>  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
TimYates

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
justin_316_sr

ASKER
OMG it's working now!
yey! yey! yey!
Thanks so much TimYates!
you're the best! =D
TimYates

:-)  Glad I could help :-)

Good luck!

Tim
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck