公司更改项目框架时,处理CLOB字段曾经出过一个问题。今天抽时间整理个大概出来。以下是老框架的配置。
Ibatis配置文件:
<sqlMapConfig>
<settings cacheModelsEnabled="true" enhancementEnabled="true"
lazyLoadingEnabled="false" maxRequests="6000" maxSessions="5000"
maxTransactions="2000" useStatementNamespaces="true" />
<typeHandler jdbcType="BLOB" javaType="[B"
callback="org.springframework.orm.ibatis.support.BlobByteArrayTypeHandler" />
<typeHandler jdbcType="CLOB" javaType="java.lang.String"
callback="org.springframework.orm.ibatis.support.ClobStringTypeHandler" />
<typeHandler jdbcType="DATETIME" javaType="java.util.Date"
callback="com.**.ibatis.patch.typehandler.DateTimeTypeHandlerCallback" />
<!-- generate start -->
maps_xml_generate
<!-- generate end -->
</sqlMapConfig>
map映射文件中:
<resultMap id="abatorgenerated_PayOrderInfoResultWithBLOBs" class="com.**.entity.PayOrderInfo" extends="PAY_ORDER_INFO.abatorgenerated_PayOrderInfoResult" >
<!--
WARNING - This element is automatically generated by Abator for iBATIS, do not modify.
-->
<result column="REQUEST_DATA" property="requestData" jdbcType="CLOB" />
</resultMap>
spring配置文件:
<!-- SqlMap setup for iBATIS Database Layer -->
<bean id="sqlMapClient"
class="com.**.ibatis.patch.spring.SqlMapClientFactoryBean">
<property name="configLocations">
<list>
<value>classpath:/META-INF/sql-map-config.xml</value>
<value>classpath:/META-INF/sql-map-config-front.xml</value>
</list>
</property>
<property name="dataSource" ref="dataSource" />
<property name="lobHandler" ref="lobHandler"></property>
<property name="sqlExecutor" ref="sqlExecutor"></property>
</bean>
<!--Blob field hander for Oracle9i or Oracle10g -->
<bean id="nativeJdbcExtractor"
class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"
lazy-init="true" />
<bean id="lobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler"
lazy-init="true">
<property name="nativeJdbcExtractor" ref="nativeJdbcExtractor" />
</bean>
看过一些介绍,说的是OracleLobHandler能处理oracle9i及其以上版本的CLOB字段。但是在新框架中同样的配置,查询时没有问题,插入CLOB字段时却失败,这里由于是很久前出的问题,没有错误日志可贴。后来解决办法如下:
<!--Blob field hander for Oracle9i or Oracle10g -->
<bean id="nativeJdbcExtractor"
class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"
lazy-init="true" />
<bean id="lobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler"
lazy-init="true">
<!--这里去掉了nativeJdbcExtractor转换器-->
<!-- <property name="nativeJdbcExtractor" ref="nativeJdbcExtractor" /> -->
</bean>
这样就解决了blob和clob字段的插入问题。但是对于为什么不能用OracleLobHandler并没有找到原因。最后总结可能是Spring版本问题(新框架版本为4.0.3,老框架2.5.5),但是目前为止没有找到相关说明。这里注意必须在事务下操作clob字段。原因不明。。
springAPI文档中DefaultLobHandler介绍为:This does not apply to Oracle 9i, and only to a limited degree to Oracle 10g!
OracleLobHandler介绍为:Note that this LobHandler requires Oracle JDBC driver 9i or higher!
Spring内置提供的NativeJdbcExtractor转换器有:
C3P0NativeJdbcExtractor
CommonsDbcpNativeJdbcExtractor
JBossNativeJdbcExtractor
NativeJdbcExtractor
NativeJdbcExtractorAdapter
SimpleNativeJdbcExtractor
WebLogicNativeJdbcExtractor
WebSphereNativeJdbcExtractor
XAPoolNativeJdbcExtractor