iBATIS DataMapper
Added by Brandon Goodin, last edited by John Beatty on Oct 07, 2006  (view change) show comment

Environment Specific Solutions



As of release 2.0.9, iBATIS includes default BLOB/CLOB handlers. If you are using an older version of Oracle (pre-10g), then you might have to write your own Custom Tag Handler to access the proprietary Oracle API to work with LOBs.

Some selectKey examples

<insert id="insert" parameterClass="customer">
   <selectKey resultClass="int" keyProperty="id">
   select someSequence.NEXTVAL as "id" from dual
   insert into Customer (id, name)
   values (#id#, #name#)
An example of using selectKey with a parameterMap.
   The selectKey stanza is executed and the result is used to set the property 'pkey' of the parameter object,
   then the parameter map is invoked.
   <insert id="insert2" parameterClass="Entity" parameterMap="insert-paramMap">
   <selectKey resultClass="int" keyProperty="pkey"> SELECT seq.nextval FROM DUAL </selectKey>
   INSERT INTO EntityTable (PKEY, DATA) VALUES (?, ?)


I was having trouble using an Oracle LONG column with an iBatis sqlmap.  I had defined the jdbcType as a LONGVARCHAR in the sqlmap, but iBatis was still attempting to call getCLOB on the oracle jdbc driver's LongAccessor class.  The solution to this was to explicitly use the StringTypeHandler on the column, as follows:

Using the StringTypeHandler on an Oracle LONG column - attempt #1 (failed)

<result column="MY_VARCHAR_COLUMN" property="myStringProperty" jdbcType="VARCHAR" />
   <result column="MY_LONG_COLUMN" property="myStringProperty2" jdbcType="LONGVARCHAR" typeHandler="com.ibatis.sqlmap.engine.type.StringTypeHandler"/>

This correctly resulted in a call to LongAccessor.getString() in the oracle jdbc driver.  However, it threw up a new problem, "Stream has already been closed". 

The solution was to change the ordering of the columns in the result map so that the LONG column was accessed first, as follows:

Using the StringTypeHandler on an Oracle LONG column - attempt #2 (succeeded)

<result column="MY_LONG_COLUMN" property="myStringProperty2" jdbcType="LONGVARCHAR" typeHandler="com.ibatis.sqlmap.engine.type.StringTypeHandler"/>
   <result column="MY_VARCHAR_COLUMN" property="myStringProperty" jdbcType="VARCHAR" />

So in summary, to use an Oracle LONG column, the following worked for me:

  1. Declare the jdbcType attribute as "LONGVARCHAR" in the result map
  2. Use a typeHandler attribute of "com.ibatis.sqlmap.engine.type.StringTypeHandler"
  3. Ensure the LONG column is the first declared column in the result map.

Oracle Paging

An example of how to select a subset of an ordered list. The keys added to the parameter map over and above the abator generated ones (used by the 'Where_Clause' sql) are FIRST (0-based, hence the "+ 1") and COUNT (no of entries to return).

<select id="selectPagedIds" resultClass="int" parameterClass="java.util.Map">
   select * from (
   select a.*, ROWNUM rn from (
   select ID from Schema.MYTABLE
   <include refid="Where_Clause"/>
   order by $ABATOR_ORDER_BY_CLAUSE$) a
   WHERE ROWNUM <![CDATA[ <= ]]> (#FIRST# + #COUNT#))
   WHERE rn <![CDATA[ >= ]]> (#FIRST# + 1)


As of release 2.2.0, iBATIS includes a query timeout funcionality.
You can specify both a global timeout

<settings defaultStatementTimeout="2" />

or a per query timeout

<statement ... timeout="2">

When the timeout expires, it throws a SQL Exception "ORA-01013 : user requested cancel of current operation".

Tested with:

  • Oracle 9i release 2 drivers.


Stored Proc/UNCHAINED mode

By default, Sybase doesn't allow procs to be run in a transactional context. You can work around this problem a few different ways:

1. Use your own Connection and .setAutoCommit(true). Pass this connection into the SqlMapClient.setUserConnection(Connection) method. You're responsible for closing the connection afterwards. If you like, you can get the Connection from the same DataSource by calling SqlMapClient.getDataSource(). Although this seems a bit "roundabout", it works.

2. In Sybase, use the following command to change all stored procedures into proper transaction mode.

sp_procxmode <stored procedure> , "anymode"

3. Use the longer approach proposed by Scott Severtson: ["Scott's Sybase Proc Solution"]



Older versions of MySQL don't support transactions. Make sure you're using a modern MySQL release and the latest drivers. iBATIS won't work without Transaction support (an RDBMS without TX support is kind of silly anyway).

Some selectKey Examples

<insert id="insertFolder" parameterClass="folder">
   INSERT INTO folder (parent_id, owner, foldername)
   VALUES (#parentId#, #owner#, #foldername#)
   <selectKey resultClass="int" keyProperty="folderId">


Container Managed Transactions (CMT) Configuration

This transaction configuration is appropriate for WebSphere when using EJBs and container managed transactions (CMT).

<transactionManager type="EXTERNAL">
   <property name="SetAutoCommitAllowed" value="false"/>
   <dataSource type="JNDI">
   <property name="DataSource" value="java:comp/env/..."/>

Bean Managed Transactions or Servlets Without EJBs

This transaction configuration is appropriate for WebSphere when your are using WebSphere connection pooling, but not using EJBs (servlets only). It is also appropriate when using WebSphere connection pooling, EJBs, and bean managed transactions.

<transactionManager type="JTA" commitRequired="true">
   <property name="UserTransaction" value="java:comp/env/UserTransaction"/>
   <dataSource type="JNDI">
   <property name="DataSource" value="java:comp/env/..."/>

For more information on using WAS with iBATIS read this article on the developerworks website:


Some selectKey Examples

<insert id="insert">
   <selectKey keyProperty="accountId" resultClass="int">
   SELECT nextVal('account_accountid_seq')
   INSERT INTO Account (accountId, username, password)
   VALUES (#accountId#, #username#, #password#)

If accountId is defined as a 'serial' column in your schema, you can allow the accountId to default to the next number in the sequence and use Postgres's currval function to retrieve the generated ID.

<insert id="insert">
   INSERT INTO Account (accountId, username, password)
   VALUES (#accountId#, #username#, #password#)
   <selectKey keyProperty="accountId" resultClass="long">
   SELECT currval('account_accountid_seq')

DB400 (AS400 / iSeries)

Invalid Usages of the Parameter Marker ?

DB400 has some quirks about where the ? can go in the statement.   For instance, the ? cannot be "an operand of a scalar function."  This means that DB400 will complain about using functions like UPPER or TRIM. This is a quirk with using PreparedStatements, and therefore, SqlMaps.  Below is an example of how to get scalar functions to work, much like the example of using LIKE in the FAQ section.

For more info, see  http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzala/rzalaml.htm  Look for SQL code SQL0418.

<select id="company" resultMap="getCompanyInfo" parameterClass="java.lang.String">
   The 400 does not allow ? in scalar functions, like UPPER(?).
   This means we must use $value$ instead of #value#.
   We must rely on Java to escape the value before passing it in to SqlMaps
   by using StringEscapeUtils.escapeSql(value) to avoid injection attacks.
   WHERE UPPER(USER) = UPPER('$value$')

This correctly resulted in a call to LongAccessor.getString() in the oracle jdbc driver. However, it threw up a new problem, "Stream has already been closed".

4-td real working solution - use -Doracle.jdbc.useFetchSizeWithLongColumn=true

  • Oracle note
    • Note 736099.1 JBO-27022 Exception Error during Appraisal Creation
  • Description
    • Oracle Database JDBC Developer's Guide and Reference,11g Release 1 (11.1) -> 23 Performance Extensions -> Oracle Row-Prefetching Limitations

      If you are using the JDBC Thin driver, then in the case where only zero or one row is expected, use the useFetchSizeWithLongColumn connection property, because it will perform PARSE, EXECUTE, and FETCH in a single round-trip.

      Tuning of the prefetch size should be done along with tuning of memory management in your JVM under realistic loads of the actual application.

    • Oracle┬« Database JDBC Developer's Guide and Reference 10g Release 1 (10.1) -> 4 Basic Features -> Table 4-2 Connection Properties Recognized by Oracle JDBC Drivers
      useFetchSizeWithLongColumn String (containing boolean value) "true" causes JDBC to prefetch rows even when there is a LONG or LONG RAW column in the result. By default JDBC fetches only one row at a time if there are LONG or LONG RAW columns in the result. Setting this property to true can improve performance but can also cause SQLExceptions if the results are too big.
Posted by Serge Isaev at Jun 26, 2009 05:42
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