iBATIS DataMapper
Added by Nathan Maves, last edited by Chadwick on Jun 21, 2009  (view change) show comment
Labels: 
(None)


Here is an example of how to use the Custom Type Handler (CTH) feature of iBatis with large objects (LOB) such as BLOB's (Binary) and CLOB's (Character). As of release 2.0.9 the iBatis framework has the default CLOB and BLOB type handlers included. The example below was done for Oracle but should work for any database with a well written JDBC driver. Make sure that you do not use the thin driver supplied from Oracle. You need to use the latest ojbc14.jar.

The example below was not the intended way to use CTH's but it works great for me!

First lets take a look at the table.

Report.sql
REPORT {
   id              varchar2(5),
   name            varchar2(25),
   description     varchar2(1000),
   data            BLOB
   }

Next we continue by creating a plain old java object (POJO) to represent this table.

Report.java
/*
   * Report.java
   *
   * Created on March 23, 2005, 11:00 AM
   */
   package reporting.viewer.domain;
   
   /**
   *
   * @author Nathan Maves
   */
   public class Report {
   
   /**
   * Holds value of property id.
   */
   private String id;
   /**
   * Holds value of property name.
   */
   private String name;
   /**
   * Holds value of property description.
   */
   private String id;
   /**
   * Holds value of property data.
   */
   private byte[] data;
   
   
   //Standard accessors and mutators
   
   public byte[] getData() {
   return this.data;
   }
   
   public void setData(byte[] data) {
   this.data = data;
   }
   }

Now that the easy stuff is completed let connect both the database and the POJO together using iBatis.

Report.xml
<typeAlias alias="Report" type="reporting.viewer.domain.Report"/>
   
   <resultMap class="Report" id="ReportResult">
   <result column="id" property="id" />
   <result column="name" property="name" />
   <result column="description" property="description" />
   <result column="data" property="data" jdbcType="BLOB"/>
   </resultMap>
   
   <select id="getReportById" parameterClass="string" resultMap="ReportResult">
   SELECT
   *
   FROM
   REPORT
   WHERE
   id = #value#
   </select>
   
   <insert id="insertReport" parameterClass="Report">
   INSERT INTO
   REPORT (
   id,
   name,
   description,
   data
   )
   values (
   #id#,
   #name#,
   #description#,
   #data#
   )
   </insert>
   
   <update id="updateReport" parameterClass="Report">
   UPDATE REPORT set
   name = #name#,
   description = #description#,
   data = #data#
   WHERE
   id = #id#
   </update>

As you can see there is nothing special that you need to do.

When working with a CLOB the only that the you need to change is the property in your bean. Just change byte[] to java.lang.String.

Data size bigger than max size for this type: ????

Some of the older jdbc drivers for Oracle have trouble with Strings that are larger then 4k. The first step to correct this issue it to get a jdbc driver from Oracle that is newer then 10g Release 2. This driver will work with both 9i and 10g databases. If you are stuck with an older driver you can try to set a driver property. The property is SetBigStringTryClob=true. If you are using the SimpleDataSource with iBatis use the follow line in the config file.

<property name="Driver.SetBigStringTryClob" value="true"/>

Data size always 86 bytes?

If you find that the length of your byte[] is always 86, check that you have the jdbcType="BLOB" or jdbcType="CLOB" in your result map.

Please add to phrase "The example below was done for Oracle" 10g as described in "Database Specific Information". It's very confusing. I've spent some time trying to make this example works under Oracle 9i.

Posted by Anonymous at May 26, 2005 01:54

Hey All,

I am getting a byte array containing garbled characters in my implementation. Does anyone have any ideas as to what I am doing incorrectly? I am using JDeveloper 10.

Thanks!
Sam

I wanted to see what is in the event_text so I printed it out:

ErrorMainDetail errmainDetail = ((ErrorMainDetail) errorMainDetail.get(0));
byte[] bytes = errmainDetail.getEvent_text();
logger.debug("String errorMainDetail: " + new String(bytes));

My SQL Map looks like:

