Tag Archives: hibernate

Hibernate Logging

Through the years I’ve encountered a recurring requirement. Clients need to log changes to the database for auditing and legal purposes. To satisfy this requirement you could add logging to every save/update/delete call in your code. Or better yet, you could create an aspect that wraps these calls. While these would certainly work Hibernate provides a convenient interceptor.

In this article I will show you how to add a simple logger to Hibernate.
Continue reading

Hibernate Embeddable Objects

Hibernate Embeddable Objects are a really neat way to organize your data model.  Especially, if you have the same few columns in a number of different tables, that all pertain to the same thing. The example commonly used is Addresses.  You may have a number of tables that each have fields pertaining to address information and you don’t want to have to do all the mappings for each entity again and again.  If the column names are the same across each table, you can just add an @Embeddable annotation.
Continue reading

Building Multi-Criteria Search Queries in Hibernate

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.

Hibernate Criteria trick

So here’s the situation.

Let’s say I have this query here:

SELECT * FROM employees
WHERE employee_id NOT IN ( 1234 , 3456 , 5678 );

How do we do that with the Hibernate Criteria object with a Restriction?  You would think that the Restrictions API would have a “not in” method, since it does have a not equals method(ne), but alas, there is nothing…

Well, here’s the solution:

//Create the criteria
Criteria crit = factory.getCurrentSession().createCriteria(Employee.class);
 
//add my restriction where idList is a list of emp ids that need to be excluded
crit.add(Restrictions.not(Restrictions.in("employeeId", idList)));
 
//get some results
List employees = crit.list();

There you go!  Now you know this neat little trick and you can use it in your own app… Be forewarned though, it can be slow…