Java Reference
In-Depth Information
Listing 8.1
Example of Dynamic WHERE clause
<select id="getChildCategories" parameterClass="Category"
FROM category
<dynamic prepend="WHERE ">
<isNull property="parentCategoryId">
parentCategoryId IS NULL
<isNotNull property="parentCategoryId">
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
Search WWH ::

Custom Search