Using database functions to transform columns in hibernate criteria

Question

Despite 3 hours of googling and searching the API I can't find any reference to whether it's possible to use database functions within a hibernate criteria query. To be specific:

I'd like to access the date portion of the datetime in a postgres database and group by that. I'd imagine the query would look something like:

session.createCriteria(Exam.class)
  .setProjection(Projections.projectionList()
    .add(Projections.property("DATE(beginExam)").as("beginDate"))
    .add(Projections.groupProperty("beginDate")))
  .list();

This does not work giving me a "could not resolve property: Date(beginExam)..." exception. It seems like this is a very simple thing to do, and I must be missing something. Given that I am also building restrictions dynamically (I've left that out in the example) it seems that criteria is the hibernate component to use for this, but I'm open to any suggestions at this point short of side stepping the whole issue by building my own group by.

Thanks

Answer 1

Have you tried Projections.sqlProjection like this

session.createCriteria(Exam.class)
  .setProjection(Projections.projectionList()
  .add(Projections.sqlProjection("date(beginExam) as beginDate", new String[] { "beginDate" }, new Type[] { StandardBasicTypes.DATE }))
  .add(Projections.groupProperty("beginDate")))
.list();

or Projections.sqlGroupProjection like this

session.createCriteria(Exam.class)
  .setProjection(Projections.sqlGroupProjection("date(beginExam) as beginDate", "beginDate", new String[] { "beginDate" }, new Type[] { StandardBasicTypes.DATE }))
.list();

Hibernate generates table aliases in SQL queries, so you may need to add the {alias} fragment to your SQL fragment to make this work:

session.createCriteria(Exam.class)
  .setProjection(Projections.projectionList()
  .add(Projections.sqlProjection("date({alias}.beginExam) as beginDate", new String[] { "beginDate" }, new Type[] { StandardBasicTypes.DATE }))
  .add(Projections.groupProperty("beginDate")))
.list();

or with SQLGroupProjection:

session.createCriteria(Exam.class)
  .setProjection(Projections.sqlGroupProjection("date({alias}.beginExam) as beginDate", "beginDate", new String[] { "beginDate" }, new Type[] { StandardBasicTypes.DATE }))
.list();

Answer 2

Take a look at Projections.sqlProjection and Porjections.sqlGroupProjection