CODEWITHSUNDEEP
javamysqlhibernate
Dejell
Dejell

Reputation: 14317

how to use date() in hibernate criteria

I would like to use Date() function in hibernate criteria, since one field is timestamp.

My sql would be:

"select * from feed where DATE(date) = DATE(NOW()) + INTERVAL 5 DAY"

When I try in criteria:

critera.add(Restrictions.eq("DATE(date)", "DATE(NOW()) + INTERVAL 5 DAY"));

I get:

could not resolve property: DATE(date) of: com.mycomapany.model.Feed

I have there in feed a field by the name date.

What is the problem? I am using MySQL

Upvotes: 1

Views: 8578

Answers (3)

khmarbaise
khmarbaise

Reputation: 97399

The best solution is to use the criteria api which you started to use.

Calendar c = Calendar.getInstance(LOCALIZATION);
//Add 5 Days
c.add(Calendar.DATE, 5);
criteria.add(Expression.ge("dateColumn",c.getTime());

Upvotes: 4

Steve
Steve

Reputation: 8809

Using Joda Time:

public List<Feed> findByDaysFromToday(int daysFromToday) {
    return em.createQuery(
            "SELECT f FROM Feed f WHERE f.date BETWEEN :start AND :end")
            .setParameter("start",
                          new LocalDate()
                                  .toDateMidnight()
                                  .toDate(),
                          TemporalType.DATE)
            .setParameter("end",
                          new LocalDate().plusDays(daysFromToday)
                                  .toDateMidnight()
                                  .toDateTime()
                                  .minusMillis(1)
                                  .toDateTime()
                                  .toDate(),
                          TemporalType.DATE)
            .getResultList();
}

Upvotes: 1

GreyBeardedGeek
GreyBeardedGeek

Reputation: 30088

You could probably find a way to make this work, but in my opinion, this is the wrong way to approach it.

One of the major features of Hibernate is the abstraction of the database-vendor-specific features, via the Hibernate Dialect.

You are attempting to access MySQL features directly, but through the vendor-neutral interface, thus your difficulty. Hibernate could have support for date intervals , but does not - see https://hibernate.onjira.com/browse/HHH-2434

So, the options seem to be:

  • Calculate the date ranges in your code, rather than in the HQL
  • Do what you need to do in SQL rather than HQL
  • Create a custom Hibernate Dialect, as in Performing Date/Time Math In HQL?

Upvotes: 1

Related Questions