CODEWITHSUNDEEP
javamysqlhibernate
rattek
rattek

Reputation: 1670

Select distinct year from date using Hibernate Criteria

I would like to get all distinct years from a MySQL date field with the Hibernate Criteria API. This is the query that I want to translate to criteria: 

sess.createQuery("SELECT DISTINCT year(o.date) FROM Observation");

I've tried :

Criteria crit = sess.createCriteria(Observation.class)
    .setProjection(Projections.distinct(Projections.property("year(date)")));

But it returns an error

ERROR - could not resolve property: year(date) of: ca.ogsl.biodiversity.hibernate.Observation

Does any body knows if it's possible and how?

Thanks a lot!!!

Keven

Upvotes: 0

Views: 3214

Answers (2)

Julien
Julien

Reputation: 1097

Try using an sql projection, so you can use any SQL expression: 

criteria.setProjection(Projections.sqlProjection( "yeard(DATE_COLUMN_NAME) as year", new String[] {"year"}, new Type[] {StandardBasicTypes.INTEGER} ));

Upvotes: 2

Rainbolt
Rainbolt

Reputation: 3660

Use the DISTINCT keyword to query for distinct years, rather than getting all years and trying to filter them in Java code.

SELECT DISTINCT year(o.date) FROM Observation;

Upvotes: 0

Related Questions