I have trouble getting the following query to return anything at all:

final Query query = session.createQuery(
" FROM FXRate AS fxr "
+ " WHERE fxr.baseCurrencyId = :base "
+ " AND fxr.ratedCurrencyId = :rated "
+ " AND fxr.effectiveDate = :date"
);
query.setParameter("base", base.getId());
query.setParameter("rated", rated.getId());
query.setParameter("date", calendar);

Yet I know that the combination of values I pass exists in the database.

Only if I first do this, I get what I want:

calendar.set(Calendar.HOUR, 0);
calendar.set(Calendar.MINUTE, 0);
calendar.set(Calendar.SECOND, 0);
calendar.set(Calendar.MILLISECOND, 0);

THAT IS, if I happen to do my coding before 12:00AM. After that, I have to write:

calendar.set(Calendar.HOUR, 12);

Things cannot be this ugly, can they? Am I overlooking something? I use "calendar_date" as the type of the date, but have also tried "calendar" and "date". Also note that the object has a composite key with a date as one of its components, so I overrode equals() and hashcode(). In the equals() method I just do

"this.calendar.equals(other.calendar)"

to compare the date component of the composite key.

Anyone any clue?

Use some SQL function or cast:



It must not be a problem with index in your case, you can create index for function in some databases too.

Thanks! I will try that