& Continuous Learning

In this post I am going to show how to write queries multi-criteria search screens. There are two approaches for making this possible.

• HQL for building the Query
• Building Query using Criteria API

HQL for building the Query

Here I am going to show 2 approaches to building the HQL and try to point out the better approach.

Approach I:String concatenation This approach uses String concatenation and setting up the values directly in the query.

if (startDate != null) {
   if (firstClause) {
      query = query + " where ";
   }
   else {
      query = query + " and ";
   }

   query += " s.date >= '" + startDate + "'";
}

Using the above approach there might be a chance of SQL Injection attack and using string concatenation is inherently error-prone.

Approach 2: Criteria as Named Parameters

In this one we create two map to hold parameter name and value which could be binded to the HQL during the execution.

Here is brief example of the approach.

public List search() {
   StringBuilder aQuery =
      new StringBuilder(" from document p where p.id is not null ");
   HashMap parameterMap = new HashMap();
   HashMap parameterListMap =  new HashMap();

   //Collection Criteria
   if (countyList != null) {
      buildCollectionCriterion(aQuery, parameterListMap, "listID", countyList);
   }

   //Date Criteria
   if (startDate!= null || endDate != null) {
      buildDateCriterion(aQuery, parameterMap, "dateField",startDate, endDate);
   }

   Query query = hibSession.createQuery(aQuery.toString());

   for (String key : parameterMap.keySet()) {
      query.setParameter(key, parameterMap.get(key));
   }

   for (String key : parameterListMap.keySet()) {
      query.setParameterList(key, parameterListMap.get(key));
   }

   List results = query.list();
}

//Helper Methods for different type of criteria

protected void buildCollectionCriterion(StringBuilder aQuery,
                                        Map parameterListMap,
                                        String aFieldName,
                                        Collection aList) {
   if (aList != null && !aList.isEmpty()) {
      aQuery
         .append(" and p.")
         .append(aFieldName)
         .append(" in (:")
         .append(aFieldName)
         .append(")");
      parameterListMap.put(aFieldName, aList);
   }
}

public void buildDateCriterion(StringBuilder aQuery,
                               Map parameterMap,
                               String aFieldName,
                               Date aStartDate,
                               Date anEndDate) {
   if (aStartDate != null && anEndDate != null) {
      aQuery
         .append(" and ( p."
         .append(aFieldName)
         .append(" between :aStartDate and :anEndDate)");
      parameterMap.put("aStartDate", aStartDate);
      parameterMap.put("anEndDate", anEndDate);
   }
   else if (aStartDate != null) {
      aQuery
         .append(" and  (p.")
         .append(aFieldName)
         .append(" >= :aStartDate)");
      parameterMap.put("aStartDate", aStartDate);
   }
   else if (anEndDate != null) {
      aQuery
         .append(" and (p.")
         .append(aFieldName)
         .append(" <=:anEndDate");
      parameterMap.put("anEndDate", anEndDate);
   }
}

Building Query using Criteria API

public List search() {
        Criteria c = hibSession.createCriteria(Document.class);
        c.add(Restrictions.notNull("id"));

        if(countryList != null) {
                c.add(Restrictions.in("listId", countryList));
        }

        if(startDate != null) {
                c.add(Restrictions.ge("dateField", startDate);
        }

        if(endDate != null) {
                c.add(Restrictions.le("dateField", endDate);
        }

        return c.list();
}

Conclusion

The Hibernate Criteria API is a powerful and elegant library which is well adapted for implementing multi-criteria search functionality and also HQL queries must be built 'on-the-fly'. Using it in appropriate circumstances will result in cleaner, clearer, more reliable and more maintainable code.