Hibernate lob字段读取失败解决

Posted by Simon Dong on 2014-09-07

Oracle

Oracle读取Lob时,需要在Hibernate属性中设置

1
<prop key="hibernate.jdbc.use_streams_for_binary">true</prop>

Oracle版本 驱动版本 注解方式 效果
11g 11g @Column(name=”content”,columnDefinition=”CLOB”) 创建、读写正常
10g 10g @Column(name=”content”,columnDefinition=”CLOB”) 创建、读写正常
9i 9i 在10g基础上添加 @Lob 或 @Type(type=”text”) 或者使用 org.springframework.jdbc.support.lob.OracleLobHandler 创建、读写正常,若不加,会出现读取数据为空的情形
9i 10g @Column(name=”content”,columnDefinition=”CLOB”) 创建、读写正常

使用OracleLobHandler处理
在sessionFactory的配置中加入

1
2
3
4
5
6
7
8
9
10
11
12
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">

<property name="lobHandler" ref="lobHandler"/>

</bean>
<bean id="lobHandler" lazy-init="true" class="org.springframework.jdbc.support.lob.OracleLobHandler">
<property name="nativeJdbcExtractor">
<ref bean="nativeJdbcExtractor"/>
</property>
</bean>

<bean id="nativeJdbcExtractor" lazy-init="true" class="org.springframework.jdbc.support.nativejdbc.SimpleNativeJdbcExtractor"/>

PostgreSQL

在PostgreSQL读取Clob或Blob字段时,会出现

1
2
3
4
5
6
7
8
9
10
Caused by: org.postgresql.util.PSQLException: Large Objects may not be used in auto-commit mode.
at org.postgresql.largeobject.LargeObjectManager.createLO(LargeObjectManager.java:241)
at org.postgresql.largeobject.LargeObjectManager.createLO(LargeObjectManager.java:228)
at org.postgresql.jdbc2.AbstractJdbc2Statement.setBlob(AbstractJdbc2Statement.java:2817)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setBlob(DelegatingPreparedStatement.java:180)
at org.hibernate.type.BlobType.set(BlobType.java:49)
at org.hibernate.type.BlobType.nullSafeSet(BlobType.java:117)
at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:1997)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2243)
... 75 more

解决方法

1
2
3
4
5
6
7
8
9
@Lob
@Basic(fetch=FetchType.LAZY)
@Type(type = "org.hibernate.type.TextType")
private String description;

@Lob
@Basic(fetch=FetchType.LAZY)
@Type(type = "org.hibernate.type.BinaryType")
private byte[] photos;