Convert date to string in Hibernate Criteria

Question

Is it possible to perform a request that checks the string representation of a Date instance. For example

Restrictions.like("dateField", "%12%") 

to retrieve dates that either have String 12 in day or 12 in month or 12 in year where "dateField" is an instance of java.util.Date Thanks

Answer 1

I had the same problem and here's what I did:

First, I created my own Criterion implementation:

public class DateLikeExpression implements Criterion {

private static final long serialVersionUID = 1L;
private String propertyName;
private String value;

public DateLikeExpression(String propertyName, String value) {
    this.propertyName = propertyName;
    this.value = value;
}

public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
    String[] columns = criteriaQuery.getColumnsUsingProjection(criteria, propertyName);
    if (columns.length != 1) {
        throw new HibernateException("Like may only be used with single-column properties");
    }
    return "to_char(" + columns[0] + ", 'DD/MM/YYYY HH24:MI') like ?";
}

public TypedValue[] getTypedValues(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
    return new TypedValue[] { new TypedValue(new org.hibernate.type.StringType(),
            MatchMode.START.toMatchString(value.toLowerCase()), EntityMode.POJO) };
}

}

Then, I just use it when I put the Criteria together:

criteria.add(new DateLikeExpression("dateColumnName", "26/11%"));

And that's about it. Note that this implementation is locale-dependent (pt_BR in this case) and works for postgresql, which has the to_char function. You might have to tweak it a little bit to work with your database engine.

Answer 2

Something like this

Restrictions.sqlRestriction("month(dateField) = 12");
Restrictions.sqlRestriction("right(year(dateField),2) = 12");

The part within the sqlRestriction depends on which database you are using.