Ibatis 中的 oracle LOB 字段处理

方法一

来源：http://opensource2.atlassian.com/confluence/oss/display/IBATIS/How+do+I+use+a+BLOB+or+CLOB

Here is an example of how to use the Custom Type Handler (CTH) feature of iBatis with large objects (LOB) such as BLOB's (Binary) and CLOB's (Character). As of release 2.0.9 the iBatis framework has the default CLOB and BLOB type handlers included. The example below was done for Oracle but should work for any database with a well written JDBC driver. Make sure that you do not use the thin driver supplied from Oracle. You need to use the latest ojbc14.jar.

The example below was not the intended way to use CTH's but it works great for me!

First lets take a look at the table.

Report.sql

REPORT {
        id              varchar2(5),
        name            varchar2(25),
        description     varchar2(1000),
        data            BLOB
}

Next we continue by creating a plain old java object (POJO) to represent this table.

Report.java

/*
 * Report.java
 *
 * Created on March 23, 2005, 11:00 AM
 */
package reporting.viewer.domain;

/**
 *
 * @author Nathan Maves
 */
public class Report {
    
    /**
     * Holds value of property id.
     */
    private String id;
    /**
     * Holds value of property name.
     */
    private String name;
    /**
     * Holds value of property description.
     */
    private String id;
    /**
     * Holds value of property data.
     */
    private byte[] data;


    //Standard accessors and mutators

   public byte[] getData() {
       return this.data;
   }

   public void setData(byte[] data) {
       this.data = data;
   }
}

Now that the easy stuff is completed let connect both the database and the POJO together using iBatis.

Report.xml

<typeAlias alias="Report" type="reporting.viewer.domain.Report"/>

<resultMap class="Report" id="ReportResult">
        <result column="id" property="id" />
        <result column="name" property="name" />
        <result column="description" property="description" />
        <result column="data" property="data" jdbcType="BLOB"/>
</resultMap>

<select id="getReportById" parameterClass="string" resultMap="ReportResult">
        SELECT 
            *
        FROM 
            REPORT
        WHERE 
            id = #value#
</select>

<insert id="insertReport" parameterClass="Report">
        INSERT INTO 
            REPORT (
                id, 
                name, 
                description,
                data
                )
            values (
                #id#, 
                #name#, 
                #description#,
                #data#
            )
</insert>

<update id="updateReport" parameterClass="Report">
        UPDATE REPORT set
                name = #name#,
                description = #description#,
                data = #data#
        WHERE
                id = #id#
</update>

As you can see there is nothing special that you need to do.

When working with a CLOB the only that the you need to change is the property in your bean. Just change byte[] to java.lang.String.

Data size bigger than max size for this type: ???? Some of the older jdbc drivers for Oracle have trouble with Strings that are larger then 4k. The first step to correct this issue it to get a jdbc driver from Oracle that is newer then 10g Release 2. This driver will work with both 9i and 10g databases. If you are stuck with an older driver you can try to set a driver property. The property is SetBigStringTryClob=true. If you are using the SimpleDataSource with iBatis use the follow line in the config file. <property name="Driver.SetBigStringTryClob" value="true"/>

Data size always 86 bytes? If you find that the length of your byte[] is always 86, check that you have the jdbcType="BLOB" or jdbcType="CLOB" in your result map.

方法二

http://spaces.msn.com/tsaijun/

Spring+Ibatis中插入LOB字段

sql-map-config.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中配置

<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
  <property name="configLocation"><value>/sql-map-config.xml</value></property>
  <property name="dataSource"><ref local="dataSource"/></property>
  <property name="lobHandler"><ref local="oracleLobHandler"/></property>
 </bean>

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

 <!-- LobHandler for Oracle JDBC drivers -->
 <!-- (refers to the NativeJdbcExtractor above to get access to native OracleConnections) -->
 <bean id="oracleLobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler"
   lazy-init="true">
  <property name="nativeJdbcExtractor"><ref local="nativeJdbcExtractor"/></property>
 </bean>

 

