Corresponding hibernate code using criteria

How to write the following JDBC query in hibernate?

Select * from Date_Table where

(START_DT>='2011-05-01 00:00:00.000000' and END_DT<='2011-05-02 00:00:00.000000' )

or

( START_DT<'2011-05-01 00:00:00.000000' and END_DT>='2011-05-01 00:00:00.000000' )

or

( START_DT<'2011-05-02 00:00:00.000000' and END_DT>'2011-05-01 00:00:00.000000' )

order by

START_DT asc

is this the right approach?

Criteria criteria=session.create开发者_运维问答Criteria(Hours.class);
            Criterion one=Restrictions.ge("startDate", startDate);
                Criterion two=Restrictions.le("endDate", endDate);
                LogicalExpression andOne=Restrictions.and(one, two);
                Criterion one_a=Restrictions.lt("startDate",  startDate);
                Criterion two_a=Restrictions.gt("startDate", startDate);
                LogicalExpression andOne_a=Restrictions.and(one_a, two_a);
                Criterion one_b=Restrictions.lt("endDate",  endDate);
                Criterion two_b=Restrictions.gt("endDate", endDate);
                LogicalExpression andOne_b=Restrictions.and(one_b, two_b);
                LogicalExpression or =Restrictions.or(andOne,andOne_a);
                LogicalExpression or1 =Restrictions.or(or,andOne_b);                
                criteria.add(or1);
                                criteria.addOrder(Order.desc("startDate"));

Have you tested it? Does it work?

I haven't tested it, but I'd say you are on the right track. If you are having trouble getting it to work, then try breaking things up. Get the query to run with the first AND clause only. When that works, try implementing the second AND clause (which requires you to implement the first OR condition).

I suggest you return to SO with a more precise question if you can't get it to work. Good luck!

You can use hibernate query for this.

Query query= sessionFactory.openSession().createQuery("from Date_Table where (START_DT>= :sdate and END_DT<= :enddate) or (START_DT>= :sdate2 and END_DT<= :enddate2); query.setDate("sdate", date1); query.setDate("enddate", date2); query.setDate("sdate2", date3); query.setDate("enddate2", date3);

List res=(Date_Table)query.list();