Adding days to named parameter of type Date in HQL

Question

I've got an oracle database in which warehouses are stored that are open only on one given day in a week, e.g. 'monday' or 'thursday'.

The day the warehouse is open is stored as an integer. Sunday is stored as 0, saturday as 6.

Each warehouse is only open for given period of time, known as its validity period. This validity period is stored as two dates, a validity start date and a validity end date. E.g. a warehouse is open for two months from 01-01-2011 until 28-02-2011.

Now I want to select all valid warehouses for a given week. The requested week is passed as a date. It is agreed upon that this date will always be some some sunday, e.g. May 29th, 2011. This leads to the following query:

  from Warehouse w
 where (:requested_week_date + w.day_open) between w.valid_start and w.valid_end

The problem is that this query will give a class cast exception:

java.lang.ClassCastException: java.util.Date incompatible with java.lang.Integer

The thing is that under water Oracle is capable of adding an integer to a date. Oracle will treat the integer as a number of days to add to the date. My question is if it is possible to get this to work in HQL.

I know you can also define native sql queries in hibernate (already tried this and this works well), but that is not the answer i'm looking for. I'm just looking for an HQL solution, given that there is one.

Answer 1

A solution might be to subclass the oracle dialect you're using and register an additional add_days function which would do the appropriate SQL translation:

package foo.bar;

import org.hibernate.dialect.Oracle10gDialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.type.StandardBasicTypes;

public class MyOracleDialect extends Oracle10gDialect {
    @Override
    protected void registerFunctions() {
        super.registerFunctions();
        registerFunction("add_days", new SQLFunctionTemplate(StandardBasicTypes.DATE, "(?1 + ?2)"));
    }
}

Now that the dialect class is defined, use this class in your hibernate configuration:

hibernate.dialect=foo.bar.MyOracleDialect

And use the following HQL query:

from Warehouse w
  where add_days(:requested_week_date, w.day_open) between w.valid_start and w.valid_end