Java Reference
In-Depth Information
The starting point for a search is another form in which the user sets up his or her search criteria. A
simple search form is illustrated in
Figure 15-1
. The search criteria this form defines are collected using
a JSP page and a JavaBean. The search criteria are passed as inputs to a SQL stored procedure of the
form shown in
Listing 15-1
.
Figure 15-1:
Search form
Listing 15-1:
SQL stored procedure to return matching database items
CREATE PROCEDURE SEARCH @BODY VARCHAR(50),
@ZIP VARCHAR(10), @MAKE VARCHAR(50),
@MODEL VARCHAR(50), @ENGINE VARCHAR(50),
@TRANSMISSION VARCHAR(50), @PRICE INT, @YEAR1 INT,
@YEAR2 INT AS SELECT TOP 50 *
FROM VEHICLES
WHERE BODY LIKE @BODY AND
ZIP LIKE @ZIP AND MAKE LIKE @MAKE AND
MODEL LIKE @MODEL AND
ENGINE LIKE @ENGINE AND
TRANSMISSION LIKE @TRANSMISSION AND
PRICE <= @PRICE AND YEAR BETWEEN
@YEAR1 AND @YEAR2;
The stored procedure uses the
LIKE
comparator so that wild cards can be used. This approach allows
a great deal of flexibility in searching the database. The HTML snippet below shows how the
SELECT
element is defined to return the wild card character '%' when the
OPTION
Any
is selected:
<TR>
<TD>Make: </TD>
<TD>
<SELECT name=Make size=1>
<OPTION VALUE="%" SELECTED>Any</OPTION>
