java - 将输入流插入 PostgreSQL

Question

我想实现文件上传到PostgreSQL。我尝试了这个解决方案:

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)
            {}
        }
    }

但我收到此错误:

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.

我该如何解决这个问题？

Answer 1

在持久化之前，您必须将 InputStream 转换为 Oid 对象。为此，只需创建一个新的 Oid，传递从 IS 获得的字节数组:

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

然后您将在准备好的语句中传递 oid。

数据库端的另一个解决方案是将表的列更改为 BLOB 类型。