Java Postgresql、JDBC 和流式 BLOB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2069541/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql, JDBC, and streaming BLOBs
提问by CJ F
I am trying to retrieve a blob from a postgres database using the jdbc drivers. It is too big to have in memory so I want to stream it as a download. I tried using the getBinaryStream method on ResultSet, but it turns out that this method actually reads it all into memory, so doesn't work for large file.
我正在尝试使用 jdbc 驱动程序从 postgres 数据库中检索 blob。它在内存中太大了，所以我想将它作为下载进行流式传输。我尝试在 ResultSet 上使用 getBinaryStream 方法，但事实证明该方法实际上将其全部读入内存，因此不适用于大文件。
Apparently, one can use the getBlob method on the resultset and the presumeably get the inputstream from the blob and go from there, but that is where I run into my problem.
显然，可以在结果集上使用 getBlob 方法，并且可能从 blob 获取输入流并从那里开始，但这就是我遇到问题的地方。
PreparedStatement ps = con.prepareStatement("select data from file_data WHERE ID = ?");
ps.setLong(1,file.fileData.id)
ResultSet rs = ps.executeQuery()
if(rs.next()){
rs.getBlob("data")
That is the code I'm running. When it gets to that last line it throw out an error that I cannot make sense of...
那是我正在运行的代码。当它到达最后一行时，它会抛出一个我无法理解的错误......
org.postgresql.util.PSQLException: Bad value for type long : xxxxxx
org.postgresql.util.PSQLException：long 类型的错误值：xxxxxx
"xxxxxx" then is the contents of the file. You can imagine that gets quite long, but not really the point.
“xxxxxx”则是文件的内容。你可以想象这会很长，但并不是重点。
I'm stuck here. Does anyone have any ideas on what is going on? Heck I'll even take alternative methods for streaming large blobs as a download.
我被困在这里。有没有人对正在发生的事情有任何想法？哎呀，我什至会采用其他方法来流式传输大型 blob 作为下载。
采纳答案by janko
My guess is, that you have mixed up OID and BYTEA style blobs. Large binary objects are stored indirecty with OID columns in Postgres. The actual file data is stored somewhere outside the database table by Postgres. The column just contains an object identifier that is associated internally with the blob. For instance:
我的猜测是，您混淆了 OID 和 BYTEA 风格的 blob。大型二进制对象与 Postgres 中的 OID 列间接存储。Postgres 将实际的文件数据存储在数据库表之外的某处。该列仅包含与 blob 内部关联的对象标识符。例如：
janko=# CREATE TABLE blobtest1 (name CHAR(30), image OID);
CREATE TABLE
janko=# INSERT INTO blobtest1 VALUES ('stackoverflow', lo_import('/tmp/stackoverflow-logo.png'));
INSERT 0 1
janko=# SELECT * FROM blobtest1;
name | image
--------------------------------+-------
stackoverflow | 16389
(1 row)
If you use the ResultSet#getBlob(String)method, than an OID style column is expected. getBlobreads the data from the column and converts it to a Long. Then it tries to read the associated binary data from its internal storage.
如果您使用该ResultSet#getBlob(String)方法，则预期会出现 OID 样式列。getBlob从列中读取数据并将其转换为Long. 然后它尝试从其内部存储中读取相关的二进制数据。
On the other hand, with BYTEA you can place small pieces of binary data directly in your DB. For instance:
另一方面，使用 BYTEA，您可以将小块二进制数据直接放入数据库中。例如：
janko=# CREATE TABLE blobtest2 (name CHAR(30), image BYTEA);
CREATE TABLE
janko=# INSERT INTO blobtest2 VALUES ('somebinary', E'\336\255\276\357\336\255\276\357');
INSERT 0 1
janko=# SELECT * FROM blobtest2;
name | image
--------------------------------+----------------------------------
somebinary | 65676567
(1 row)
Here, the data column directly contains the binary data. If you try to use getBlobon such a column, the data will still be interpreted as an OID but obviously it will not fit into a Long. Let's try this on the database, we just created:
这里，数据列直接包含二进制数据。如果您尝试getBlob在这样的列上使用，数据仍将被解释为 OID，但显然它不适合Long. 让我们在刚刚创建的数据库上试试这个：
groovy:000> import java.sql.*
===> [import java.sql.*]
groovy:000> Class.forName("org.postgresql.Driver");
===> class org.postgresql.Driver
groovy:000> db = DriverManager.getConnection("jdbc:postgresql:janko", "janko", "qwertz");
===> org.postgresql.jdbc4.Jdbc4Connection@3a0b2c64
groovy:000> ps = db.prepareStatement("SELECT image FROM blobtest2 WHERE name = ?");
===> SELECT image FROM blobtest2 WHERE name = ?
groovy:000> ps.setString(1, "somebinary")
===> null
groovy:000> rs = ps.executeQuery()
===> org.postgresql.jdbc4.Jdbc4ResultSet@66f9104a
groovy:000> rs.next()
===> true
groovy:000> rs.getBlob("image")
ERROR org.postgresql.util.PSQLException: Bad value for type long : 65676567
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toLong (AbstractJdbc2ResultSet.java:2796)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong (AbstractJdbc2ResultSet.java:2019)
at org.postgresql.jdbc4.Jdbc4ResultSet.getBlob (Jdbc4ResultSet.java:52)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getBlob (AbstractJdbc2ResultSet.java:335)
at groovysh_evaluate.run (groovysh_evaluate:3)
...
回答by obscuredlogic
Would the PostgreSQL docs for "Storing Binary Data" help?
“存储二进制数据”的 PostgreSQL 文档有帮助吗？
http://jdbc.postgresql.org/documentation/head/binary-data.html
http://jdbc.postgresql.org/documentation/head/binary-data.html
The section titled "Retrieving the image from the Large Object", its at the bottom of the page, might help.
位于页面底部的标题为“从大对象中检索图像”的部分可能会有所帮助。
