Inserting/Updating a blob into MySQL from Java

Question

After a day of googling I have finally decided its time to ask the all powerful SO community. I am trying to insert or update an image into a MySQL database. The query and code I am using is as follows:

    FileInputStream inputStream = new FileInputStream(file);

    String[] str_array = file.getName().split("-");
    String stringb = str_array[1];
    String stringc = str_array[2];
    String fingerName = stringc.substring(0, 2);
    //gets file name and splits it accordingly

    String id = getID.id(stringb); //does a sql lookup to get the previously inserted id according to the stringb(users unique id number)

    String INSERT_PIC = "INSERT INTO database.user_picture(id_ref, picture_num, user_image) values('" + id + "', ?, ?) ON DUPLICATE KEY UPDATE user_image = ?;";

    //creates the sql statement that inserts or updates according to the primary keys id_ref and picture_num

    ps = (PreparedStatement) connection.prepareStatement(INSERT_PIC);

    ps.setString(1, fingerName);
    ps.setBinaryStream(2, (InputStream) inputStream, file.length());
    ps.setBinaryStream(3, (InputStream) inputStream, file.length());

    //creates the prepared statement and inserts the 3 parameters

    ps.executeUpdate();
    connection.commit();
    //executes the query on the connected database

I was quite sure this would work. When tested it will insert the image into the database correctly. When updating all of the fields are updated correctly except for the blob/image field which is instead changed to NULL.

I'm not sure why this is happening or of any other way to get this to work, Any suggestions would be appreciated...

Hendrik · Accepted Answer · 2013-11-25 14:44:31Z

1

You are providing the same input stream for both parameter 2 and 3. When the statement is executed, first the parameter 2 stream is read until the end. When JDBC driver then attempts to read the stream for parameter 3, it is already at its end, because it is the same stream instance.

Try to provide two input streams:

FileInputStream inputStream1 = new FileInputStream(file);
FileInputStream inputStream2 = new FileInputStream(file);
[...]
ps.setBinaryStream(2, (InputStream) inputStream1, file.length());
ps.setBinaryStream(3, (InputStream) inputStream2, file.length());

Also, don't forget to close the streams in some finally block.

Good luck.

answered Nov 25, 2013 at 14:44

Hendrik

5,33029 silver badges61 bronze badges

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

2 Comments

DeanMWake Over a year ago

Will reading the same file at the same time by two different input streams cause an exception? Logically I could think this could cause something similar to a thread deadlock

Hendrik Over a year ago

It shouldn't cause any problems, as long as they both are only reading.

user3012345 · Accepted Answer · 2013-11-25 14:42:06Z

1

My guess is the first setBinaryStream() consumes the stream. So the second call to setBinaryStream() simply reads "null".

answered Nov 25, 2013 at 14:42

user3012345

251 bronze badge

Collectives™ on Stack Overflow

Inserting/Updating a blob into MySQL from Java

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related