Using Dynamic SQL - iBATIS in Action - page 178

String s. Since we are using a simple List directly, we will use the iterate tag. The
iterate tag will traverse through the List of String s while its body creates the crite-
ria that looks for the provided term in the categoryId , name, and description. We
need to be sure to add the conjunction attribute with a value of OR . The conjunc-
tion will join each set of term criteria together. Note that it is smart to name your
mapped statements in such a way that it adequately describes the function of the
SQL contained within it. In this case, we named our mapped statement search-
ProductList . When you read the name of the mapped statement, the statement's
purpose becomes immediately clear.

sqlMap.queryForPaginatedList(
"searchProducts", parameterObject, PAGE_SIZE);
As a final requirement, we need to make sure that we return only four records at a
time. To accomplish this, we will call our statement using the queryForPaginat-
edList() method, which takes a pageSize parameter. This will allow us to control
how many records are returned.
We have now stepped through a simple example and learned how to plan and
develop our Dynamic SQL . It is important to remember that Dynamic SQL should
be used with a single purpose that requires multiple options; avoid using it to
accomplish multiple purposes. In our example, the purpose was to address how a
user can select products. Bear in mind that Dynamic SQL was invented to make
the complex simpler and not the simple more complex. Next, let's move on to a
more advanced usage of Dynamic SQL .

8.4 Advanced Dynamic SQL techniques

In this example, we will use a shopping cart application that needs to provide a way
for customers to search for products using more detail. We'll build on some of the
structures involved in our previous examples and step up the complexity. Again
we'll apply the analysis approach that you learned about in the previous example.

8.4.1 Defining the resulting data

Let's define our output in generic terms. Our shopping cart application requires
that a list of products be displayed when a set of criteria are searched on by a user
(see figure 8.4). The output is determined based on specific selected criteria
involving the categories, products, and manufacturers. The products must be dis-
played in increments of four products per page. Our Dynamic SQL must be able
to produce the paged product list and handle the complex variations of values the
user may enter.