这样Spring会使用LobHandler对Lob字段进行操作。

 

方法三

http://java.mblogger.cn/wuyu/archive/09292004.aspx

ibatis 2.0.5以后提供的TypeHandlerCallback，应该可以解决ibatis操作oracle clob/blob这类字段的问题

自己用了ibatis以后，逛ibatis的forum似乎就成了每日的功课了。今天在论坛上翻到一个贴

http://sourceforge.net/forum/forum.php?thread_id=1120723&forum_id=206694

因为是在家，勿勿做了一个的CLOB读测试，等明天上班再全面测试好了。

自己定义了一个spring.ORM.ibatis.OracleClobTypeHandlerCallback，因为只测试读，就只实现了getResult方法

public class OracleClobTypeHandlerCallback implements TypeHandlerCallback {

  public Object getResult(ResultGetter getter) throws SQLException {
    if (logger.isDebugEnabled()) logger.debug(getClass().getName());
    CLOB clob = (CLOB) getter.getClob();
    if (clob == null || clob.length() == 0) {
      if (logger.isDebugEnabled()) logger.debug("CLOB对象为空");
      return "";
    }
    else
      return clob.getSubString((long) 1, (int) clob.length());
  }

做了个load的sqlmap文件

<?xml version="1.0" encoding="GBK"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
    "http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="Clob">
    <resultMap class="spring.ORM.Clob" id="result">
        <result property="id" column="CONTENT_ID" javaType="long"
            jdbcType="BIGINT"/>
        <result property="value" column="S_CONTENT"
            typeHandler="spring.ORM.ibatis.OracleClobTypeHandlerCallback"/>
    </resultMap>
    <select id="load" parameterClass="long" resultMap="result"> <![CDATA[
        SELECT
            t.CONTENT_ID,
            t.S_CONTENT
        FROM T_INFOCONTENT  t
        WHERE t.CONTENT_ID=#value#
        ]]> </select>
</sqlMap>

随便写了段测试代码：

    ClobDAOIface dao = (ClobDAOIface) ctx.getBean("ClobDAOProxy");
    Clob clob=dao.loadClob(113499191529712L);
    System.out.println(clob.getValue());

哈哈！看来有望解决CLOB/BLOB的问题了，可以不再调jdbc来单独操作clob/blob字段，ibatis提供的cache、延迟加载等等东西也能用了，爽！

方法四

http://www.matrix.org.cn/blog/zhenggc/

spring+ibatis+oracle clob完全搞定

算是伤经动骨了，几乎翻了一遍ibatis和spring相关的代码，先说搞定的情况，
spring115+ibatis216(215有问题，216已经修复，原来用215，现在已经替换，是ibatis在buglist上的191号问题)oracle9i2。oracle的驱动用了10g的，感谢上次在qq上那个(谁?忘记名字了，不好意思)传给我的10g的驱动。
基本思路是用ibatis提供的com.ibatis.sqlmap.engine.type.ClobTypeHandlerCallback
在ibatis的sqlmap中配置resultMap和parameterMap，
Oracle9i的驱动只能插入很少的字符，不清楚为什么，读取基本没有问题。
Oracle8i的数据库和驱动根本就没有成功过。
spring的那个org.springframework.jdbc.support.lob.LobHandler
和org.springframework.jdbc.support.lob.OracleLobHandler，
和org.springframework.orm.ibatis.support.ClobStringTypeHandler只能读取，写的时候居然判断Connection的类型是不是Oracle.jdbc.OracleConnection，不适合dbcp之类的连接池，不清楚后期版本有没有改动。spring的org.springframework.jdbc.support.lob.OracleLobHandler就是等于getString和setString，一点用处都没有，呵呵。
ibatis215的那个com.ibatis.sqlmap.engine.type.ClobTypeHandlerCallback居然在set reader以后，又set了一次string，真够害人的。216已经修改bug。
没有大堆代码是没有办法描述这个东西的，大致记录一下，希望可以对人有帮助。