Hibernate version: 3.2.0cr2
Mapping documents: (excerpt)
Code:
<property name="activeStart" column="active_start_date" type="date" not-null="true" />
<property name="activeEnd" column="active_end_date" type="date" />
Code:
Date now = ... (date with time truncated) ...
Integer resultCount = (Integer)session.createCriteria( MyEntity.class )
.add( Restrictions.sqlRestriction(
"date_between_range( ?, {alias}.active_start_date, {alias}.active_end_date ) = ?",
new Object[] { now, 1 },
new Type[] { Hibernate.DATE, Hibernate.INTEGER } ) )
.setProjection( Projections.rowCount() )
.uniqueResult();
Name and version of the database you are using: Oracle 10gR2
The generated SQL (show_sql=true):Code:
Hibernate: select count(*) as y0_ from blah this_ where date_between_range( ?, this_.active_start_date, this_.active_end_date ) = ?
Oookie, so here we go. The above solution works, but I'm wondering if there's a substantially better one. Specifically, I need to write the SQL myself (and refer to column names directly from the database). I have a database function that takes 3 arguments: when, start, and end, all dates, and determines whether or not "when" is between "from" and "end" (inclusive, and also accommodating for null values in "end"). It returns 0 or 1 for false and true, respectively.
Actually this wasn't working for a coworker at first, but I got half way through this post and figured out what the problem was (an extraneous closing brace or something silly), but decided to post anyways to see if I was completely missing an existing capability in the Criteria framework. Any ideas?
Thanks!
Login
FAQ
Search
