Java Reference
In-Depth Information
WHERE
c.categoryId IN ('ACTADV')
AND
p.name LIKE '007'
AND
p.description LIKE '007'
AND
i.name LIKE 'PS2'
AND
i.description LIKE 'PS2'
Now that we know our input needs and what tables are involved, we can mock up
a static
SQL
query. In our static example (listing 8.11), you may have noticed that
the
WHERE
criteria has been expanded to include the largest possible combination
of criteria. It is important to make your static
SQL
as complex as possible in order
to provide a good sense of what the dynamic needs will be. Let's move on to
assembling the Dynamic
SQL
.
8.4.4
Applying Dynamic SQL tags to static SQL
Now that we have constructed our static
SQL
mock-up, let's apply the dynamic
tags. As you can see in listing 8.12, the Dynamic
SQL
has become a bit more com-
plex. Note the use of
<isEqual>
as a top-level tag. It's not necessary to use the
<dynamic>
parent because there's no need for any
prepend
,
open
, or
close
values.
We simply want to either display the content, or not. The same
<isEqual>
tag is
used to determine the inclusion of a
LEFT JOIN
.
Listing 8.12
Dynamic SQL for advanced search
B
Contains opening select tag
<select id="searchProductsWithProductSearch"
parameterClass="productSearch"
resultClass="product" >
SELECT DISTINCT
p.PRODUCTID,
p.NAME,
p.DESCRIPTION,
p.IMAGE,
p.CATEGORYID
FROM Product p
<isEqual property="itemProperties" compareValue="true">
INNER JOIN Item i ON i.productId=p.productId
</isEqual>
<dynamic prepend="WHERE">
C
Contains minimal
SQL statement
Checks to
see if join
needed for
item criteria
D
Contains simple
opening dynamic tag
E
