最近在开发中遇到需要将oracle中查询到的Blob字段转为实体类的String

如果实体类中某个属性在数据库中存储的类型为Blob，而实体类用String类型接收会抛出异常

解决办法：

1.自定义TypeHandler

实现的方式有很多可以实现TypeHandler接口或者继承BaseTypeHandler父类

import java.io.ByteArrayInputStream;
import java.io.UnsupportedEncodingException;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

/**
 * Blob转String类型转换器
 */
public class Blob2StringTypeHandler extends BaseTypeHandler<String> {

    private static final String DEFAULT_CHARSET = "UTF-8";
    
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i,
            String parameter, JdbcType jdbcType) throws SQLException {
        if(jdbcType == JdbcType.BLOB){
            ByteArrayInputStream bis;
            try {
                byte[] b = parameter.getBytes(DEFAULT_CHARSET);
                bis = new ByteArrayInputStream(b);
                ps.setBinaryStream(i, bis, b.length);
            } catch (UnsupportedEncodingException e) {
                throw new RuntimeException("Blob Encoding Error!", e);
            }
        }else{
            ps.setString(i, parameter);
        }
    }

    @Override
    public String getNullableResult(ResultSet rs, String columnName)
            throws SQLException {
        try {
            Blob blob = rs.getBlob(columnName);
            byte[] returnValue = new byte[0];
            if (null != blob) {
                returnValue = blob.getBytes(1, (int) blob.length());
            }
            return new String(returnValue, DEFAULT_CHARSET);
        } catch (Exception e) {
            return rs.getString(columnName);
        }
    }

    @Override
    public String getNullableResult(ResultSet rs, int columnIndex)
            throws SQLException {
        try {
            Blob blob = rs.getBlob(columnIndex);
            byte[] returnValue = new byte[0];
            if (null != blob) {
                returnValue = blob.getBytes(1, (int) blob.length());
            }
            return new String(returnValue, DEFAULT_CHARSET);
        } catch (Exception e) {
            return rs.getString(columnIndex);
        }
    }

    @Override
    public String getNullableResult(CallableStatement cs, int columnIndex)
            throws SQLException {
        Blob blob = cs.getBlob(columnIndex);
        byte[] returnValue = new byte[0];
        if (null != blob) {
            returnValue = blob.getBytes(1, (int) blob.length());
        }
        try {
            return new String(returnValue, DEFAULT_CHARSET);
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException("Blob Encoding Error!", e);
        }
    }

}

2.在配置中声明自定义的TypeHandler

我这里是在mybatis-config.xml文件中配置

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="cacheEnabled" value="false"/>
        <setting name="useGeneratedKeys" value="false"/>
        <!-- <setting name="defaultExecutorType" name="REUSE" /> -->
        <setting name="logImpl" value="LOG4J"/>
        <!-- 开启自动驼峰命名规则 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    
    <typeHandlers>
        <typeHandler handler="com.sunline.etl.common.ibatis.Blob2StringTypeHandler" jdbcType="BLOB" javaType="java.lang.String"/>
    </typeHandlers>

    <databaseIdProvider type="DB_VENDOR">
        <property name="MySQL" value="mysql"/>
        <property name="Oracle" value="oracle"/>
        <property name="MSSQL" value="mssql"/>
        <property name="Microsoft SQL Server" value="mssql"/>
    </databaseIdProvider>
    
    <mappers>
       
    </mappers>
</configuration>

springboot项目可以在yml中配置

mybatis:
  #  该配置项配置了MyBatis配置文件保存路径
  mapper-locations: classpath*:show/mrkay/mapper/*.xml
  type-handlers-package: show.mrkay.handler

3.使用

<resultMap type="com.sunline.etl.mapping.entity.CaEtlDtdScriptVO" id="caEtlDtdScriptVOMap">
        <result column="task_code" property="taskCode"/>
        <result column="sql_seq" property="sqlSeq"/>
        <result column="task_step" property="taskStep"/>
        <result column="task_name" property="taskName"/>
        <result column="sql_type" property="sqlType"/>
        <result typeHandler="com.sunline.etl.common.ibatis.Blob2StringTypeHandler" column="sql_script" property="sqlScript" javaType="string" jdbcType="BLOB"/>
    </resultMap>

    <select id="queryTaskScriptByTaskCode" resultMap="caEtlDtdScriptVOMap">
        SELECT s.task_code, s.sql_seq, s.sql_script, d.task_code_simp, d.task_name,s.sql_type
          FROM ca_etl_dtd_script s
         INNER JOIN ca_bat_task_group_rel g
            ON s.task_code = g.task_code
         INNER JOIN ca_etl_dtd d
            ON s.task_code = d.task_code
        WHERE s.task_code = #{taskCode}
        ORDER BY g.sort_no,s.sql_seq
    </select>