Can we fetch an added image from MySql database by using java?

Question

I have added an image into a MySql database. And I am trying to fetch the image from the database by creating a new file by using java. The problem is that everything goes fine and image file has been created but the image file doesn't contain the image, it has just nothing and the size of the new created image file differs from the original one..... The Sql Code:

CREATE TABLE `pictures` ( `id` int(11) NOT NULL auto_increment, `description` varchar(20) default NULL, `photo` blob, PRIMARY KEY (`id`) );
insert into pictures values('1','pic1','D:\java.jpg');

And the java code:

public class ReadBlobPicture
{
    static String url = "jdbc:mysql://localhost:3306/imgdatabase";
    static String username = "root";
    static String password = "tiger";

    public static void main(String[] args) 
        throws Exception 
    {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(url, username, password);
        String sql = "SELECT photo FROM pictures where id=1";
        Statement stmt = conn.prepareStatement(sql);
        ResultSet resultSet = stmt.executeQuery(sql);
        Object obj = resultSet;

        while (resultSet.next()) 
        {
            File image = new File("E:\\java12.jpg");
            FileOutputStream fos = new FileOutputStream(image);
            System.out.println(resultSet.getString(1));
            byte[] buffer = new byte[1];
            InputStream is2 = resultSet.getBinaryStream(1);
            System.out.println(is2.available());

            while (is2.read() > 0) 
            {
                fos.write(buffer);
                fos.flush();
            }

            fos.close();
        }

        conn.close();
    }
}

TofuBeer · Accepted Answer · 2010-01-30 07:52:58Z

You read from the stream, but never save the read character into the buffer...

while (is2.read() > 0) 
{
    fos.write(buffer);
    fos.flush();
}

something like:

int x;

while((x = is2.read()) != -1)
{
   fos.write(x);
}

fos.flush();

(edit - from the comment on another answer...)

For the insert statement not inserting the image see this link

/*

Defining the Table: Oracle and MySql

create table MyPictures (
   id INT PRIMARY KEY,
   name VARCHAR(0),
   photo BLOB
);
*/
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class InsertPictureToMySql {
  public static void main(String[] args) throws Exception, IOException, SQLException {
    Class.forName("org.gjt.mm.mysql.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/databaseName", "root", "root");
    String INSERT_PICTURE = "insert into MyPictures(id, name, photo) values (?, ?, ?)";

    FileInputStream fis = null;
    PreparedStatement ps = null;
    try {
      conn.setAutoCommit(false);
      File file = new File("myPhoto.png");
      fis = new FileInputStream(file);
      ps = conn.prepareStatement(INSERT_PICTURE);
      ps.setString(1, "001");
      ps.setString(2, "name");
      ps.setBinaryStream(3, fis, (int) file.length());
      ps.executeUpdate();
      conn.commit();
    } finally {
      ps.close();
      fis.close();
    }
  }
}

thanks it works.but if i add it from sql browser what will be the code?

Omar Al Kababji · Accepted Answer · 2010-01-30 07:52:28Z

0

you have a problem writing to your outputstream, it should be like this:

while ((buffer[0] = is2.read()) > 0) 
            {
                fos.write(buffer[0]);
                fos.flush();
            }

And your picture should be saved in the database as a BLOB!

P.S. your buffer is useless since it has a size of 1, you should make it at least 512 or 1024 to void reading byte by byte.

Hope it helped.

answered Jan 30, 2010 at 7:52

Omar Al Kababji

1,8384 gold badges17 silver badges31 bronze badges

Comments

Klaus Byskov Pedersen · Accepted Answer · 2010-01-30 07:51:54Z

0

insert into pictures values('1','pic1','D:\java.jpg');

Does not insert binary jpg data into the database, it inserts the string D:\java.jpg

answered Jan 30, 2010 at 7:51

Klaus Byskov Pedersen

122k31 gold badges192 silver badges223 bronze badges

Comments

Kiran · Accepted Answer · 2011-10-12 18:56:12Z

// create a file called MySqlInsertBlob.java
//==========import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.Blob;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.*;

class MySqlInsertBlob {
FileOutputStream image;
Connection conn = null;
PreparedStatement pstmt = null;
Statement stmt= null;
ResultSet res = null;
StringBuffer query=null;
String filename ="c:/backblue.gif";

public MySqlInsertBlob(){

try{

query=new StringBuffer("insert into blobs(filename,binarydata,name) values (?,?,?)");
File file= new File(filename);

Class.forName("com.mysql.jdbc.Driver")…
conn = DriverManager.getConnection("jdbc:mysql:…
stmt=conn.createStatement();
pstmt=conn.prepareStatement(query.toSt…
pstmt.setString(1, filename);
pstmt.setBinaryStream(2, new FileInputStream(filename),(int)file.leng…
pstmt.setString(3,"silviya");
pstmt.executeUpdate();
System.out.println("Successfully inserted into BLOBS .....");

ResultSet rs=stmt.executeQuery("select * from blobs where name='silviya'");
if (rs.next()) {
Blob test=rs.getBlob("binarydata");
InputStream x=test.getBinaryStream();
int size=x.available();
OutputStream out=new FileOutputStream("c:/xyz.gif");
byte b[]= new byte[size];
x.read(b);
out.write(b);


}
}catch(ClassNotFoundException cnfe){

System.out.println("ClassNotFoundExcep… occured :"+cnfe);
}catch(SQLException se){

System.out.println("SQLException occured :"+se);
}catch(FileNotFoundException fe){

System.out.println("File Not Found Exception occured :"+fe);
}catch(Exception e){

System.out.println("Exception occured :"+e);
}finally{
try{
stmt.close();
conn.close();
}catch(Exception e){}
}
Source(s):

You just posted quite a bit of code with no explanation and few comments in the code. Can you add a bit of explanation to your post?

Collectives™ on Stack Overflow

Can we fetch an added image from MySql database by using java?

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related