Search code examples
javahibernatehibernate-criteria

Hibernate criteria to use distinct date values on the basis of day only

I am using Hibernate Criteria API and using projection for my result shown below:

projList.add(Projections.property("router"), "router");
projList.add(Projections.property("date"), "date");

criteria.setProjection(Projections.distinct(projList));

Output:

2017-01-10 19:47:33.0   Router1
2017-01-11 20:45:59.0   Router1
2017-01-10 21:58:49.0   Router2
2017-01-10 21:59:00.0   Router2

This code works as expected but i want to run distinct function on the basis of unique date records, meaning distinct function should not consider time value but just the day value. So out put should be like:

2017-01-10  Router1
2017-01-11  Router1
2017-01-10  Router2

Any idea how to do that?

Solution

  • I found a way for doing it:

    Instead of 

    projList.add(Projections.property("date"), "date");

    Use

    projList.add(Projections.sqlProjection( "date(date) as date", new String[] {"date"}, new Type[] {StandardBasicTypes.STRING} ));

    Where expression "date(date) as date" first "date" is mysql Function, "(date)" is HBM column name of date, as "as date" is return column name from mysql and type "date" will be bean name that will be populated.