I need to use criteria to query a database. The data I'm searching has a date, say 'startDate' and I have a month say 0 for January, I need to extract all data where startDate has month = 0; in sql I'd use something like 'where month(startDate) = 0' but I don't know how to do this with hibernate criteria and if it's possible at all. Can you help me? Thank you guys. Luca.
How to query on a month for a date with Hibernate criteria
With criteria, I think you'll have to write your own expression class. Something like this should work (not tested, though):
public class MonthEqExpression implements Criterion {
private final String propertyName;
private final int month;
public MonthEqExpression(String propertyName, int month) {
this.propertyName = propertyName;
this.month = month;
}
@Override
public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery)
throws HibernateException {
String[] columns = criteriaQuery.findColumns(propertyName, criteria);
if (columns.length!=1) {
throw new HibernateException("monthEq may only be used with single-column properties");
}
return "month(" + columns[0] + ") = ?";
}
@Override
public TypedValue[] getTypedValues(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
return new TypedValue[] {new TypedValue(IntegerType.INSTANCE, month, EntityMode.POJO)};
}
@Override
public String toString() {
return "month(" + propertyName + ") = " + month;
}
}
And then, you can use this expression in a criteria:
criteria.add(new MonthEqExpression("startDate", 0));
It worked fine, thank you, I couldn't have come up with such a solution, I feel miserable. –
Bickart
@Luca Paoli: Hibernate is open-source. I just looked at the code for existing expressions in the hibernate code. –
Fernery
The answer above didn't work for me, but with a few changes it's working now.
public class MonthEqExpression implements Criterion {
private final String propertyName;
private final Long month;
public MonthEqExpression(String propertyName, Long month) {
this.propertyName = propertyName;
this.month = month;
}
@Override
public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
String[] columns = criteriaQuery.getColumns(propertyName, criteria);
if (columns.length != 1) {
throw new HibernateException("monthEq may only be used with single-column properties");
}
return "extract(month from " + columns[0] + ") = ?";
}
@Override
public TypedValue[] getTypedValues(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
return new TypedValue[] {new TypedValue(criteriaQuery.getIdentifierType(criteria), month, EntityMode.POJO)};
}
@Override
public String toString() {
return "extract(month from " + propertyName + ") = " + month;
}
}
And then, you can use this expression in a criteria:
criteria.add(new MonthEqExpression("startDate", new Long(1)));
© 2022 - 2024 — McMap. All rights reserved.