How_do_I_use_a_Custom_Type_Handler_with_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 {
        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
 *
 * 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.

<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#
</insert>

As you can see there is nothing special that you need to do. CLOB's should work the exact same way. Everything just works!