最近在开发中遇到需要将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>