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 - https://virgo47.wordpress.com/2008/06/13/jpa-postgresql-and-bytea-vs-oid-type/ — Gandhi, May 04 '16 at 08:23

Aurasphere · Accepted Answer · 2016-05-04T09:50:42.640

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 04 '16 at 09:50

answered May 04 '16 at 07:51

Aurasphere

3,841
12
44
71

Probably you mean that I need to cast `inputStream`? – Peter Penzov May 04 '16 at 07:59
One general question . What will happen if I upload 2 GB file on a server with 1 GB RAM? – Peter Penzov May 04 '16 at 08:05
You will get an OutOfMemoryError. The same would happen with the InputStream though. – Aurasphere May 04 '16 at 08:11
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? – Peter Penzov May 04 '16 at 08:15
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. – Aurasphere May 04 '16 at 08:17
I tried to cast the inputStream Object using Oid oid = new Oid(); But there is no siutable constructor, Can you give some code example how to cast it? – Peter Penzov May 04 '16 at 09:41
Sorry but I don't undestand where do we get the variable is. Can you extend the example little more? – Peter Penzov May 04 '16 at 09:49
IS is your inputStream. I've fixed it. Tell me if you have any more doubts. – Aurasphere May 04 '16 at 09:51
I imported import sun.misc.IOUtils; and I get constructor Oid in class Oid cannot be applied to given types; required: no arguments found: byte[] reason: actual and formal argument lists differ in length IOUtils is internal proprietary API and may be removed in a future release – Peter Penzov May 04 '16 at 09:55
Which version of Java are you using? Also, if you are using Apache Commons you can get a byte[] like this: byte[] bytes = IOUtils.toByteArray(is); – Aurasphere May 04 '16 at 10:02
If thre is no solution with core Jaca we can try to use Apache Commons. – Peter Penzov May 04 '16 at 10:08
You for sure can do it with core Java. Here are some different solutions: http://stackoverflow.com/questions/1264709/convert-inputstream-to-byte-array-in-java – Aurasphere May 04 '16 at 10:23
See the orignal post. I already tried. Can you propose some other solution? – Peter Penzov May 04 '16 at 10:28
Sorry, I didn't see that. If you have a method that converts an InputStream to a byte[] why are you not using that? Oid oid = new Oid(getBytesFromInputStream(inputStream)); – Aurasphere May 04 '16 at 10:31
I'm a junior developer. This is something new to me and I don't know how to implement it properly. – Peter Penzov May 04 '16 at 10:35
P. I get constructor Oid in class Oid cannot be applied to given types; required: no arguments found: byte[] reason: actual and formal argument lists differ in length – Peter Penzov May 04 '16 at 10:38
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/110984/discussion-between-aurasphere-and-peter-penzov). – Aurasphere May 04 '16 at 10:42
Thanks. It's working fine. By the way is there a way to prevent OutOfMemoryErrors? – Peter Penzov May 04 '16 at 21:24
Glad I could help you. I think to avoid that you should try to stream data to DB. Unfortunately, my knoledge of Postgre APIs is very limited but if you are willingly to add Hibernate to manage your database, here are some hints on how to do that: http://stackoverflow.com/questions/11926487/how-to-stream-data-to-database-blob-using-hibernate-no-in-memory-storing-in-byt – Aurasphere May 05 '16 at 07:06

Insert InputStream into PostgreSQL

1 Answers1