iBATIS CLOB&BLOB最终解决方案

最新更新：使用最新版ibatis（ibatis-2.3.4.726）+最新版ojdbc（10g），可以用thin连接像操作普通数据一样操作clob（string），blob（byte[]）对象，不用oci连接方式！


1， 下载Oracle10GinstantClient驱动并安装
2， 环境变量的PATH指向Oracle10GinstantClient的安装文件夹
3， 把Oracle10GinstantClient文件夹下的classes.jar考到工程的lib文件夹下，并且保证tomcat的common/lib/文件夹下没有同名文件
4， 配好本地的TNS，把JDBC驱动的Url从jdbc:oracle:thin:@<your IP>:<工程名>改为jdbc:oracle:oci:@<工程名>

大功告成，然后就可以用ibatis象操作普通类型的字段一样操作CLOB和BLOB类型了！

代码如下所示：
iBATIS DataMapper
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.

Oracle10GinstantClient 这个在会修改的数据库结构的情况下， OCI JAVA DRIVER 可能造成严重的jvm CRASH。 这个已经被我们无数次证明了。 ORACLE OCI JAVA DRIVER还是要谨慎使用。 我们目前已经放弃了这个用法。
SetBigStringTryClob是个比较正确的，  thin driver也是支持的。
BLOB我没有用过， 我不知道。