Escape special characters in hibernate criteria

This is the continuation of my previous post. The method mentioned in previous post will help in case of direct SQL and HQL. We will see how to implement the same thing with hibernate criteria.

We will extend the default functionality of org.hibernate.criterion.IlikeExpression and override
public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) method
to include ESCAPE clause at the end of sql query.

So we need to generate sql out of hibernate criteria in the flowing pattern in order to tell data base that special characters are interpreted literally..
select * from EMP_TEMP where name like '%¼_%' ESCAPE ‘¼’

Two need to do two things in order to achieve this.
1. Append escape character with search value containing special character.
2. Append ESCAPE clause at the end of the generated query.

The first step appending escape character with search value is as follows
public static String HIBERNATE_ESCAPE_CHAR = "¼";
String searchValue = “%_%”;
//In the simillar fashion we can escape “%” also
searchValue = searchValue.replaceAll(“_”,HIBERNATE_ESCAPE_CHAR + "_");

The second step is to append ESCAPE clause with sql. For that first we will create custom expression by extending IlikeExpression as specified below

public class EscapedILikeExpression extends IlikeExpression {
public static final String ESCAPE_CHAR = " ESCAPE '¼' ";

/**
* @param propertyName
* @param value
*/
public EscapedILikeExpression(String propertyName, Object value) {
super(propertyName, value);
// TODO Auto-generated constructor stub
}

/**
* @param propertyName
* @param value
* @param matchMode
*/
public EscapedILikeExpression(String propertyName, String value,MatchMode matchMode) {
super(propertyName, value, matchMode);
// TODO Auto-generated constructor stub
}

public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery)
throws HibernateException {
String sql = super.toSqlString(criteria, criteriaQuery);
sql = sql + ESCAPE_CHAR;
return sql;
}
}

And add EscapedRestrictions to use EscapedIlikeExpression
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.MatchMode;
public class EscapedRestrictions {
          public static Criterion ilike(String propertyName, String value) {
                    return new EscapedILikeExpression(propertyName, value);
          }

          public static Criterion ilike(String propertyName, String value, MatchMode matchMode) {
                    return new EscapedILikeExpression(propertyName, value, matchMode);
          }
}

Now the criteria code will look like this which makes use of EscapedRestrictions.java
Session session = hibernateTemplate.getSessionFactory().openSession();
Criteria criteria = session.createCriteria(EmpTemp.class)
.add(EscapedRestrictions.ilike("name", searchValue));
List<EmpTemp> list = criteria.list();
if(list != null && !list.isEmpty()){
for(EmpTemp employee : list){
System.out.println(employee.getName());
System.out.println(employee.getLocation ());
}
}
session.close();

This would result the SQL query same as below and provide results that we expected.
select * from EMP_TEMP where name like '%¼_%' ESCAPE ‘¼’