I encountered a subtle hibernate mapping issue involving Dates and Timestamps. The following test recreates this issue.
» Read more: Hibernate Date vs Timestamp
Posts Tagged ‘hibernate’
Hibernate Logging
January 30th, 2012Through 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.
» Read more: Hibernate Logging
Building Multi-Criteria Search Queries in Hibernate
December 9th, 2009In 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
October 8th, 2009So 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…
