Hibernate version: 3.1

Code between sessionFactory.openSession() and session.close():



Name and version of the database you are using: DB2

The generated SQL (show_sql=true):



I know that this question has been asked in the past, but I did a search and found only unanswered posts.

I would like to use a Criteria Query to perform a LIKE wildcard search, but our business requirements allow for data containing underscores. Since underscores and percent signs are the SQL wildcards used with LIKE, a search for a field starting with "test_"% might return "test_page" and "tests". The users want to only see the row for "test_page" for this example.

I have gotten this to work using HQL and SQL by adding an "ESCAPE '\'" clause at the end of the provided query string. Is this possible using criteria queries? Is there a method that I have not found to provide an escape character that Hibernate would escape in the match criteria for like and or ilike?

Thanks in advance,
Ryan

YOu may try creating own criterion like this:
public class EscLikeExpression implements Criterion {

private final String propertyName;
private final Object value;

protected EscLikeExpression(String propertyName, Object value) {
this.propertyName = propertyName;
this.value = value;
}

protected EscLikeExpression(String propertyName, String value, MatchMode matchMode) {
this( propertyName, matchMode.toMatchString(value) );
}

public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery)
throws HibernateException {
Dialect dialect = criteriaQuery.getFactory().getDialect();
String[] columns = criteriaQuery.getColumnsUsingProjection(criteria, propertyName);
if (columns.length!=1) throw new HibernateException("like may only be used with single-column properties");
return columns[0] + " like ? escape '#' ";

//TODO: get SQL rendering out of this package!
}

public TypedValue[] getTypedValues(Criteria criteria, CriteriaQuery criteriaQuery)
throws HibernateException {
return new TypedValue[] { criteriaQuery.getTypedValue( criteria, propertyName, value.toString().toLowerCase() ) };
}

public String toString() {
return propertyName + " ilike " + value;
}



And then use it:
session.createCriteria( My.class ).add( new EscLikeExpression( "name","test#_%") ).list();

Thank you for the response. I wondered if implementing a Criterion would do what I wanted, but being new to Hibernate, I wasn't sure.

FYI... I did have to make the constructors public in the above example code. Also, I removed the ".toLowerCase()" in getTypedValues() and modified the toString() implementation since I am using like instead of ilike.


Thanks again,
Ryan