Dashboard > iBATIS DataMapper > Home > Frequently Asked Questions > How do I reuse SQL-fragments
How do I reuse SQL-fragments
Added by Niels Beekman, last edited by Niels Beekman on Dec 30, 2005  (view change)
Labels: 
(None)


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>

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