Page Operations
Browse Space
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 {
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 * * 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.
<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.
|
| 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. |
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.
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
)
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.
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
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
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
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()
public void setBhtmb(byte[] 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)
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.