3

How to use order by clause for sort by sum of field

Entity:

    class MyClass {

        private int a;
        private int b;

        //...

    }

HQL works correctly:

"SELECT myclass FROM MyClass myclass ORDER BY myclass.a + myclass.b DESC "

How to do the same thing using Criteria API?

rdm
  • 330
  • 1
  • 4
  • 18
  • AFAIK, there's no way without hacking your own Order subclass that would override toSqlString(). Why don't you keep it as a HQL query? – JB Nizet Nov 02 '13 at 13:10
  • Is true? My HQL works. "Hibernate doesn't support arithmetics expressions in the order by clause." http://stackoverflow.com/a/2373359/1319419 – rdm Nov 02 '13 at 13:21
  • Well, things have probably changed since 2010, since your HQL query works fine. – JB Nizet Nov 02 '13 at 13:22
  • 1
    How about use @Formula for this case? – rdm Nov 02 '13 at 14:02
  • I don't understand what you want. You said that your HQL query did work fine. Why not use it then? – JB Nizet Nov 02 '13 at 14:04
  • I dont know :) I everywhere use the Criteria API, uniform style. – rdm Nov 02 '13 at 14:11
  • 2
    The Criteria API is much more limitating than HQL, and leads to longer, harder to read code. It's useful when dynamic queries must be created (like for a search form with multiple optional criteria). Other than that, HQL is much easier, more maintainable, and more powerful. Use it. – JB Nizet Nov 02 '13 at 14:13
  • You have a point there. – rdm Nov 02 '13 at 14:19
  • @JBNizet Very true. And for the same reasons, I first decided to use Criteria but later ended up using a mixture of HQL & Criteria at places according to requirement. – coding_idiot Nov 03 '13 at 13:20

1 Answers1

1

As you said you could use @Formula

@Column(name="column_a")
private Integer a;
@Column(name="column_b")
private Integer b;
@Formula("(column_a+column_b)")
private Integer c;

After that you can sort by property c like

criteria.addOrder(Order.asc("c"));
Alex
  • 11,451
  • 6
  • 37
  • 52