Hibernate : Escape underscore character in like expression

Hello,

A simple article concerning a problem encountred by several developers (including myself) who developing with Hibernate 3 search criteria.

In SQL, in the following query:

SELECT * FROM person WHERE firstname LIKE 'PERSON_1%';

…the underscore is not being considered because underscore is a wildcard for a single character in SQL. The query matches and bring many rows apart the rows which have “PERSON_1”.

Several solutions are possible for thath the underscore character becomes a simple character:

SELECT * FROM employee WHERE firstname LIKE 'PERSON[_]1%';
SELECT * FROM employee WHERE firstname LIKE 'PERSON\_1%';

But, for the queries generated by Hibernate, is there a way to configure Hibernate to escape all underscores in all queries?

By default, the underscore character “_” is also ignored in the search criteria. For example a search for “PERSON_1” returns “PERSON 1” ignoring the underscore.

1st solution:
We need treat the underscore as an escape character.

The following did not appear to work:
criteria.add(Restrictions.ilike(“name”, “PERSON_1”, MatchMode.ANYWHERE));

The following worked in Hibernate 3:
criteria.add(Restrictions.ilike(“name”, “PERSON\\_1”, MatchMode.ANYWHERE));

2nd solution:
If the query is createb via the Criteria, it is possible to create an own like expression which subclasses org.hibernate.criterion.LikeExpression.
This own subclasses uses one of the protected constructors that takes in ‘Character escapeChar’, and does substitution in the value.

Assuming that ‘!’ is a known value that won’t be in any search strings, we will create the following class:

package com.ho.orm;

import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.LikeExpression;
import org.hibernate.criterion.Restrictions;


/**
 * @author huseyin
 *
 */
public class CriteriaUtil {
	
	/**
	 * Utility class needed in order to escape the specials characters (like '_') in a like expression in Hibernate Criteria.
	 * @author huseyin
	 *
	 */
	public static class EscapingLikeExpression extends LikeExpression{
		private static final long serialVersionUID = 1L;
		
		public EscapingLikeExpression(String propertyName, String value){
			super(propertyName, escapeString(value), '!', false);
		}
		
		static String escapeString(String inputString) {
			inputString = inputString.replace("_", "!_");
			return inputString;
		}	
	}	
}

… and this class could be used:

// LIKE Expression
CriteriaUtil.EscapingLikeExpression likeExpression = new CriteriaUtil.EscapingLikeExpression("name", "%_%");
		
// NOT LIKE Expression
hibernateCriteria.add(Restrictions.not(likeExpression));

Note: Another useful method allows the use of ‘null’ value in a criteria because ignoreCase of criteria API doesn’t like null parameter.

	/**
	 * Helper because ignoreCase of criteria API doesn't like null parameter.
	 * 
	 * @param propertyName
	 * @param param
	 * @return
	 */
	public static final Criterion eqWithIgnoreCase(String propertyName, String param){
		return param == null ? Restrictions.isNull(propertyName) : Restrictions.eq(propertyName, param).ignoreCase();
	}

That’s all!!!!

Huseyin OZVEREN