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

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.

2 Answers

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.

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