Insert InputStream into PostgreSQL

Question

I want to implement file upload into PostgreSQL. I tried this solution:

public static byte[] getBytesFromInputStream(InputStream is) throws IOException
    {
        try (ByteArrayOutputStream os = new ByteArrayOutputStream();)
        {
            byte[] buffer = new byte[0xFFFF];

            for (int len; (len = is.read(buffer)) != -1;)
                os.write(buffer, 0, len);

            os.flush();

            return os.toByteArray();
        }
    }

    public void upload() throws SQLException
    {
        if (file != null)
        {
            try
            {
                InputStream inputStream = file.getInputStream();

                byte[] bytesFromInputStream = getBytesFromInputStream(inputStream);
                ByteArrayInputStream input = new ByteArrayInputStream(bytesFromInputStream);


                long lastTimestamp = System.currentTimeMillis();
                int pushInterval = 1000;
                long totalRead = 0;
                long size = file.getSize();

                int bytesRead = 0;
                final byte[] chunck = new byte[1024];
                while ((bytesRead = inputStream.read(chunck)) != -1)
                {
//                    outputStream.write(chunck, 0, bytesRead);
                    totalRead += bytesRead;

                    if (System.currentTimeMillis() > lastTimestamp + pushInterval)
                    {
                        lastTimestamp = System.currentTimeMillis();
                        uploadProgress.send(100.0 * totalRead / size); // Make sure this isn't sent more often than once per second.
                    }
                }

                Connection conn = ds.getConnection();
                // insert into database file
                PreparedStatement ps = null;
                boolean committed = false;
                try
                {
                    conn.setAutoCommit(false);

                    ps = conn.prepareStatement("INSERT INTO PROCEDURE_FILES (ID, PROCEDURE_ID, FILE_NAME, FILE) "
                        + " VALUES (?, ?, ?, ?)");
                    ps.setInt(1, obj.number);
                    ps.setInt(2, obj.number);
                    ps.setString(3, file.getSubmittedFileName());
                    ps.setBinaryStream(4, input, input.available());

                    ps.executeUpdate();
                    ps.close();

                    conn.commit();
                    committed = true;
                }
                catch (SQLException e)
                {
                    e.printStackTrace();
                }
                finally
                {}
            }
            catch (IOException e)
            {}
        }
    }

but I get this error:

org.postgresql.util.PSQLException: ERROR: column "file" is of type oid but expression is of type bytea
Hint: You will need to rewrite or cast the expression.

How I can solve this problem?

can u have a look at this link - virgo47.wordpress.com/2008/06/13/… — Gandhi
– Gandhi, Commented May 4, 2016 at 8:23

Aurasphere · Accepted Answer · 2016-05-04 09:50:42Z

1

You will have to cast your InputStream to a Oid object before persisting it. To do so, simply create a new Oid passing the byte array you get from the IS:

 Oid oid = new Oid(IOUtils.readFully(inputStream, -1, false));

then you will pass the oid in the prepared statement.

Another solution, database-side, would be changing the column of the table to a BLOB type.

edited May 4, 2016 at 9:50

answered May 4, 2016 at 7:51

Aurasphere

4,03912 gold badges53 silver badges80 bronze badges

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

19 Comments

Peter Penzov Over a year ago

Probably you mean that I need to cast inputStream?

Peter Penzov Over a year ago

One general question . What will happen if I upload 2 GB file on a server with 1 GB RAM?

Aurasphere Over a year ago

You will get an OutOfMemoryError. The same would happen with the InputStream though.

Peter Penzov Over a year ago

Is there any solution? In general maybe the solution will be to configurure 10 GB SWAP memory on the server or I can first upload the file on a tmp firecotry and then upload it in PostgreSQL?

Aurasphere Over a year ago

The solution will depend on whar are your needs. You could swap the memory as you said or if you are developing an application that will be have different users you could implement a check on the file size and give back an error if it is too big.

|

Collectives™ on Stack Overflow

Insert InputStream into PostgreSQL

1 Answer 1

19 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

19 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related