MyBatis 操作ORACLE 中CLOB字段

MyBatis 操作ORACLE 中CLOB字段相关功能：
1.插入
2.查询

一、背景

近期项目中遇到了用mybatis中ORACLE 数据库CLOB类型的相关操作,记录一下,方便以后查阅。

二、实例代码

varchar2类型的字符长度是4000，注意：oracle对汉字的存储占3个字符
表结构：

create table T_RECRUITMENT_ORDER
(
  id                NUMBER(8) not null,
  user_id           NUMBER(8),
  club_id           NUMBER(8),
  recruitment_title VARCHAR2(255),
  recruitment_img   VARCHAR2(200),
  createtime        DATE default sysdate,
  club_introduce    CLOB
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column T_RECRUITMENT_ORDER.user_id
  is '创建者用户id';
comment on column T_RECRUITMENT_ORDER.club_id
  is '俱乐部id';
comment on column T_RECRUITMENT_ORDER.recruitment_title
  is '招募令标题';
comment on column T_RECRUITMENT_ORDER.recruitment_img
  is '招募令海报';
comment on column T_RECRUITMENT_ORDER.createtime
  is '创建时间';
comment on column T_RECRUITMENT_ORDER.club_introduce
  is '俱乐部介绍';

功能1：MyBatis +ORACLE 插入CLOB

mybatis映射文件中编写如下 insert语句（CLUB_INTRODUCE 定义 jdbcType类型为CLOB），实现类正常调用即可：

 <!-- 创建集结令 -->
     <insert id="addRecruitmentOrder"  parameterType="java.util.HashMap" >
        <selectKey resultType="java.lang.Integer" order="BEFORE" keyProperty="ID">
     		 SELECT SEQ_T_RECRUITMENT_ORDER.nextval AS ID FROM dual
        </selectKey>
        insert into t_recruitment_order(
            id,
            user_id,
            club_id,
            recruitment_title,
            recruitment_img,
            club_introduce,
            createtime
        )values(
        	#{ID,jdbcType=DECIMAL},
            #{MY_USERID,jdbcType=DECIMAL},
            #{CLUB_ID,jdbcType=DECIMAL},
            #{RECRUITMENT_TITLE,jdbcType=VARCHAR},
            #{RECRUITMENT_IMG,jdbcType=VARCHAR},
            #{CLUB_INTRODUCE,jdbcType=CLOB},
            sysdate
        )
     </insert>

上面的方法只适合 4000字符以内的数据插入。 超过4000字符，则会报如下错：

解决方法：使用变量，通过PL/SQL将数据赋予CLOB变量，通过引用变量将数据插入:

<!-- 创建集结令 -->
     <insert id="addRecruitmentOrder"  parameterType="java.util.HashMap" >
        <selectKey resultType="java.lang.Integer" order="BEFORE" keyProperty="ID">
     		 SELECT SEQ_T_RECRUITMENT_ORDER.nextval AS ID FROM dual
        </selectKey>
         DECLARE
            club_introduce CLOB :=#{CLUB_INTRODUCE,jdbcType=CLOB};
		 BEGIN
	        insert into t_recruitment_order(
	            id,
	            user_id,
	            club_id,
	            recruitment_title,
	            recruitment_img,
	            club_introduce,
	            createtime
	        )values(
	        	#{ID,jdbcType=DECIMAL},
	            #{MY_USERID,jdbcType=DECIMAL},
	            #{CLUB_ID,jdbcType=DECIMAL},
	            #{RECRUITMENT_TITLE,jdbcType=VARCHAR},
	            #{RECRUITMENT_IMG,jdbcType=VARCHAR},
	            club_introduce,
	            sysdate
	        );
        END;
     </insert>

功能2：MyBatis +ORACLE 查询CLOB

方案一：ORACLE 函数（有长度限制）

方式1：dbms_lob.substr(CLOB字段)

-- Clob字段查看方法1： 此方法查询出的数据长度不可超过4000个字符
select t.id,dbms_lob.substr(t.club_introduce) as text from T_RECRUITMENT_ORDER t

注意：此方法查询出的数据长度不可超过4000个字符
执行结果：

方式2：to_char(CLOB字段)

注意：此方法查询出的数据长度不可超过4000个字符

-- Clob字段查看方法2： 此方法查询出的数据长度不可超过4000个字符
select t.id,to_char(t.club_introduce) as text from T_RECRUITMENT_ORDER t

执行结果：

这两个方法都有数据长度的限制。

方案二：直接读取， 将Clob转成String（最终方案）

最终解决方案如下：

直接读取clob: select column from …
Clob columnClob = (Clob) map.get(“column”);
将Clob转成String

（1）sql语句直接读取

<!--根据集结令id获取集结令详情信息-->
 <select id="getRecruitmentOrderInfo" parameterType="java.util.HashMap" resultType="java.util.HashMap">
        select * 
        from t_recruitment_order
        where ID =#{recruitment_id,jdbcType=DECIMAL}
 </select>

（2）编写工具类：将Clob转成String

package com.njpp.wxxcx.util;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.Clob;
import java.sql.SQLException;

public class ClobToString {
	
	/**
	 *  Clob类型 转String
	 * @param clob
	 * @return
	 * @throws SQLException
	 * @throws IOException
	 */
    public static String ClobToString(Clob clob) throws SQLException, IOException {
        String ret = "";
        Reader read= clob.getCharacterStream();
        BufferedReader br = new BufferedReader(read);
        String s = br.readLine();
        StringBuffer sb = new StringBuffer();
        while (s != null) {
            sb.append(s);
            s = br.readLine();
        }
        ret = sb.toString();
        if(br != null){
            br.close();
        }
        if(read != null){
            read.close();
        }
        return ret;
    }
}

（3）在相应的ClubServiceImpl中调用工具类ClobToString中的方法

	/**
	 * 根据集结令id获取集结令详情信息
	 */
	@Override
	public Map<String, Object> getRecruitmentOrderInfo(HttpServletRequest request) throws Exception {
		Map<String, Object> result = new HashMap<String, Object>();
		Map<String, Object> paramMap = GetParamToMap.getParameterMapCode(request);
		//根据集结令id获取集结令详情信息
		Map<String, Object> recruitmentOrder = clubMapper.getRecruitmentOrderInfo(paramMap);
        //处理 CLUB_INTRODUCE（clob类型）
        Clob columnClob = (Clob) recruitmentOrder.get("CLUB_INTRODUCE");
        String CLUB_INTRODUCE = ClobToString.ClobToString(columnClob);//Clob类型 转String
        recruitmentOrder.put("CLUB_INTRODUCE", CLUB_INTRODUCE);
        
		result.put("recruitmentOrder", recruitmentOrder);// 集结令详情
		
		result.put(ErrCode.STATE, ErrCode.SUCCESS);
		return result;
	}

参考资料：
MyBatis 读取CLOB格式字段 返回Map相关问题

oracle插入CLOB类型超过4000个字符报ORA-01704错的解决方法及其它相关场景解决方案

Mybatis向oracle数据库插入clob字段，长度大于4000时报错