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.

http://opensource2.atlassian.com/confluence/oss/images/icons/emoticons/check.gif

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

http://opensource2.atlassian.com/confluence/oss/images/icons/emoticons/check.gif

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://blog.csdn.net/fckeditor/editor/images/smiley/msn/lightbulb.gif
方法二
　　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://blog.csdn.net/fckeditor/editor/images/smiley/msn/lightbulb.gif
方法三
　　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://blog.csdn.net/fckeditor/editor/images/smiley/msn/lightbulb.gif
方法四
　　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。
没有大堆代码是没有办法描述这个东西的，大致记录一下，希望可以对人有帮助。