Ibatis Memo iBATISSQLOracle

Reusing SQL Fragments
we use the tags <sql></sql>and <include></include>. The <sql></sql>tag contains the fragment to reuse, the <include></include>tag includes such a fragment in a statement. For example:

1. <sql id="selectItem_fragment">  
2. FROM items   
3. WHERE parentid = 6  
4. sql>  
5.   
6. <select id="selectItemCount" resultClass="int">  
7.   SELECT COUNT(*) AS total   
8.   <include refid="selectItem_fragment"/>  
9. select>  
10.   
11. <select id="selectItems" resultClass="Item">  
12. SELECT id, name   
13.   <include refid="selectItem_fragment"/>  
14. select>  

The fragments are included and processed on query-execution so parameters can be used too:

1. <sql id="selectItem_fragment">  
2.   FROM items   
3.   WHERE parentid = #value#   
4. sql>  
5.   
6. <select id="selectItemCount" parameterClass="int" resultClass="int">  
7.   SELECT COUNT(*) AS total   
8.   <include refid="selectItem_fragment"/>  
9. select>  
10.   
11. <select id="selectItems" parameterClass="int" resultClass="Item">  
12.   SELECT id, name   
13.   <include refid="selectItem_fragment"/>  
14. select>  

Auto-Generated Key

1. >  
2. <insert id="insertProduct-ORACLE" parameterClass="com.domain.Product">  
3.     <selectKey resultClass="int" >  
4.         SELECT STOCKIDSEQUENCE.NEXTVAL AS ID FROM DUAL   
5.     selectKey>  
6.     insert into PRODUCT (PRD_ID,PRD_DESCRIPTION)   
7.     values (#id#,#description#)   
8. insert>  

The selectKey statement is executed before the insert statement if it is placed before the insert SQL, otherwise the selectKey statement is executed after the insert statement.

It improve in 2.0

1. <insert id="insertProduct-ORACLE-type-specified" parameterClass="com.domain.Product">  
2.     insert into PRODUCT (PRD_ID,PRD_DESCRIPTION)   
3.     values (#id#,#description#)   
4.     <selectKey resultClass="int" type="pre" >  
5.         SELECT STOCKIDSEQUENCE.NEXTVAL AS ID FROM DUAL   
6.     selectKey>  
7. insert>  

Substitution Strings

select * from $tableName$

With this syntax, iBATIS will substitute the value of the “tableName” property into the SQL before the statement is prepared. With this support, you can substitute string into any part of an SQL statement.Important Note 1: This support will only substitute Strings, so it is not appropriate for complex data types like Date or Timestamp.
Important Note 2: If you use this support to alter a table name, or a column list, in an SQL select statement, then you should always specify remapResults=“true”.

iBATIS provides syntax for substituting strings into SQL before the statement is prepared. You can use this support to generate a dynamic SQL statement. An example of the substitution syntax is as follows: