18.1 Introduction to Creating Search Forms

You can create a search form that allows users to enter criteria into a form based on known attributes of an object. The criteria is then constructed into a query, and a query-by-example (QBE) search is executed. All records whose attributes match the entered criteria are returned and can then be displayed in a table, either on a separate page or on the same page. To create a QBE search from, you drop a collection from the Data Control palette as an ADF Search Form. When you do this, the input text components do not have any associated validators or converters, which then allows the end user to enter search criteria that might otherwise not pass validation or conversion, such as entering > 1500 into a number field (for more information about conversion and validation, see Chapter 20, "Using Validation and Conversion").

In addition to iterating over collections, an iterator binding provides QBE capability by also being able to iterate over a collection of QBE criteria rows. These rows are created by the criteria entered by the user. Each criteria row has the same attribute structure as a row in its related data collection, except that the attribute values are all treated as a String data type. This data type allows the user to enter in the form fields query criteria containing comparison operators and wildcard characters.

For the iterator binding to have this capability, the binding must be set to Find mode. When an iterator binding is set to work in Find mode, the binding iterates over the these criteria rows instead of the standard collection data. The Execute operation is then used to execute the query against the collection. The Execute operation applies the user's query criteria and also disables the Find mode for the iterator binding, allowing the form to display data returned from the collection, as opposed to criteria. For more information about the Find mode, see Section 10.5.3, "How to Use Find Mode to Implement Query-by-Example".

By default, an ADF Search form contains command buttons bound to the Find and Execute operations. These button are useful if you want to be able to use the form to both search for records and view the current returned record. The user clicks the Find button to put the iterator in Find mode, thus making the input text fields available for criteria. When the user then clicks the Execute button, the criteria is used to create the query, the search is executed, and the form can then display the results. This type of search form is similar to how Oracle Forms EnterQuery/ExecuteQuery searches work. For more information on creating this type of search form, see Section 18.2, "Creating a EnterQuery/ExecuteQuery Search Form".

You can also force the iterator to always be in Find mode, thus negating the need for the Find button and allowing the user to view both their search criteria and the results. The user sees only a button bound to the Execute operation. When the user clicks that button, the iterator is toggled out of Find mode. To have it programatically set back to Find mode so that the user can always enter criteria, you insert an action binding into the page definition file that executes whenever the iterator is not in Find mode (for example after the Execute operation is invoked). However, this means that the results must be displayed in a separate form or table, as the iterator for the search form will always be in Find mode. This type of search is how a typical web page search works. For more information, see Section 18.3, "Creating a Web-type Search Form".

For a this type of web search, you can have the search form and results table on separate pages, or you can have the search form and the results table on the same page. However, when the search and results are on the same page, there must be one iterator that is always in Find mode for the search form, and a separate iterator for the results. See Section 18.3.4, "About Creating Search and Results on the Same Page" for more information. The SRSearch page uses this QBE search functionality, and displays the search form and results table on the same page, as shown in Figure 18-1.

Figure 18-1 Search and Results on the Same Page

You can also create a quick search form using named bind variables from a view object created just for the search. For example, the SRStaffSearch page in the SRDemo application uses the StaffListByEmailNameRole view object to create the search form. Instead of dropping the collection as an ADF Search Form, the ExecuteWithParams operation is dropped as a parameter form. When this search is executed, instead of building a QBE query dynamically each time the search is executed, the parameter form uses the view object's design-time SQL statement to execute the query. The text of the SQL statement never changes, so the database can reuse it efficiently, providing increased performance.

By contrast, in a QBE query the WHERE clause predicate is generated dynamically to match your search criteria. So if you keep searching with different combinations of criteria, the text of your SQL statement for that view object changes with the different executions. For example, three different searches might generate the following three different WHERE clauses for their SQL statements:

• AND (SVR_ID = 101)

• AND (SVR_ID = 102

• AND (PROBLEM_DESCRIPTION LIKE '%FOO%' AND ... )

Because the text of the SQL statement potential changes for a QBE search, the view object creates a new prepared statement each time, and the query must be reparsed on the database side. For this reason, when a search is expected to be frequently executed with the same statement, you may want to use a parameter search form for enhanced performance.

However, in order to create a parameterized search, you create a specific view object for the search. If you need to work programmatically with the rows using their generated custom row interfaces, it might not be practical if the view object for the search requires exactly the same structure and behavior as an existing view object. For example, if you create two view objects, ServiceRequests and ServiceRequestSearch, both with the same structure, and you need to edit the client code to get typesafe access to attributes, you'll need to edit both the ServiceRequestsRow and SearchRequestsSearchRow client interfaces. For this reason, you should use a QBE type search when you the query will require a view object that is the same as an existing view object, and the expected performance savings from a parameterized search will not be great.

In the SRDemo application, the SRSearch page uses a QBE query to find service requests based on the ID, status, problem description, and product name from the ServiceRequests view object. This search page uses an instance of the ServiceRequests view object named SearchServiceRequests, which is instantiated specifically for the search page by the application module. Another instance of the ServiceRequests view object, the ServiceRequestsByStatus instance, is used by the SRList page to show a filtered list of service requests by status.

For more information about view objects, view object instances, and named bind variables, see Chapter 5, "Querying Data Using View Objects"