SI中的LOB问题

      继博文IBATIS存活空间续--SI（SpringIbatis）实现之后，本博文给出SI（SpringIbatis）实现中的LOB问题，尤其是CLOB，根据相关资料，简单地给出两种解决方案！

 

问题一：SI中的LOB问题
SI中的LOB配置在com/si/dao/core/lob.xml中，采用默认的处理器，可按需修改。lob.xml中内容如下：

<!-- 默认的DefaultLobHandler -->
<bean id="lobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler" lazy-init="true" />
<!-- 特定的OracleLobHandler-->
<!-- 以c3p0数据库连接池为例，其它类型的数据库连接池仅需更换相应的jdbcExtractor类即可 -->
<!-- 
<bean id="jdbcExtractor" class="org.springframework.jdbc.support.nativejdbc.C3P0NativeJdbcExtractor" /> 
<bean id="lobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler" lazy-init="true">    
 <property name="nativeJdbcExtractor" ref="jdbcExtractor"/>
</bean>  
-->

 
问题二：关于LOB解决之道：（针对Oracle9i或更高版本）
注：主要是CLOB问题，CLOB资料http://developer.51cto.com/art/200907/136699.htm
方案一：采用spring的DefaultLobHandler
前提：Oracle 9i或更高版本，ojdgc14或更高版本驱动JAR
    （推荐10g以上驱动，如ojdbc5-11.1.0.6.0-Produc.jar或ojdbc14-10.2.0.4.0.jar）
优点：完全透明化，（遵循最佳实践parameterClass&resultMap）
不足：只是对Oracle的CLOB只能支持到最大值4000字符
备注：原则上说，DefaultLobHandler不支持Oracle9i，且对Oracle10g仅有限支持；关于此点限制应该是指对CLOB的支持，因为在Oracle9i中我应用的BLOB操作无任何问题，而CLOB当时没太在意，不知是否存在4k字符的限制，若有，请采用方案二。
在xSpring.xml中样例

<bean id="lobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler" lazy-init="true" />
<!-- 配置sqlMapClient -->
<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
 <property name="dataSource" ref="dataSource" />
 <property name="lobHandler" ref="lobHandler"/>
 <property name="configLocation" value="classpath:ibatis.xml" />   
</bean>

 
在ibatis.xml中的全局配置

<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"/>

 
 
方案二：采用spring的OracleLobHandler
前提：Oracle 9i或更高版本，ojdgc14或更高版本驱动JAR
优点：完全透明化，解决CLOB最大值为4k字符的限制（遵循最佳实践parameterClass&resultMap）
不足：需要根据数据库连接的类型选择不同的本地SQL执行器，如Spring2.5.6中支持的：
 ①直接JDBC：SimpleNativeJdbcExtractor或Jdbc4NativeJdbcExtractor（若采用jdbc4 api）
 ②c3p0数据库连接池：CommonsDbcpNativeJdbcExtractor
 ③dhcp数据库连接池：C3P0NativeJdbcExtractor
 ④xprool数据库连接池：XAPoolNativeJdbcExtractor
 ⑤jboss数据库连接池：JBossNativeJdbcExtractor
 ⑥weblogic数据库连接池：WebLogicNativeJdbcExtractor
 ⑦websphere数据库连接池：WebSphereNativeJdbcExtractor
在xSpring.xml中样例

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

 
<!-- 配置sqlMapClient -->

<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
 <property name="dataSource" ref="dataSource" />
 <property name="lobHandler" ref="lobHandler"/>
 <property name="configLocation" value="classpath:ibatis.xml" />   
</bean>

 
在ibatis.xml中的全局配置（同上）

 

以下是参考资料，来自于Spring2.5.6的源码

pakage org.springframework.orm.ibatis.support；
/**
 * iBATIS TypeHandler implementation for byte arrays that get mapped to BLOBs.
 * Retrieves the LobHandler to use from SqlMapClientFactoryBean at config time.
 *
 * <p>Can also be defined in generic iBATIS mappings, as DefaultLobCreator will
 * work with most JDBC-compliant database drivers. In this case, the field type
 * does not have to be BLOB: For databases like MySQL and MS SQL Server, any
 * large enough binary type will work.
 *
 * @author Juergen Hoeller
 * @since 1.1.5
 * @see org.springframework.orm.ibatis.SqlMapClientFactoryBean#setLobHandler
 */
public class BlobByteArrayTypeHandler extends AbstractLobTypeHandler {
 //...
}
/**
 * iBATIS TypeHandler implementation for arbitrary objects that get serialized to BLOBs.
 * Retrieves the LobHandler to use from SqlMapClientFactoryBean at config time.
 *
 * <p>Can also be defined in generic iBATIS mappings, as DefaultLobCreator will
 * work with most JDBC-compliant database drivers. In this case, the field type
 * does not have to be BLOB: For databases like MySQL and MS SQL Server, any
 * large enough binary type will work.
 *
 * @author Juergen Hoeller
 * @since 1.1.5
 * @see org.springframework.orm.ibatis.SqlMapClientFactoryBean#setLobHandler
 */
public class BlobSerializableTypeHandler extends AbstractLobTypeHandler {
 //...
}
/**
 * iBATIS TypeHandler implementation for Strings that get mapped to CLOBs.
 * Retrieves the LobHandler to use from SqlMapClientFactoryBean at config time.
 *
 * <p>Particularly useful for storing Strings with more than 4000 characters in an
 * Oracle database (only possible via CLOBs), in combination with OracleLobHandler.
 *
 * <p>Can also be defined in generic iBATIS mappings, as DefaultLobCreator will
 * work with most JDBC-compliant database drivers. In this case, the field type
 * does not have to be BLOB: For databases like MySQL and MS SQL Server, any
 * large enough binary type will work.
 *
 * @author Juergen Hoeller
 * @since 1.1.5
 * @see org.springframework.orm.ibatis.SqlMapClientFactoryBean#setLobHandler
 */
public class ClobStringTypeHandler extends AbstractLobTypeHandler {
 //...
}

package org.springframework.jdbc.support.lob;
/**
 * Default implementation of the {@link LobHandler} interface. Invokes
 * the direct accessor methods that <code>java.sql.ResultSet</code>
 * and <code>java.sql.PreparedStatement</code> offer.
 *
 * <p>This LobHandler should work for any JDBC driver that is JDBC compliant
 * in terms of the spec's suggestions regarding simple BLOB and CLOB handling.
 * This does not apply to Oracle 9i, and only to a limited degree to Oracle 10g!
 * As a consequence, use {@link OracleLobHandler} for accessing Oracle BLOBs/CLOBs.
 *
 * <p>Some JDBC drivers require values with a BLOB/CLOB target column to be
 * explicitly set through the JDBC <code>setBlob</code> / <code>setClob</code>
 * API: for example, PostgreSQL's driver. Switch the {@link #setWrapAsLob "wrapAsLob"}
 * property to "true" when operating against such a driver.
 *
 * <p>On JDBC 4.0, this LobHandler also supports streaming the BLOB/CLOB content
 * via the <code>setBlob</code> / <code>setClob</code> variants that take a stream
 * argument directly. Consider switching the {@link #setStreamAsLob "streamAsLob"}
 * property to "true" when operating against a fully compliant JDBC 4.0 driver.
 *
 * <p>See the {@link LobHandler} javadoc for a summary of recommendations.
 *
 * @author Juergen Hoeller
 * @since 04.12.2003
 * @see #setStreamAsLob
 * @see java.sql.ResultSet#getBytes
 * @see java.sql.ResultSet#getBinaryStream
 * @see java.sql.ResultSet#getString
 * @see java.sql.ResultSet#getAsciiStream
 * @see java.sql.ResultSet#getCharacterStream
 * @see java.sql.PreparedStatement#setBytes
 * @see java.sql.PreparedStatement#setBinaryStream
 * @see java.sql.PreparedStatement#setString
 * @see java.sql.PreparedStatement#setAsciiStream
 * @see java.sql.PreparedStatement#setCharacterStream
 */
public class DefaultLobHandler extends AbstractLobHandler {
 //...
}
/**
 * {@link LobHandler} implementation for Oracle databases. Uses proprietary API
 * to create <code>oracle.sql.BLOB</code> and <code>oracle.sql.CLOB</code>
 * instances, as necessary when working with Oracle's JDBC driver.
 * Note that this LobHandler requires Oracle JDBC driver 9i or higher!
 *
 * <p>While most databases are able to work with {@link DefaultLobHandler},
 * Oracle just accepts Blob/Clob instances created via its own proprietary
 * BLOB/CLOB API, and additionally doesn't accept large streams for
 * PreparedStatement's corresponding setter methods. Therefore, you need
 * to use a strategy like this LobHandler implementation.
 *
 * <p>Needs to work on a native JDBC Connection, to be able to cast it to
 * <code>oracle.jdbc.OracleConnection</code>. If you pass in Connections from a
 * connection pool (the usual case in a J2EE environment), you need to set an
 * appropriate {@link org.springframework.jdbc.support.nativejdbc.NativeJdbcExtractor}
 * to allow for automatical retrieval of the underlying native JDBC Connection.
 * LobHandler and NativeJdbcExtractor are separate concerns, therefore they
 * are represented by separate strategy interfaces.
 *
 * <p>Coded via reflection to avoid dependencies on Oracle classes.
 * Even reads in Oracle constants via reflection because of different Oracle
 * drivers (classes12, ojdbc14) having different constant values! As this
 * LobHandler initializes Oracle classes on instantiation, do not define this
 * as eager-initializing singleton if you do not want to depend on the Oracle
 * JAR being in the class path: use "lazy-init=true" to avoid this issue.
 *
 * @author Juergen Hoeller
 * @since 04.12.2003
 * @see #setNativeJdbcExtractor
 * @see oracle.sql.BLOB
 * @see oracle.sql.CLOB
 */
public class OracleLobHandler extends AbstractLobHandler {
 //...
}