Using Dynamic SQL - iBATIS in Action

Java Reference

In-Depth Information

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).