2

I want to select something like this MySQL query, how do it in hibernate criteria?

SELECT id, IF(type = 'P', amount, amount * -1) as amount FROM    report
moosy
  • 55
  • 8
  • Same Question... http://stackoverflow.com/questions/15826567/please-provide-example-for-an-if-statement-in-hibernate-criteria – pL4Gu33 Mar 01 '14 at 08:53
  • Same Questions with no useful response – moosy Mar 01 '14 at 09:03
  • In Hibernate, you don't do this in a query, you do this in your Java code. Hibernate is an object-relational mapper but I don't see anything object-relational in your question. I think you're using the wrong tool. – Erwin Bolwidt Mar 01 '14 at 09:50

1 Answers1

0

I'm not sure how to do it using a criteria, but you can acheive the same by executing a native sql query in hibernate. Lets assume your model class is "Report" and it has a property called "amount" with its getter and setter:

String query = "SELECT id, IF(type = 'P', amount, amount * -1) as amount FROM    report";
Session session = sessionFactory.openSession();
SQLQuery sqlQuery = session.createSQLQuery(query);
sqlQuery.setResultTransformer(Transformers.aliasToBean(Report.class));
List<Report> resultList = sqlQuery.list();
session.close();
Sari Alalem
  • 870
  • 7
  • 18
  • thanks for you replay, unfortunately I can not use native sql, Is there any method in different ways to achieve such result with criteria? – moosy Mar 01 '14 at 09:27
  • I think you can use "Case When" in HQL, as it seems to be added since 3.0.3, but I believe it will only work in the where satement, give it a try and let me know how it goes – Sari Alalem Mar 01 '14 at 09:39
  • 1
    In HQL it is possible but I use a wrapper class used criteria to handle filter, sort, groping and ... so I want a criteria query to do such thing and now I think it is impossible. damn you criteria damn you. – moosy Mar 01 '14 at 09:48