iBATIS DataMapper
Added by Brandon Goodin, last edited by Chadwick on Jun 21, 2009  (view change) show comment

This is a collaborative development space to discuss the future of dynamic SQL in iBATIS SQL Mapping files. This is a key feature of iBATIS that gives it great power. The current implementation has some limitations that we'd like to resolve by introducing a new and improved Dynamic SQL syntax. This page represents the current state of the decisions and future design.

What are the existing problems/limitations?

  • Inflexible iterators
  • Odd iterator syntax ([] vs. item definition)
  • More flexible variable definition and consistent access

Which new syntax do we want to support?

  • Old school proprietary XML
  • New/improved proprietary XML
  • 3rd Party XML
  • Groovy
  • Velocity
  • JavaScript

How do we ensure consistency accross the Java and .NET versions?

  • Ideas?

Brandon's Proposal

Introducing pluggable scripting languages into the ibatis mix will not provide anything but needless complexity. My feeling is that we need to settle on a scoped EL implementation and make our xml simpler. Following is a recommendation i posted to the developer's list:

I did a little more research on where we can take dyna sql and here is a short proposal.

<isEqual>;<isNotEqual>;<isGreaterThan>;<isGreaterEqual>;<isLessThan>;<isLessEqual> <isPropertyAvailable>;<isNotPropertyAvailable>;<isNull>;<isNotNull>;<isEmpty>;<isNotEmpty> <isParameterPresent>;<isNotParameterPresent>

These could be condensed down to a simpler set of tags:

  • <choose> - for mulitple conditionals. used in conjunction with when and otherwise.
    • attributes: none
  • <when>
    • attributes: prepend, test, append
  • <otherwise>
    • attributes: prepend, append
  • <if> - for single conditionals
    • Attributes: prepend, test, append
  • <foreach> - for iterating over arrays. Also, i was thinking we can place a test attribute on this to gain a for loop [for(x=0; x<y; x++)] effect.
  • attributes:
    • test
    • begin
    • end
    • item
    • step
    • open
    • close
    • conjuction
    • prepend
    • append
  • <while> - for looping a "do while" condition.
  • attributes:
    • test
    • open
    • close
    • conjuction
    • prepend
    • append

In light of making this cross platform it may a good idea to use JEXL as a reference point and implement our own el with ANTLR.

The EL should be simple and used for value extraction, boolean tests, and math. It should not have control structure syntax like for, if, while.

ANTLR - http://www.antlr.org/ – ANTLR looks to be the most popular and cross-language (for our C# friends). A quote from the ANTLR website - "ANTLR currently generates Java, C#, and C++. A prototype Python generator is almost ready."

Can you provide a compelling reason to need a while tag? The foreach and it seem similar enough to only require one of them to exist (I'd pick foreach to minimize the potential for infinite loops).

Also, a nice addition would be custom tag handlers - so if I need a new dyna tag, I can write it, plug it in, and add it to JIRA for others.

Posted by Larry Meadors at May 31, 2005 07:09

Does the foreach loop support some kind of declared item, like struts logic:iterate tag declares a Bean with the id attribute? If this is supposed to be the item attribute, how can we make a difference in the foreach tag between the object in list and the list itself? I suppose, in this case, that "item" would mean the object in the list and "item[]" would mean the list itself.

For the while loop, it would be a good idea to rename it to doWhile, since it does at least a loop (if I undestood your explanation correctly). It is a good idea, but if you include this kind of loop, we should also include some scripplet code to make sure the user can end the loop at some point or the condition is likely to be true forever.
I personnaly dislike the idea of scripplet code, but it is likely to be necessary in a long term view.

The custom tag handler idea of Larry Meadors seems nice too. Maybe this could appear in a future version of iBatis (maybe a version 3 or something).

Posted by Christian Poitras at May 12, 2006 16:33

can we use

<include refid="#value#"/>


<include refid="$value$"/>


to provide a uniform listing of database records, there is a need of few reusable java classes to be developed for pagination, sorting and filtering of records.

 for example

 <!- A reusable block ->

<sql id="allproductcategoriesinclude">
      SELECT * FROM tbl_product_category ORDER BY category_id

 <!-reusable block  Used to retrieve all ->

  <statement id="selectall" parameterClass="com.ibatis.model.ProductCategory" resultMap="abatorgenerated_ProductCategoryResult">
  <include refid="allproductcategoriesinclude" />

<!- This block may get a sql map id and return the count .->

  <select id="selectPCCount" parameterClass="java.lang.String" resultClass="int">
      SELECT COUNT AS total FROM (
      <include refid="$value$"/> 
      ) a

 Because a single reusable sql element can be used multiple times.

As of now, only static reference is accepted in <include ref="">

And is there any way to get the last executed SQL statement in iBATIS?.

Posted by sph at Jun 10, 2009 06:24
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