Posts Tagged ‘hibernate’

Hibernate Embeddable Objects

January 18th, 2010

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.
» Read more: Hibernate Embeddable Objects

No comments »

Posted in Hibernate, Java

Tags:

Building Multi-Criteria Search Queries in Hibernate

December 9th, 2009

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.

No comments »

Posted in Database, Design Patterns, Java

Tags:

Hibernate Criteria trick

October 8th, 2009

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…

1 comment »

Posted in Java

Tags: