Inserting blob data in Java using PreparedStatement

Question

I am using the following code to insert an image in a database. It will store two images because I have used PreparedStatement and Statement.

When I run this code, I get two images in the database. But the two images are different, and I don't understand why. Using PreparedStatement, it is inserting perfectly. I want to have the same image when I use Statement. Why is it not working now, and how can I make it work?

import java.io.*;
import java.sql.*;
public class Image
{
    public static void main(String args[]) throws Exception
    {
        System.out.println("kshitij");
        Class.forName("com.mysql.jdbc.Driver");
        Connection cn=DriverManager.getConnection("jdbc:mysql://localhost:3306/jsfdb","root","kshitij");
        Statement st=cn.createStatement();
        File f1=new File("c:\\k1.jpg");
        FileInputStream fin=new FileInputStream(f1);
        //DataInputStream dataIs = new DataInputStream(new FileInputStream(f1));
        PreparedStatement pst = cn.prepareStatement("insert into registration(image) values(?)");
        //pst.setInt(1,67);
        pst.setBinaryStream(1,fin,fin.available());
        pst.executeUpdate();
        //int length=(int)f1.length();
        byte [] b1=new byte[(int)f1.length()];
        fin.read(b1);
        fin.close();
        st.executeUpdate("insert into registration(image) values('"+b1+"')");
        System.out.println("Quesry Executed Successfully");
        FileOutputStream fout=new FileOutputStream("d://k1.jpg");
        fout.write(b1);
        fout.close();
    }
}

MySQL

CREATE DATABASE IF NOT EXISTS jsfdb;
USE jsfdb;

-- Definition of table `registration`
DROP TABLE IF EXISTS `registration`;
CREATE TABLE `registration` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `image` blob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=234 DEFAULT CHARSET=latin1;

"please run this code and script you will find two images in database table. but both are different i dont know why." - sorry, not worth the effort. You ought to be doing more work here. — duffymo
– duffymo, Commented Feb 24, 2012 at 11:32
Please indent your code properly and use proper capitalization in your sentences. — Felix Kling
– Felix Kling, Commented Feb 24, 2012 at 11:34
People won't generally run code supplied on this site - too risky. But they will look over your code for you. Do you expect the two images to be the same? — halfer
– halfer, Commented Feb 24, 2012 at 11:37
PS: welcome to Stack Overflow! Btw, I agree with Felix - try to use meaningful titles and make sentences as case and grammar correct as you can (though I try to make exceptions for non-English speakers). Putting effort into questions will encourage people to reply, generally speaking. — halfer
– halfer, Commented Feb 24, 2012 at 11:41

RenRen · Accepted Answer · 2013-11-26 14:45:04Z

13

Use setBlob with InputStream

File file= new File("your_path");
FileInputStream inputStream= new FileInputStream(file);

PreparedStatement statement = connection.prepareStatement("INSERT INTO yourTable (yourBlob) VALUES (?)");
statement.setBlob(1, inputStream);

answered Nov 26, 2013 at 14:45

RenRen

11.5k4 gold badges43 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JB Nizet · Accepted Answer · 2012-02-24 11:45:41Z

11

Of course they will be different. The following query does the following thing:

"insert into registration(image) values('"+b1+"')"

Take b1, which is a byte array, and call its toString() method. This results in a String like [B@8976876, which means "an object of type byte array with hashCode 8976876", but doesn't represent the contents of the byte array at all. Then insert this string in the table.

A byte array is not a String. End of story. You must use a prepared statement to insert binary data in a table. In fact, you should always use a prepared statement to execute any query that has a non-constant parameter.

edited Feb 24, 2012 at 11:45

answered Feb 24, 2012 at 11:36

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

2 Comments

JB Nizet Over a year ago

How is that "not true"? In your answer, you transform the binary data into a String. So you're not inserting binary data anymore, but a String instead. By doing that, you loose time (to encode and decode), and space (because a base64 string needs 4 bytes to represent 3 binary bytes).

JB Nizet Over a year ago

To insert binary data, yes, it's true. And prepared statements are a good practice in general anyway.

Community · Accepted Answer · 2017-05-23 12:34:17Z

2

Your problem is that you are concatenating a string with a byte array (in your call to executeStatment)

See this answer on how to insert a blob using a statement: https://stackoverflow.com/a/2609614/355499

edited May 23, 2017 at 12:34

CommunityBot

11 silver badge

answered Feb 24, 2012 at 11:53

Emil L

21.2k3 gold badges48 silver badges71 bronze badges

Collectives™ on Stack Overflow

Inserting blob data in Java using PreparedStatement

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related