Using Dynamic SQL - iBATIS in Action - page 184

Listing 8.13 Static SQL mock-up

SELECT *
FROM Category
WHERE
categoryId IN ('ACTADV','SPORTS','STRATEGY') AND
name LIKE ('N%')

8.5.1 Using Java code

Coding in Java is a great thing. But when you mix Java and SQL together, you have
to take care to craft your code in such a way that it retains clarity. As requirements
grow more complex, it will become easy to lose track of where all the pieces are.
Let's take a look at a mildly complex example that uses straight JDBC to assemble
a Dynamic SQL statement and pass it to the database. Listing 8.14 shows the
search criteria that we will use to build our SQL statement.

Listing 8.14 CategorySearchCriteria.java

public class CategorySearchCriteria implements Serializable {

private String firstLetter;
private List categoryIds;
…
// setters and getters
}

Our mildly complex SQL statement will receive an unknown quantity of category
ID s from the categoryIds property of the CategorySearchCriteria . This will be
used to populate the IN statement. The firstLetter property containing a single
alpha character will be provided to perform the search against the first letter of
the category name. In this example, our focus is on the JDBC interactions and
comparing Dynamic SQL solutions. So, we will not expound on anything outside
of that. Listing 8.15 shows how to build the Dynamic SQL using only Java code.

Listing 8.15 CategorySearchDao.java

public class CategorySearchDao {
…
public List searchCategory(
CategorySearchCriteria categorySearchCriteria) {
List retVal = new ArrayList();