在java中正确使用PostgreSQL大对象和字节流(bytea)类型的姿势

背景

在PostgreSQL中大对象和字节流是两种数据类型，使用时需要注意。

正文

有人在java中使用getBlob获取所谓的”大对象”失败，问题是用法不对。

groovy:000> rs.getBlob("image")  
ERROR org.postgresql.util.PSQLException: Bad value for type long : \336\255\276\357\336\255\276\357  
        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)  
        ...  

注意PostgreSQL和Oracle不一样，大对象存储在pg_largeobject中，在用户的数据字段中存储的是OID（即引用），不是字节流。

如果你需要存储字节流，可以使用bytea类型来存储。

bytea和large object属于两种类型，这两种类型的获取接口当然也不一样。

stackoverflow中的例子

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:

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. getBlob reads the data from the column and converts it to a Long.

Then it tries to read the associated binary data from its internal storage.

On the other hand, with BYTEA you can place small pieces of binary data directly in your DB. For instance:

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                     | \336\255\276\357\336\255\276\357  
(1 row)  

Here, the data column directly contains the binary data.

If you try to use getBlob on 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:

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 : \336\255\276\357\336\255\276\357  
        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)  
        ...  

jdbc文档中也明确的指出了用法

To use the BYTEA data type you should simply use the getBytes(), setBytes(), getBinaryStream(), or setBinaryStream() methods.

To use the Large Object functionality you can use either the LargeObject class provided by the PostgreSQL? JDBC driver, or by using the getBLOB() and setBLOB() methods.

https://jdbc.postgresql.org/documentation/94/binary-data.html

digoal’s 大量PostgreSQL文章入口