Shekhar Khairnar Shekhar Khairnar - 1 year ago 124
SQL Question

Date difference in days Restriction Hibernate

I am using Hibernate and Criteria API to write my database quires. What I need to do is I need two dates difference in days and compare that days with with specific number.

E.g. Commonly written criteria restriction as:

criteria.add(Restrictions.eq("somProperty", someValue));

What I needs is:

criteria.add("dateProperty1 - dateProperty2", 15));

Means date difference between two dates is greater than or equal 15 days.

I don't see how to achieve this. And yes I did lots of Google to find out the possible solution but didn't get proper material what I need.

Answer Source

If you check the documentation of the Restrictions class, you will see that:

  • most functions operate on a "property vs value" pair (just like in your first example)
  • the remaining comparator functions operate on "property vs other property" pair

But no customized function is available for your case. So what option you have is using the sqlRestriction, which can be used to express this condition in a native form of your DBMS. That would be a different, but much easier problem, altough clearly not as elegant as your original idea.