<resultMap id="error_main_detail_result" class="ErrorMainDetail">
<result property="error_id" column="error_id"/>
<result property="request_id" column="request_id"/>
<result property="master_bpid" column="master_bpid"/>
<result property="location" column="location"/>
<result property="event_text" column="event_text" jdbcType="CLOB"/>
.....

Posted by Anonymous at Jun 01, 2005 18:26

It seams to me that CLOB doesn't work the way it is supposed to

-Henry

Posted by Anonymous at Jun 09, 2005 13:57

Should this work for Oracle 9i or not?

Posted by Anonymous at Jun 15, 2005 14:24

how max length(byte[length]) is?

the system is outofmemory when stream.available()>53477376

FileInputStream stream = new FileInputStream(file);
System.out.print(stream.available());
byte[] bb = new byte[stream.available()];
stream.read(bb);
stream.close();
return bb;
---------------------posted by abc

Posted by Anonymous at Jul 11, 2005 21:52

code bug!
/**

  • Holds value of property description.
    */
    private String id;
    not id – is description
Posted by Anonymous at Jul 11, 2005 22:08

I am getting inconsistent results right now using Oracle 9i (ojdbc14.jar) and jdbcType=CLOB in parameter maps and result maps.

The jdbcType=CLOB should map to a Java String object and not a byte[] (as it does for BLOB), and I am able to see it work properly with CLOB-String data type mappings.

Posted by jv otis at Jul 13, 2005 16:29

In case you want to use Map to hold BLOB result, use something like this. Guess, this is undocumented way of using byte[] type-handler.

<resultMap id="NoteResult" class="java.util.HashMap">
      <result column="NOTE_ID" property="NOTE_ID"/>
      <result column="CAT_ID" property="CAT_ID"/>
      <result column="WKR_ID" property="WKR_ID"/>
      <result column="NOTE_BODY" property="NOTE_BODY" jdbcType="BLOB" javaType="[B"/>
      <result column="USER_ID" property="USER_ID"/>
      <result column="NOTE_DATE" property="NOTE_DATE"/>
      <result column="NOTE_DESC" property="NOTE_DESC"/>
      </resultMap>
      <select id="getNote" parameterClass="int" resultMap="NoteResult">SELECT * FROM NOTES WHERE NOTE_ID = #NOTE_ID#</select>

Cheers,

