How to Select a BLOB column from database using iBatis

Question

One of a table's column is of BLOB datatype (Oracle 10g). We have a simple select query executed via iBatis to select the BLOB column and display it using Struts2 & JSP.

The result tag in the iBatis xml file had the jdbctype as java.sql.Blob

<result property="uploadContent" column="uploadcontent" jdbctype="Blob"/>

Should we be mentioning any typeHandler class for Blob column ? Currently we are getting an error stating column type mismatch.

Note: This column is selected and mapped into a java bean who has an attribute of type java.sql.Blob

score 3 · Accepted Answer · answered Aug 21 '12 at 21:26

I think you cannot use native jdbctype for LOB types in Oracle with iBatis. The solution is to create custom typeHandler to handle LOB and then map it like -

<result property="aClassStringProperty" column="aClobColumn" typeHandler="com.path.to.my.ClobTypeHandler"/>

More information on typeHandlerCallback here.

David García González · Answer 2 · 2013-04-22T07:20:43.113

It is not neccesary to create a typeHandler. For Oracle, the jdbctype is BLOB

<result property="bytes" column="COLUMNBLOB"  jdbcType="BLOB" />

Assumming "bytes" as byte [].

The important thing: in the select sql, you must set the jdbcType in this way:

INSERT INTO X (COLUMNBLOB) VALUES #bytes:BLOB#

I noticed that this jdbctype for Postgresql is different. You must set:

<result property="bytes" column="COLUMNBLOB"  jdbcType="BINARY" />

score 0 · Answer 3 · answered Sep 20 '12 at 16:52

I found somebody who deal with this here.

For a CLOB :

<result property="uploadContent" column="obfile" jdbctype="String" />

For a BLOB :

<result property="uploadContent" column="obfile" jdbctype="byte[]" />

I am still looking for it to work with C# !

score 0 · Answer 4 · edited May 23 '17 at 12:04

I dindn't have problems using INSERTs, my problems where when I did SELECT of the blob type. I am using Oracle 9i and this is how I've done:

Add the Oracle JDBC driver to your project, you will need mybatis dependencies too. If you are using Maven:

<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc14</artifactId>
    <version>10.2.0.3.0</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>1.2.1</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.2.3</version>
</dependency>

Add the custom BaseTypeHandler for reading byte[] from Oracle BLOB class:

@MappedTypes(byte[].class)
public class OracleBlobTypeHandler extends BaseTypeHandler<byte[]> {
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, byte[] bytes, JdbcType jdbcType) throws SQLException {
        // see setBlobAsBytes method from https://jira.spring.io/secure/attachment/11851/OracleLobHandler.java
        try {
            if (bytes != null) {
                //prepareLob
                BLOB blob = BLOB.createTemporary(preparedStatement.getConnection(), true, BLOB.DURATION_SESSION);

                //callback.populateLob
                OutputStream os = blob.getBinaryOutputStream();
                try {
                    os.write(bytes);
                } catch (Exception e) {
                    throw new SQLException(e);
                } finally {
                    try {
                        os.close();
                    } catch (Exception e) {
                        e.printStackTrace();//ignore
                    }
                }
                preparedStatement.setBlob(i, blob);
            } else {
                preparedStatement.setBlob(i, (Blob) null);
            }
        } catch (Exception e) {
            throw new SQLException(e);
        }
    }

    /** see getBlobAsBytes method from https://jira.spring.io/secure/attachment/11851/OracleLobHandler.java */
    private byte[] getBlobAsBytes(BLOB blob) throws SQLException {

        //initializeResourcesBeforeRead
        if(!blob.isTemporary()) {
            blob.open(BLOB.MODE_READONLY);
        }

        //read
        byte[] bytes = blob.getBytes(1L, (int)blob.length());

        //releaseResourcesAfterRead
        if(blob.isTemporary()) {
            blob.freeTemporary();
        } else if(blob.isOpen()) {
            blob.close();
        }

        return bytes;
    }

    @Override
    public byte[] getNullableResult(ResultSet resultSet, String columnName) throws SQLException {
        try {
            //use a custom oracle.sql.BLOB
            BLOB blob = (BLOB) resultSet.getBlob(columnName);
            return getBlobAsBytes(blob);
        } catch (Exception e) {
            throw new SQLException(e);
        }
    }

    @Override
    public byte[] getNullableResult(ResultSet resultSet, int i) throws SQLException {
        try {
            //use a custom oracle.sql.BLOB
            BLOB blob = (BLOB) resultSet.getBlob(i);
            return getBlobAsBytes(blob);
        } catch (Exception e) {
            throw new SQLException(e);
        }
    }

    @Override
    public byte[] getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        try {
            //use a custom oracle.sql.BLOB
            BLOB blob = (BLOB) callableStatement.getBlob(i);
            return getBlobAsBytes(blob);
        } catch (Exception e) {
            throw new SQLException(e);
        }
    }
}

Add the type handlers package to mybatis configuration. As you can see, I am using spring-mybatis:

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="typeHandlersPackage" value="package.where.customhandler.is" />
</bean>

And then, you can read byte[] from Oracle BLOBs from Mybatis:

public class Bean {
    private byte[] file;
}

interface class Dao {
    @Select("select file from some_table where id=#{id}")
    Bean getBean(@Param("id") String id);
}

I hope this will help. This is an adaptation of this excellent answer: This is an adaptation of this excellent answer: https://stackoverflow.com/a/27522590/2692914.

How to Select a BLOB column from database using iBatis

4 Answers4

Linked