Designing the search query for 3 drop downs containing the same values

We have 2 search modes. Normal and Advanced. In Normal mode we have only one drop-down with 10 search criteria’s. In advanced mode we have 3 drop-downs with the same search criteria’s. We have this advanced mode to filter the search values.

Now that we have a basic understanding of our requirement we shall go ahead and design the implementation.

We have 3 drop-downs to select the search criteria and 3 textboxes to enter the values. Once the user selects the search criteria and enters values, search values are added to a HashMap with the search criteria as key and user condition as value. Before we put the values in a map we trim the user conditions to remove extra spaces like

criteriaSelectedMap.put(criteriaSelected1, valueSelected1.trim());

The keys in the map are nothing but the search criteria which are fetched from database, means those values are pre-defined and known to us before hand.

We then do a null check and get the value from the map using the pre-defined key and check for wild card search and finally store it in a bean as a property. We follow the same approach for all the 10 drop-down values and store the user search condition in one of the 10 property of the bean.

Wild card search: Wild card search is nothing but using * for searching in UI (JSP Pages). In the back end (database) all the * has to be replaced with % as we use this (%) symbol for searches.

Example:
Select name from employee where name like ‘s%’;
Here we are searching for names that start with s.

Select name from employee where name like ‘%s’;
Here we are searching for names that end with s.

RequestSearchBean objRequestSearchBean = new RequestSearchBean();

if (searchCriteriaMap != null && !searchCriteriaMap.isEmpty()) {
String countryName = searchCriteriaMap.get(SEARCH_COUNTRY);
}

if (countryName != null) {
objRequestSearchBean.setCountryName(
frameWildCardSearch (countryName));
}

public static String frameWildCardSearch(String criteria)
{
if (criteria == null) return criteria;
if (criteria.contains("*"))
{
criteria = criteria.replace('*','%');
}
return (criteria);
}

We will now design the JDBC part.

preparedStatement = objConnection.prepareStatement(sqlQuery);

ps.setString(1, objRequestSearchBean.getCountryName());
ps.setString(2, objRequestSearchBean.getCountryName());
ps.setString(3, objRequestSearchBean.getBankName());
ps.setString(4, objRequestSearchBean.getBankName());

Query part:

AND RD.REQUESTDETAILSKEY = BFC.REQUESTDETAILSKEY(+)
AND BFC.BANKFACILITYKEY = BFTY.BANKFACILITYKEY(+)
AND BFTY.FACILITYTYPEKEY = FT.FACILITYTYPEKEY(+)

AND (1 = NVL2 (:COUNTRYNAME, 0, 1) OR UPPER (CD1.DISPLAYNAME) LIKE UPPER(:COUNTRYNAME))
AND (1 = NVL2 (:BANKNAME, 0, 1) OR UPPER (BD.BANKNAME) LIKE UPPER(:BANKNAME))

This is how we pass all the 10 user search conditions to the query.

NVL2
=====

Syntax:
NVL2( string1, value_if_NOT_null, value_if_null )

Example:
select NVL2(supplier_city, 'Completed', 'n/a') from suppliers;

The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the 'Completed'.

So NVL2 function will return either 1 or 0. If it returns 1 then it is understood that the input value is null and the condition of (1 = 1) is satisfied and the control won’t go the next part that’s the OR part.