How do I use Criteria to make a join on a date field (in DB2) based on the year.

Question

I have a java.util.Date field in my Object. I would like to use Criteria to select all rows that have a date field with a given year. The SQL would look like the following:

SELECT *
FROM GAME GM
WHERE YEAR(GM.GAME_DATE) = 2010

How can I use Criteria to accomplish this? Thanks in advance.

public Collection<Game> getGamesByDate(Date date){
        Collection<Game> games = null;

        try {       
            session.beginTransaction();
            Criteria criteria = session.createCriteria(Game.class);
            criteria ....
                        ...
                        ...
            games = criteria.list();
        } catch (HibernateException e) {
            e.printStackTrace();
        }

        return games;
    }

Answer 1

Hi HtmlTableGuru

I would add a Restriction as follows:

criteria.add(Restrictions.between("date", startDate, endDate);

Where startDate and endDate would be your year with -01-01 and -12-31 (like Ian Bjorhovde said).

If you want to use HQL, the year() function is a valid expression in the where clause.

Cheers
Thomas