PostgreSQL + Hibernate 关于 Bytea 和 oid 的映射问题

半只蚂蚁

　　问题起始,
　　1. 我在pojo里面定义了一个 binaryData，想通过Blob类型映射，

public Blob getBinaryData() {
return _binaryData;
}
public void setBinaryData(Blob binaryData) {
this._binaryData = binaryData;
}
　　2. Hibernate 配置文件片段

     <property name="_binaryData" access="field" type="blob" not-null="true">
<column name="BINARYDATA" not-null="true" />
</property>
　　3. Database 对应的Column 为 bytea 
　　4.Java 代码 

     InputStream stream = getClass().getResourceAsStream("CrissAngel.jpg");
picStorage1.setBinaryData(Hibernate.createBlob(stream));
_picStorageDAO.save( picStorage1 );
　　4. 报错
　　ERROR JDBCExceptionReporter - ERROR: column "binarydata" is of type bytea but expression is of type bigint
Hint: You will need to rewrite or cast the expression.
Position: 42
27 Jul 2010 17:12:13,828 ERROR AbstractFlushingEventListener - Could not synchronize database state with session
　　问题追溯,
　　1. PostgreSQL 用两种类型 bytea/oid 来 处理binary stream
　　http://jdbc.postgresql.org/documentation/80/binary-data.html
　　2. Hibernate BlobType 默认行为
转载自 http://blog.toadhead.net/index.php/2005/02/12/postgresql-blobs-with-hibernate/
　　My test JDBC code looks something like:

PreparedStatement stmt = conn.prepareStatement("INSERT INTO foo (data) VALUES (?)");
stmt.setBlob(1, Hibernate.createBlob(myInputStream));
stmt.executeUpdate();
　　The above example works fine. The Postgresql JDBC driver accepts any object that implements javax.sql.Blob.
　　I therefore assumed that the following code would work:

HibernateObject o = new HibernateObject();
o.setBlob(Hibernate.createBlob(myInputStream));
hibernate.save(o);
　　Unfortunately this doesn’t work. The problem lies in net.sf.hibernate.type.BlobType. Hibernate contains the following logic:

public void set(PreparedStatement st, Object value, int index) throws HibernateException, SQLException {

  
final boolean useInputStream = session.getFactory().getDialect().useInputStreamToInsertBlob()
    && BlobImplementer.class.isInstance( blob );
if (value instanceof BlobImpl) {
BlobImpl blob = (BlobImpl) value;
st.setBinaryStream( index, blob.getBinaryStream(), (int) blob.length() );
}
else {
st.setBlob(index, (Blob) value);
}
}
　　The problem with the above code is that Postgresql uses setBinaryStream for byte array (bytea) fields. Hibernate incorrectly assumes that all databases can use setBinaryStream on blob fields.
　　Steve Lustbader filed a bug HB-955 regarding this issue that has, unfortunately, been rejected. Fortunately, there is a fairly simple workaround. net.sf.hibernate.lob.BlobImpl is the Hibernate class that implements javax.sql.Blob. I copied the implementation of this class and put it in my own class. I then use this class to the set the blob field in my Hibernate object. Hibernate than passes this object onto the JDBC driver and everything works well.
　　I understand the logic behind what is happening in Hibernate. They are trying to prevent problems with JDBC drivers that cast the blob field to their own implementation of javax.sql.Blob. I’m of the opinion that any JDBC driver that does this is broken. Any JDBC driver that does not accept any implementation of javax.sql.Blob is in my opinion broken. The assumption that all blobs can be set using setBinaryStream() is also incorrect, in my opinion.
　　问题原因
　　1. Postgres 保存 bytea 的 JDBC代码片段

For example, suppose you have a table containing the file names of images and you also want to store the image in a bytea  column:
CREATE TABLE images (imgname text, img bytea);
To insert an image, you would use:
File file = new File("myimage.gif");
FileInputStream fis = new FileInputStream(file);
PreparedStatement ps = conn.prepareStatement("INSERT INTO images VALUES (?, ?)");
ps.setString(1, file.getName());
ps.setBinaryStream(2, fis, (int)file.length());
ps.executeUpdate();
ps.close();
fis.close();

　　由此可见，bytea 必须执行 ps.setBinaryStream 方法.
　　但是 Hibernate 默认的行为时执行   st.setBlob(index, (Blob) value); 因为 session.getFactory().getDialect().useInputStreamToInsertBlob() 永远都返回 FALSE.

解决办法

　　方法1 - 改成 byte[] 类型  
　　由于 bytea 直接支持 byte[] 可以用 ps.setBytes() 直接赋值.
    所以可以作如下修改

public byte[] getBinaryData() {
return _binaryData;
}
public void setBinaryData(byte[] binaryData) {
this._binaryData = binaryData;
}
　　配置文件，  

        <property name="_binaryData" access="field" type="byte[]" not-null="true">
<column name="BINARYDATA" not-null="true" />
</property>
　　测试代码,

  InputStream stream = getClass().getResourceAsStream("CrissAngel.jpg");
byte[] input = new byte[stream.available()];
stream.read(input);
picStorage1.setBinaryData(input);
_picStorageDAO.save( picStorage1 ); 
　　
   这样做可以 通过Hibernate 保存和读取 bytea 类型，但是 必须将所有的 binary data 读入到内存，如果在并发环境下很容易导致 OutOfMemory 的问题. 还有可移植性太差，以后如果换成MySQL数据库，那么所有的Byte[] 对应的bytea Blob类型需要全部改回成BLOB，包括所有的实现方法.
　　方法二，撰写自己的 PostgreSQLDialect

public class MyPostgreSQLDialect extends PostgreSQLDialect {
@Override
public boolean useInputStreamToInsertBlob() {
// TODO Auto-generated method stub
return true;
}
}
　　目的是让 BlobType 执行 ps.setBinaryStream(... 方法来保存Blob类型.  (注，如果要用 oid 类型，此法不可取)
　　配置文件

<prop key="hibernate.dialect">
com.haodao.dialect.MyPostgreSQLDialect
</prop>
　　这样，无需改动原有配置，程序正常执行，也保留了 stream 的特性，易于移植同事也不会带来高并发下OutOfMemory的问题