Sudhaker (http://sudhaker.com

Posted by Sudhaker Raj at Mar 29, 2006 14:53

Should this work for Oracle 8.1.7 or not?

This <property name="Driver.SetBigStringTryClob" value="true"/> is set in sql-map-config.xml?

I am currently using classes12dms.zip, should I change it to ojdbc14.jar?

Cheers,
Jess.

Posted by stones2u at Jul 06, 2006 20:46

When BLOB Data Length 9,270KB

<exception message>
Error executing query 'GetReferenceData' for object.
Cause: Non-negative number required.\r\nParameter name: count

but, No problem 1,000 KB size
use mapper dao method : ExecuteQueryForObject

???? T.T

Posted by DongJin, Park at Jul 20, 2006 03:34

Hi guys,

We have faced the problem of using CLOBs and BLOBs with iBatis. In summary, we have done the following:

  • Upgrade iBatis version to v2.2. Previously we were using v2.0.5
  • Upgrade Oracle driver to 10.2.0.2.0.  (ojdbc14.zip).  Previously we were using 9.0.2.0.0 with classes12.jar.  Do not forget to remove old driver from your project, it will cause annoying confusions!!
  • Use byte[] type as explained in above posts

Currently we are able to use CLOB for big text fields and BLOB to store images and files.

Hope this help u

Posted by Fran Jiménez at Dec 14, 2006 06:23

Has anyone successfully integrated iBATIS with Spring to call an Oracle stored procedure that han an output parameter of type CLOB?

 Sample sqlMap:

 <sqlMap namespace="Security">      <parameterMap id="getUserParameters" class="map" >            <parameter property="username" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>            <parameter property="xml" jdbcType="CLOB" javaType="java.lang.String" mode="OUT" typeHandler="org.springframework.orm.ibatis.support.ClobStringTypeHandler"/>      </parameterMap>       <procedure id="getUser" parameterMap="getUserParameters" resultClass="java.lang.String">            {call WEB.PKG_ADMIN.PR_GET_USER(?,?)}

      </procedure>
</sqlMap> 

When I call the java function that, in turn, calls this stored proc, iBATIS throws a nested exception claiming retrieving LOBs from a CallableStatement is not supported:

org.springframework.jdbc.UncategorizedSQLException : SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [0];
--- The error occurred in com/merlinpb/dao/ibatis/maps/Security.xml.
--- The error occurred while applying a parameter map.
--- Check the Security.getUserParameters.
--- Check the output parameters (retrieval of output parameters failed).
--- Cause: java.sql.SQLException: Retrieving LOBs from a CallableStatement is not supported; nested
exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/merlinpb/dao/ibatis/maps/Security.xml.
--- The error occurred while applying a parameter map.
--- Check the Security.getUserParameters.
--- Check the output parameters (retrieval of output parameters failed).
--- Cause: java.sql.SQLException: Retrieving LOBs from a CallableStatement is not supported

 Anyone have any thoughts about this or is the exception being thrown the answer to my question...CLOBs as out parameters in procedure just isn't supported?  That would kind of stink being I was hoping to use iBATIS but every call I make the database is to a stored procedure with out parameters of type CLOB...not my decision but one I have to live with.  I can't seem to find ANYTHING about this combination of elements in the documentation, thie WIKI, Spring's site and forums, etc...

Thanks for any insight!

Rob

Posted by Robert L. Brueckmann at Dec 28, 2006 12:32

Who can help me????

SqlMap.xml

<resultMap id="get-htxx-result" class="com.suypower.yxsj.yhdacx.entities.YhDacxHtEntity">
<result property="bhtmb" column="BHTZYMB" jdbcType="BLOB" javaType="[B"/>
</resultMap>
<statement id="getHtmb" parameterClass="java.util.Map" resultMap="get-htxx-result">
select * from db2.YWGYGYDHT where GYHTBH='0100000505011'
</statement>

YhDacxHtEntity.java
private byte []bhtmb;

public byte[] getBhtmb()

Unknown macro: { return bhtmb; }

public void setBhtmb(byte[] bhtmb)

Unknown macro: { this.bhtmb = bhtmb; }

Service.java
Map conditionMap=new HashMap(0);
conditionMap.put("querySql",querySql);
YhDacxHtEntity htnr=(YhDacxHtEntity)this.sqlMap.queryForObject("getHtmb",conditionMap);

ErrorMessage:

Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/6000] SQL0351N UnSupport SQLTYPE。 SQLSTATE=56084

at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:185)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:104)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:565)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:540)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:106)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientImpl.java:84)
at com.suypower.yxsj.yhdacx.service.YhdacxHtService.getEntityByZhh(YhdacxHtService.java:57)
at com.suypower.yxsj.yhdacx.service.YhdacxHtService.main(YhdacxHtService.java:65)

Posted by James2007 at Feb 07, 2007 05:31

Hi!, I've a DB Oracle 9i and I want to insert a BLOB using iBatis. I've mapped a BLOB column as follows:

<result column="FICHERO" jdbcType="BLOB" property="fichero" javaType="[B" />

The java class that I mapped here has the property "fichero" as byte[] with getter's and setter's.

The iBatis versión that I'm using is 2.3.0 and the oracle driver ojdbc14.jar (is the version from Oracle 10g Release 2 because I read that 9i's version had errors and I have to use the latest ojdbc14.jar although it was not the version for Oracle 9i).

When I try to insert a BLOB it failed and tell me the follow:

java.sql.SQLException: ORA-00932: inconsistent datatypes: expected BINARY got NUMBER.

I tried to use a typeHandler in the mapping: typeHandler="org.springframework.orm.ibatis.support.BlobByteArrayTypeHandler" . But the result is the same.

¿Can anyone help me? Thanks.

Posted by Abel at Sep 23, 2008 02:42

