In chapter 4 we discussed how to write simple static SQL . Static SQL only requires
that values be assigned via the property ( #...# ) or literal ( $...$ ) syntax. Although
most of the SQL you will write in iBATIS will likely be static, there are several occa-
sions where it will not remain that simple. For example, you will quickly run into
more involved scenarios where you need to iterate over a list of values for an IN state-
ment, provide users with the ability to determine which columns will be displayed,
or simply have a changing WHERE criterion based on your parameter object's state.
iBATIS provides a set of dynamic SQL tags that can be used within mapped state-
ments to enhance the reusability and flexibility of the SQL .
In this chapter we'll provide you with an understanding of what Dynamic SQL
is, its usefulness, and when best to use it. We'll also give you some comparative
context regarding other solutions you may use for dealing with Dynamic SQL
requirements. In the end you should have a strong understanding of how to add
Dynamic SQL to your arsenal of problem-solving techniques.
Before we examine dynamic tags in detail, let's demonstrate their value by
jumping right into an example of one of the most common situations where you
will need Dynamic SQL : the WHERE clause.
8.1 Dealing with Dynamic WHERE clause criteria
In the following example we are querying a table named
Category from our shopping cart application. The table
column parentCategoryId is a self-referencing column. In
other words, parentCategoryId references categoryId in
the same Category table, as shown in figure 8.1.
The requirements are simple. If the parentCategoryId
property of the passed-in Category object is null, then it
means that we want to query all top-level categories. If the
parentCategoryId property has a non-null numeric value,
then that means that we want to query for all child Cate-
gory objects of the parent Category . The parent Category
is indicated by the parentCategoryId value.
In SQL , an equal sign (=) cannot be used to compare
null equalities. The IS keyword is needed to successfully test for equality with a
NULL . Since we want to use the same SQL statement to handle both NULL and
non- NULL comparisons, we will use Dynamic SQL to accomplish this with one
mapped statement. We will use this mapped statement to examine the anatomy of
Dynamic SQL (listing 8.1).
Figure 8.1 Category