Fetching a blob from database as a byte[] using myBatis

Question

I am using spring MyBatis 1.2.0 in a project, where I have a query that gets data from a BLOB field in an Oracle 11g database. I want to retrieve the field as a byte array (byte[]), my Code is:

<select id="getResponse" resultType="_byte[]" parameterType="string">
   select blob_Data from Table where id = #{value,jdbcType=VARCHAR} AND ROWNUM = 1    
</select>

This is giving following error:

java.lang.ClassCastException: [B incompatible with [Ljava.lang.Object;
    at org.apache.ibatis.binding.MapperMethod.convertToArray(MapperMethod.java:136)
    at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:119)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)

Apart from this, I have also tried using resultMap:

<resultMap id="responseMap" type="ResponseMessageModel">
    <result property="blobData" column="blob_Data"/>
</resultMap>

<select id="getResponse" resultMap="responseMap" parameterType="string">
   select blob_Data from table where id = #{value,jdbcType=VARCHAR} AND ROWNUM = 1    
</select>

and also specifying the javaType:

<resultMap id="responseMap" type="ResponseMessageModel">
      <result property="blobData" javaType="_byte[]" column="blob_Data"/>
</resultMap>

<select id="getResponse" resultMap="responseMap" parameterType="string">
   select blob_Data from table where id = #{value,jdbcType=VARCHAR} AND ROWNUM = 1    
</select>

but with no luck, all give the same ClassCastException

Could someone please tell me what I am doing wrong?

Answer 1

Try to specify a JDBC type in your result map:

<result property="blobData" column="blob_Data" jdbcType="BLOB"/>

Here an example from Camunda BPM:

Mapping with result map "resourceResultMap", that contains a bytes property

Entity with bytes (byte[]) field

Edit:

If it is not working, please have a look at following question. It suggests to use BINARY as JDBC type or to use a custom type handler like in the accepted answer.

Answer 2

So, I got this to work by making the following changes in my code-

I am using a resultMap and specified both javaType and jdbcType:

<resultMap id="responseMap" type="ResponseMessageModel">
      <result property="blobData" javaType="_byte[]" column="blob_Data" jdbcType="BLOB"/>
</resultMap>

<select id="getResponse" resultMap="responseMap" parameterType="string">
   select blob_Data from table where id = #{value,jdbcType=VARCHAR} AND ROWNUM = 1    
</select>

By doing this, I am able to successfully retrieve the BLOB value as a byte[].

Answer 3

I have used this in my query to fetch the blob column value as a string

UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOB_COLUMN_NAME, 200,1)) FAX_NOTES

This maps the blob to a string and mybatis has:

<result column="FAX_NOTES" property="faxNotes"  jdbcType="BLOB" />

In the java class -

private String faxNotes;