Insert BLOB data in Java using PreparedStatement

Question

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

When I run this code, I get two images in the database. But both images are different, and I do not understand why. Using PreparedStatement , it inserts 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; 

Answer 1

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

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

Take b1, which is a byte array, and call its toString() method. The result is a string similar to [B @ 8976876, which means "an object of type a byte array with hashCode 8976876", but does not represent the contents of the byte array at all. Then insert this row into the table.

A byte array is not a string. The end of the story. You must use a prepared statement to insert binary data into the table. In fact, you should always use a prepared statement to execute any query with a variable parameter.

Answer 2

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

Answer 3

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

See this answer on how to insert blob using instructions: fooobar.com/questions/213586 / ...