ibatis中接受Clob类型的数据（ Cause: java.sql.SQLException: ORA-22835: Buffer too small f）

此前在使用中将CLOB类型的数据转化为可用String类型接受的属性的方法是to_char(......),但在其使用中发现，当其长度超过4000时就会抛出异常（ Cause: java.sql.SQLException: ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion）

其实本身这么转换就存在问题，因为CLOB的长度最大可为4G,如此转换肯定会存在问题，以下是正确的转换方式：

在相应的informationRelease.xml文件如此表示

<resultMap id="getInformationByIdResultMap" class="com.foundersc.crmweb.entity.information.InformationTo">
		<result property="id" column="id"/>
		<result property="title" column="title"/>
		<result property="mediaSource" column="mediaSource"/>
		<result property="publishDate" column="publishDate"/>
		<result property="publishUserName" column="publishUserName"/>
		<result property="createDt" column="createDt"/>
		<result property="keyword" column="keyword"/>
		<result property="cascadeType" column="cascadeType"/>
		<result property="industrySort" column="industrySort"/>
		<result property="relationCode" column="relationCode"/>
		<result property="isRelease" column="isRelease"/>
		<result property="publishUserId" column="publishUserId"/>
		<result property="sendGroupType" column="sendGroupType"/>
		<result property="readedCount" column="readedCount"/>
		<result property="message" column="message"/>
		<result property="content" column="content" jdbcType="CLOB" javaType = "java.lang.String" typeHandler="com.foundersc.crmweb.util.OracleClobTypeHandlerCallback"/>
	</resultMap>

<select id="information.getInformationById" resultMap="getInformationByIdResultMap" parameterClass="java.lang.String">
		SELECT distinct info.information_id As id,
			   info.title AS title,
			   info.media_source   AS mediaSource,
			   info.publish_date   AS publishDate,
			   userT.userName   AS publishUserName,
			   info.create_dt   AS createDt,
		       infoContent.content  AS content,
		       info.keyword   AS keyword,
		       info.cascade_type   AS cascadeType,
		       info.industry_sort   AS industrySort,
		       info.relation_code   AS relationCode,
		       info.is_release   AS isRelease,
		       info.publish_userId AS publishUserId,
		       info.send_group_type AS sendGroupType,
		       info.readed_count AS readedCount,
		       info.message AS message
		FROM   crm.crm_t_information info
		left join crm.crm_t_information_content infoContent on infoContent.information_id = info.information_id
		left join kf.au_t_user userT on userT.user_id = info.create_by
		WHERE  info.information_id = #informationId#
	</select>

 

 其中红色字体标示的地方才是咱们真正关心的，其中typeHandler的值即为咱们写的转换CLOB类型为String类型的java代码的路径，其java代码如下：

package com.foundersc.crmweb.util;

import java.sql.SQLException;

import oracle.sql.CLOB;

import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;

public class OracleClobTypeHandlerCallback implements TypeHandlerCallback {

	//获取时将CLOB类型的值转换为我们需要的，此处转换为了Object
	public Object getResult(ResultGetter getter) throws SQLException {
		CLOB clob = (CLOB) getter.getClob();  
        return (clob == null || clob.length() == 0 )? "" :clob.getSubString((long)1, (int)clob.length());  
	}

	//用CLOB类型的变量接受从数据库中读取的值
	@SuppressWarnings("deprecation")
	public void setParameter(ParameterSetter setter, Object obj)
			throws SQLException {
		  CLOB clob = CLOB.empty_lob();  
	      clob.setString(1, (String)obj);  
		  setter.setClob(clob);    

	}

	public Object valueOf(String arg0) {
		// TODO Auto-generated method stub
		return null;
	}

}

 

备注：在查询的sql语句中不能含有distinct,至于原因我也不太清楚。