Chapter 8. Using Dynamic SQL

 

This chapter covers

  • Introduction to Dynamic SQL
  • Simple Dynamic SQL example
  • Advanced Dynamic SQL
  • Future directions

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 occasions 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 statement, 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 statements 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

8.2. Getting familiar with the dynamic tags

8.3. A complete simple example

8.4. Advanced Dynamic SQL techniques

8.5. Alternative approaches to Dynamic SQL

8.6. The future of Dynamic SQL

8.7. Summary