Java Reference
In-Depth Information
WHERE
lower(name) like 'adventure%' OR
lower(categoryid) like 'adventure%' OR
lower(description) like 'adventure%' OR
lower(name) like 'Deus%' OR
lower(categoryid) like 'deus%' OR
lower(description) like 'deus%'
We have now defined our input, output, and table, and it is time to mock up our
SQL
statement. Once you have the previous information it is quite simple to con-
struct your
SQL
. The
SELECT
statement will fulfill our need to return a list of prod-
ucts. The
WHERE
clause provides the needed criteria for querying based on the
supplied terms again the name,
categoryId
, and description.
8.3.4
Applying Dynamic SQL tags to static SQL
Let's examine our mocked-up
SQL
statement so we can dissect it and determine
where we need to introduce dynamic tags. Since the
SELECT
clause is static, we will
not need to make any dynamic alterations to it. The
WHERE
clause is where we will
have to make dynamic adjustments. Listing 8.9 shows the
SQL
that we will be look-
ing at.
Listing 8.9
Dynamic SQL
<select id="searchProductList" resultClass="product" >
SELECT
PRODUCTID,
NAME,
DESCRIPTION,
IMAGE,
CATEGORYID
FROM PRODUCT
<dynamic prepend="WHERE">
<iterate property="keywordList" conjunction="OR">
lower(name) like lower(#keywordList[]#) OR
lower(categoryid) like lower(#keywordList[]#) OR
lower(description) like lower(#keywordList[]#)
</iterate>
</dynamic>
</select>
Since the dynamic tags utilize the parameter that is passed into the mapped state-
ment, we need to consider what it is here and how we will use the dynamic tags
with it. The parameter that will be provided to the mapped statement is a List of
