JavaServer Pages, 3rd Edition

Searching for Rows Based on Partial Information

Let’s move to the other part of the application, in which a user can search for an employee based on a partial first name, last name, and department name. The first page, search.html, contains a form for entering the search criteria, shown in Figure 12-6.

Figure 12-6. Search criteria form

The three fields in the search.html page are named firstName, lastName, and dept, and when the user clicks the Search button, the find.jsp page is invoked with the information the user entered in the corresponding request parameters. Example 12-3 shows the complete find.jsp page.

Example 12-3. Search based on partial information (find.jsp)

<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
  
<%-- 
  Execute query, with wildcard characters added to the
  parameter values used in the search criteria
--%>
<sql:query var="empList" scope="request">
  SELECT * FROM Employee 
    WHERE FirstName LIKE ?
      AND LastName LIKE ?
      AND Dept LIKE ?
    ORDER BY LastName
  <sql:param value="%${param.firstName}%" />
  <sql:param value="%${param.lastName}%" />
  <sql:param value="%${param.dept}%" />
</sql:query>
  
<jsp:forward page="list.jsp" />

As you probably expected, the <sql:query> action searches for the matching employees. But here, the SELECT statement uses the LIKE operator to find rows matching a pattern instead of an exact match. LIKE is a standard SQL operator. It must ...