http://opensource2.atlassian.com/confluence/oss/display/IBATIS/How+do+I+reuse+SQL-fragments
When writing SqlMaps, you often encounter duplicate fragments of SQL, for example a FROM-clause or constraint-statement; iBATIS offers a simple yet powerful tag to reuse them. For the sake of simplicity, let's assume we want to get some items and we want to do a count on them.
Normally, you would write something like this:
<select id="selectItemCount" resultClass="int"> SELECT COUNT(*) AS total FROM items WHERE parentid = 6 </select> <select id="selectItems" resultClass="Item"> SELECT id, name FROM items WHERE parentid = 6 </select>
To eliminate this duplication, we use the tags <sql> and <include>. The <sql>-tag contains the fragment to reuse, the <include>-tag to include such a fragment:
<sql id="selectItem_fragment"> FROM items WHERE parentid = 6 </sql> <select id="selectItemCount" resultClass="int"> SELECT COUNT(*) AS total <include refid="selectItem_fragment"/> </select> <select id="selectItems" resultClass="Item"> SELECT id, name <include refid="selectItem_fragment"/> </select>
The <include>-tag is namespace-aware so you can refer to fragments even when they are located in another map (however, due to the way iBATIS loads the SqlMaps, the included fragment should be loaded before the including statement).
The fragments are included and processed on query-execution so parameters can be used too:
<sql id="selectItem_fragment"> FROM items WHERE parentid = #value# </sql> <select id="selectItemCount" parameterClass="int" resultClass="int"> SELECT COUNT(*) AS total <include refid="selectItem_fragment"/> </select> <select id="selectItems" parameterClass="int" resultClass="Item"> SELECT id, name <include refid="selectItem_fragment"/> </select>