1

i have this simple MySQL query :

SELECT * from foo ORDER BY (col1+col2+col3) DESC;

the result of col1+col2+col3 is an Integer.

is there a way to make this with DetachedCriteria using hibernate for spring ?

P.S. i'm quite inexperienced with DetachedCriteria

Thanks

Medioman92
  • 581
  • 4
  • 10
  • 21

1 Answers1

2

Yes there is a way using the formula annotation. The annotation would be placed on the Foo class. Note that the formula annotation accepts sql column names and not object property names.

@Formula("col1+col2+col3")
private int calculatedValue;

public int getCalculatedValue() {
    return calculatedValue;
}

public void setCalculatedValue(int calculatedValue) {
    this.calculatedValue = calculatedValue;
}

The detached query would look like something below.

DetachedCriteria query = DetachedCriteria.forClass(Foo.class);
query.addOrder(Order.desc("calculatedValue"));
List<Foo> results = query.getExecutableCriteria(session).setMaxResults(100).list();
Manuel Quinones
  • 4,196
  • 1
  • 19
  • 19
  • thanks for the answer, but what about the hibernate mapping file ? should i also map the formula there ? how ? i tried your code and i'm getting a "property not found exception"... the hibernate documentation about @Formula is lacking – Medioman92 Jan 08 '13 at 10:36
  • SOLVED...i'm not using annotations in my project so i've just added the formula in the hibernate mapping file...thanks Manuel – Medioman92 Jan 08 '13 at 13:34