Using Dynamic SQL - iBATIS in Action - page 183

So, how do we call that monster?
queryForPaginatedList(
"Product.searchProductsWithProductSearch",
productSearch, PAGE_SIZE);
As in the previous example, our final requirement is to make sure that we only
return four records at a time. To accomplish this, we call our statement using the
queryForPaginatedList method, which takes a page size parameter. See the previ-
ous example for some pointers on how to use the ranged queryForPaginatedList .
You have now seen a complex example and learned how to perform some
intense Dynamic SQL . Keeping in mind the advice from the previous example, we
had a single purpose for our Dynamic SQL —to provide product listings based on
search criteria entered by the user—and we accomplished that. Although this
example may have looked a bit complex, it would be significantly more difficult to
write this from scratch in Java code. You should now be armed with what you need
to build your own dynamic SQL .
It's important to understand how iBATIS Dynamic SQL compares against other
solutions for handling dynamic SQL . Let's take a brief look at how Dynamic SQL
might be accomplished with a couple of other means.
8.5 Alternative approaches to Dynamic SQL
Dynamic SQL is by no means a new concept. The complex requirements of a con-
ditionally manufactured SQL query have always been a bit of a challenge. In the
past we have had to deal with proprietary internal database approaches that use
stored procedures to gain efficiency in the execution of Dynamic SQL . In other
cases we paid the price of performance by constructing SQL in a more robust pro-
gramming language and passed the query into our database via a driver. In both
cases, the means of constructing that simple SQL string became unwieldy and
increasingly complex. If you are already an iBATIS user, this will be a brief jog down
memory lane that will remind you of what you are missing. If you are new to iBATIS ,
this may be a fresh introductory comparison. Regardless of your background, we
hope that this section will provide fresh insight into your practices and convince
you that iBATIS can reduce much of the complexity you may be experiencing.
We'll share the same SQL statement between the Java code example and the
stored procedure example. Listing 8.13 is a mildly complex select statement that
should be fairly simple to code. Once we have shown how each approach solves
the problem of Dynamic SQL , we will provide a quick summary of how these
approaches compare to using iBATIS .