How to select a BLOB column from a database using iBatis

Question

One column of the table has a BLOB data type (Oracle 10g). We have a simple selection query made through iBatis to select a BLOB column and display it using Struts2 and JSP.

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

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

Should I mention any class like Handler for a Blob column? We are currently getting an error indicating a column type mismatch.

Note. This column is selected and displayed in a java bean that has an attribute of type java.sql.Blob

Answer 1

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

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

Read more about typeHandlerCallback here .

Answer 2

No need to create typeHandler. For Oracle, jdbctype is BLOB

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

Executing "bytes" as a byte [].

Important: in select sql you should set jdbcType like this:

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

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

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

Answer 3

I found someone who does this here .

For CLOB :

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

For BLOB :

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

I'm still looking for it to work with C #!

Answer 4

I have no problem using INSERT, my problems are when I made a SELECT of type blob. I am using Oracle 9i, and here is how I did it:

• Add the Oracle JDBC driver to your project, you will need mybatis dependencies. 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 a custom BaseTypeHandler to read bytes [] from the 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 a package of type handlers 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 BLOB 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); } 

Hope this helps. This is an adaptation of this excellent answer: this is an adaptation of this excellent answer: fooobar.com/questions/1429786 / ....