Hi!, I've a DB Oracle 9i and I want to insert a BLOB using iBatis. I've mapped a BLOB column as follows:

<result column="FICHERO" jdbcType="BLOB" property="fichero" javaType="[B" />

The java class that I mapped here has the property "fichero" as byte[] with getter's and setter's.

The iBatis versión that I'm using is 2.3.0 and the oracle driver ojdbc14.jar (is the version from Oracle 10g Release 2 because I read that 9i's version had errors and I have to use the latest ojdbc14.jar although it was not the version for Oracle 9i).

When I try to insert a BLOB it failed and tell me the follow:

java.sql.SQLException: ORA-00932: inconsistent datatypes: expected BINARY got NUMBER.

I tried to use a typeHandler in the mapping: typeHandler="org.springframework.orm.ibatis.support.BlobByteArrayTypeHandler" . But the result is the same.

¿Can anyone help me? Thanks.

Posted by Abel at Sep 23, 2008 02:42

Hi!, I've a DB Oracle 9i and I want to insert a BLOB using iBatis. I've mapped a BLOB column as follows:

<result column="FICHERO" jdbcType="BLOB" property="fichero" javaType="[B" />

The java class that I mapped here has the property "fichero" as byte[] with getter's and setter's.

The iBatis versión that I'm using is 2.3.0 and the oracle driver ojdbc14.jar (is the version from Oracle 10g Release 2 because I read that 9i's version had errors and I have to use the latest ojdbc14.jar although it was not the version for Oracle 9i).

When I try to insert a BLOB it failed and tell me the follow:

java.sql.SQLException: ORA-00932: inconsistent datatypes: expected BINARY got NUMBER.

I tried to use a typeHandler in the mapping: typeHandler="org.springframework.orm.ibatis.support.BlobByteArrayTypeHandler" . But the result is the same.

¿Can anyone help me? Thanks.

Posted by Abel at Sep 23, 2008 02:42

Hi! In reply to my own post I've discovered my problem. It was in the code generated by abator; in the instruction "insert" puts the function nvl in all columns, including the BLOB column and this is causing, attempting to insert a NUMBER instead of a BLOB (BINARY). I don't know why but seems that nvl function don't handle well the BLOB datatype.

PD: Sorry for the 3 posts I don't know why it has happened I don't wrote 3 posts equals.

Thanks.

Posted by Abel at Sep 25, 2008 01:13

Hi all

In the eclipse I use the thin driver but with the ojbc14.jar

I'm having my transaction manager set to JTA like this
<transactionManager type="JTA" >
<property name="UserTransaction" value="java:comp/UserTransaction"/>
<dataSource type="JNDI">
<property name="DataSource" value="java:stockDS" />
</dataSource>
</transactionManager>

in my bean's result map I have this
<result property="pdfdata" column="pdfdata" jdbcType="BLOB" javaType="[B" />

inserting was ok
the problem is when I retrieve the data from db, I have the 86 bytes

I tried adding the property String example inside the transactionManager tag and changing the byte[] to string in the bean, but I have a null value retrieved

any ideas what I'm missing ?

Posted by Betto McRose at Jan 02, 2009 08:58; last updated at Jan 02, 2009 09:28

Can someone please help me with using ibatis to call Oracle stored procs/functions that take in parameters of type CLOB/BLOB and in some cases return ref cursors/OUT parameters of type BLOB/CLOB.
A very brief mapping of how to accomplish this task would be very helpful.I have looked at many places but have not found any examples.Even here on this page there is no reply/response to Rob's query.

Pls help.Thx v much.

Posted by Chetan at Oct 09, 2009 07:21

In case of CLOB Data..
Just Select the cloumn with TO_CHAR().
(Mine is Oracle 9i and worked well)

Posted by Ashley Shin at Dec 21, 2009 18:18
Site running on a free Atlassian Confluence Open Source Project License granted to OSS. Evaluate Confluence today.
Powered by Atlassian Confluence, the Enterprise Wiki. (Version: 2.5.5 Build:#811 Jul 25, 2007) - Bug/feature request - Contact Administrators