Oracle插入Clob字段

最新推荐文章于 2025-08-21 10:25:20 发布

原创最新推荐文章于 2025-08-21 10:25:20 发布 · 2.9k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#java

编程专栏收录该内容

47 篇文章

订阅专栏

这是一个关于在MyBatis中自定义TypeHandler处理OracleCLOB类型的示例代码。OracleClobTypeHandler实现了将字符串与OracleCLOB类型之间的转换，主要用于批量插入数据操作。在XML配置中，可以看到如何在SQL插入语句中使用这个类型处理器。

配置类

package com.ccbft.smartoffice.base.config;

import oracle.jdbc.driver.OracleConnection;
import oracle.sql.CLOB;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;

import java.sql.*;

public class OracleClobTypeHandler implements TypeHandler<String> {

    public String valueOf(String param) {
        return null;
    }

    @Override
    public String getResult(ResultSet arg0, String arg1) throws SQLException {
        CLOB clob = (CLOB) arg0.getClob(arg1);
        return (clob == null || clob.length() == 0) ? null : clob.getSubString((long) 1, (int) clob.length());
    }

    @Override
    public String getResult(ResultSet arg0, int arg1) throws SQLException {
        return null;
    }

    @Override
    public String getResult(CallableStatement arg0, int arg1) throws SQLException {
        return null;
    }

    @Override
    public void setParameter(PreparedStatement arg0, int arg1, String arg2, JdbcType arg3) throws SQLException {
        Connection connection = arg0.getConnection();
        OracleConnection oracleConnection = connection.unwrap(OracleConnection.class);
        CLOB clob = CLOB.createTemporary(oracleConnection, true, 1, (short) 1);
        clob.setString(1, arg2);
        arg0.setClob(arg1, clob);
    }
}

xml

 <insert id="insertBatchData" parameterType="java.util.List" useGeneratedKeys="false">
        insert into oms_notice
        (id,tb_uuid,notice_title,notice_content,create_user,publisher,receive_user,create_time,notice_content_type,notice_category,notice_source,top,publish_time,status,update_time,update_user,is_deleted)
        select SEQ_OMS_NOTICE_ID.nextval,A.* from(
        <foreach collection="noticeList" separator="union all" item="ui">
            (select
            #{ui.tbUuid,jdbcType=VARCHAR} tb_uuid,
            #{ui.noticeTitle,jdbcType=VARCHAR} notice_title,
            #{ui.noticeContent,jdbcType=CLOB,typeHandler=com.ccbft.smartoffice.base.config.OracleClobTypeHandler} notice_content,
            #{ui.createUser,jdbcType=VARCHAR} create_user,
            #{ui.publisher,jdbcType=VARCHAR} publisher,
            #{ui.receiveUser,jdbcType=VARCHAR} receiveUser,
            #{ui.createTime,jdbcType=TIMESTAMP} create_time,
            #{ui.noticeContentType,jdbcType=INTEGER} notice_content_type,
            #{ui.noticeCategory,jdbcType=VARCHAR} notice_category,
            2 notice_source,
            0 top,
            #{ui.publishTime,jdbcType=TIMESTAMP} publish_time,
            2 status,
            #{ui.updateTime,jdbcType=TIMESTAMP} update_time,
            #{ui.updateUser,jdbcType=VARCHAR} update_user,
            0 is_deleted
            from dual
            )
        </foreach>
        )A
    </insert>