Java Reference
In-Depth Information
Listing 8.1
Example of Dynamic WHERE clause
…
<select id="getChildCategories" parameterClass="Category"
resultClass="Category">
SELECT *
FROM category
<dynamic prepend="WHERE ">
<isNull property="parentCategoryId">
parentCategoryId IS NULL
</isNull>
<isNotNull property="parentCategoryId">
parentCategoryId=#parentCategoryId#
</isNotNull>
</dynamic>
</select>
…
The anatomy of Dynamic
SQL
always begins with a parent tag. A parent tag can be
any of the Dynamic
SQL
tags. In this case we are using the
<dynamic>
tag as the
parent. The
<dynamic>
tag does not evaluate any values or state like the other
Dynamic
SQL
tags do. It will generally use only the
prepend
attribute, which will
prefix the attribute's value to the resulting body content of the
<dynamic>
tag. In
our example, the value
WHERE
will be prefixed to any resulting
SQL
produced by
the processing of the nested Dynamic
SQL
tags.
The body of the parent tag may contain either simple
SQL
syntax or other
Dynamic
SQL
tags. You can see in the example that we have
<isNull>
and
<isNotNull>
tags nested in the body of the
<dynamic>
tag. Our concern here is
that the appropriate
SQL
be part of the
WHERE
criteria depending on the null state
of the
parentCategoryId
property of the
Category
parameter class (
parameter-
Class="Category"
).
At this point it is important to note how the
prepend
attribute is evaluated for
prefixing. If the body content were to result in no text being produced, then the
prepend
value would be ignored. In order for the
prepend
attribute to be prefixed,
there needs to be resulting
SQL
to prepend to. In our scenario there will always be
resulting
SQL
. In other cases where the body resulted in no content being pro-
duced, the
WHERE
value in the
prepend
attribute would simply be ignored.
One of the benefits of Dynamic
SQL
is that it enhances the reusability of your
SQL
code. Had we not used Dynamic
SQL
in this example, we would have found
ourselves writing two select statements to accommodate the scenario. We would also
be required to push the examination of our category object's
parentCategoryId
