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](https://jdbc.postgresql.org/documentation/80/binary-data.html) has example code that might work for you. Basically, adapted to your example code it's using `pstm.setBinaryStream(is, theLengthOfTheFile)`. — Mick Mnemonic, Jun 20 '18 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, Jun 20 '18 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, Jun 20 '18 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, Jun 20 '18 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, Jun 20 '18 at 23:10

Mick Mnemonic · Accepted Answer · 2018-06-20T23:15:57.847

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 · Answer 2 · 2018-06-21T05:08:28.627

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 '18 at 05:08

answered Jun 20 '18 at 21:47

Asator

31
8

ByteStreams cannot be resolved, and i cannot find a quick way to resolve this. – Rollos Jun 20 '18 at 22:03
This is probably referring to [`ByteStreams`](https://google.github.io/guava/releases/19.0/api/docs/com/google/common/io/ByteStreams.html) of Guava, which requires you to import and include said library. – Mick Mnemonic Jun 20 '18 at 22:17
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. – Asator Jun 21 '18 at 05:07

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

2 Answers2