LIKE restriction from the Hibernate Criteria API

Question

I am trying to query PostgreSQL database using Hibernate's restriction criterion like() with a partial keyword:

Criterion c1 = Restrictions.like("stateName", "Virg*");
cri.add(c1);

return cri.list();

It doesn't return anything but Restrictions.like("stateName", "Virginia"); returns the correct record. How do I use partial like restrictions in Hibernate?

EDIT:
Got it working by doing something like this:

public static List<Object> createQueryStringByRegex(Criteria cri, Parameters p) {
    String value = (String) p.value;
    if (value.contains("*")) {
        value = value.replace("*", "%");
    } else {
        value += "%";
    }
    // System.out.println("Value: "+value);
    Criterion c1 = Restrictions.ilike(p.property, value);
    cri.add(c1);

    return cri.list();

}

Leckie

Answer 1

Use the enum MatchMode to help you with it:

Criterion c1 = Restrictions.like("stateName", "Virg", MatchMode.START);

Don't use any special character, like *. And if you want a case-insensitive like, use ilike.

Gazpacho

doesn't use of ilike depend on if the underlying DB supports it? I.e. Postgres supports ilike but others may not? – Geomancer

I believe not, at least there isn't anything about it in the documentation. Probably Hibernate by itself provides this feature. – Gazpacho

For the sake of his question, shouldnt the MatchMode be MatchMode.START? – Digger

@Juliano actually MatchMode.START is right because it tells hibernate to match from the beginning. That is if you translate this to mysql implementation it becomes 'SELECT * FROM <table> where <column> LIKE "parameter%" – Puppis

Answer 2

The restriction should use the percent symbol.

Criterion c1 = Restrictions.like("stateName", "Virg%");

Akers

Youre way more flexible in which database to choose or migrate to in the future if you use the MatchMode as suggested by Juliano's answer. – Digger

Just use MatchMode, this is defeating one of the reason to use the API in the first place, which is for your code to be database agnostic! – Puppis

Answer 3

##Use MatchMode.ANYWHERE##
DetachedCriteria crit = DetachedCriteria.forClass(MyClass.class);
 crit.add(Restrictions.like("prop1", "matchValue", MatchMode.ANYWHERE));
 return getHibernateTemplate().findByCriteria(crit);

it's equivalent to:

select * 
    from Table_Name 
    where prop1 like "%matchValue%";

Casper