Issue about using PreparedStatement.setBlob on a FileInputStream to a postgres database with Bytea type

Question

As the title suggests, im trying to upload a file straight to my postgresql database to the data type Bytea with the .setBlob pstm. However JBDC doesnt like it and gives me the following error:

java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc4.Jdbc4PreparedStatement.setBlob(int, InputStream) is not yet implemented.
at org.postgresql.Driver.notImplemented(Driver.java:727)

Ive tried the .setBytes() method but im unsure how to use it correctly with the available data.

Here is the code im using for this:

private void writeToDB(Connection conn, String fileName, InputStream is, String description) throws SQLException {

    String sql = "Insert into Attachment(Id,File_Name,File_Data,Description) " //
            + " values (?,?,?,?) ";
    PreparedStatement pstm = conn.prepareStatement(sql);

    Long id = this.getMaxAttachmentId(conn) + 1;
    pstm.setLong(1, id);
    pstm.setString(2, fileName);
    pstm.setBlob(3, is);
    pstm.setString(4, description);
    pstm.executeUpdate();
}

Please let me know if there is anything i can do to improve this post, im new to using stackoverflow.

The documentation page on storing binary data has example code that might work for you. Basically, adapted to your example code it's using pstm.setBinaryStream(is, theLengthOfTheFile). — Mick Mnemonic
– Mick Mnemonic, Commented Jun 20, 2018 at 21:50
@MickMnemonic I tried this but got the following error: ERROR: column "file_data" is of type bit but expression is of type bytea Hint: You will need to rewrite or cast the expression. — Rollos
– Rollos, Commented Jun 20, 2018 at 21:54
So, Attachment.file_data isn't actually of type bytea but instead it's a bit. Sounds like your table definition for Attachment is wrong, but we can't know for sure because you haven't disclosed it. — Mick Mnemonic
– Mick Mnemonic, Commented Jun 20, 2018 at 22:14
@MickMnemonic I'm really sorry, i had forgotten i remade the table earlier to try another method! Your answer is the solution, now i only need to make it so that i can automatically get the length of the input file. Also, how do i give you credit for giving the solution? I only see that option in actual replies not in comments. — Rollos
– Rollos, Commented Jun 20, 2018 at 22:45
No worries. I think you can actually omit the third argument (file length) of setBinaryStream(). This will just read the stream until end-of-file is reached. — Mick Mnemonic
– Mick Mnemonic, Commented Jun 20, 2018 at 23:10

Mick Mnemonic · Accepted Answer · 2018-06-20 23:15:57Z

Per the PostgreSQL tutorial on storing binary data, you can use pstm.setBinaryStream() for streaming binary data to the DB. With your example code this would work as follows:

private void writeToDB(Connection conn, String fileName, InputStream is, String description) 
    throws SQLException {

    String sql = "Insert into Attachment(Id,File_Name,File_Data,Description) "
            + " values (?,?,?,?)";

    try (PreparedStatement pstm = conn.prepareStatement(sql)) {

        Long id = this.getMaxAttachmentId(conn) + 1;
        pstm.setLong(1, id);
        pstm.setString(2, fileName);
        pstm.setBinaryStream(3, is);
        pstm.setString(4, description);
        pstm.executeUpdate();
   }
}

Note that I've wrapped the PreparedStatement within a try-with-resources statement, which is the preferred way since Java 7 because it makes sure that JDBC resources are closed properly even if an exception occurs.

If the PostgreSQL JDBC driver doesn't accept pstm.setBinaryStream() without an explicit length parameter, you need to pass the length() of the File object you used for creating the input stream to the method. An another approach is to read the stream fully into a byte array within the method and use

pstm.setBytes(3, myByteArray);

instead.

Asator · Accepted Answer · 2018-06-21 05:08:28Z

0

What works for me (Java 8 and PG 9.4) is converting the InputStream to a bytearray.

import com.google.common.io.ByteStreams;

// prepare your statement
byte [] binaries =  null;
try {
     binaries = ByteStreams.toByteArray(inputStream);
} catch (Exception e) {
      ...
}
// bind your parameters

edited Jun 21, 2018 at 5:08

answered Jun 20, 2018 at 21:47

Asator

318 bronze badges

3 Comments

Rollos Over a year ago

ByteStreams cannot be resolved, and i cannot find a quick way to resolve this.

Mick Mnemonic Over a year ago

This is probably referring to ByteStreams of Guava, which requires you to import and include said library.

Asator Over a year ago

ah, sorry, I thought ByteStreams came directly from java as well, but the import statement says: import com.google.common.io.ByteStreams; I'll adapt my answer.

Collectives™ on Stack Overflow

Issue about using PreparedStatement.setBlob on a FileInputStream to a postgres database with Bytea type

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related