[Hibernate/MySQL] : Natif SQL queries ???

smayemba · **Joined:** Sat May 15, 2004 4:27 am **Posts:** 79

Hi all,

who can tell me how can i translate the current following query in Hibernate :

SELECT *
FROM contracts
WHERE purchase_date <= DATE_FORMAT(NOW(),'%Y-%m-%d')
AND end_date >= DATE_FORMAT(NOW(),'%Y-%m-%d')

Thanks

-------------------
Sebastien

Dencel · **Joined:** Tue Jan 27, 2004 12:22 pm **Posts:** 103

Hello smayemba,

Although Hibernate QL looks similar to SQL it is not. The selection depends on the type of the object you use.
My first intuition would say (java coded):

Code:

String hql=
"Select contracts from Contract as contracts Where
contracts.purchasedate<=:purchasedate and
contracts.enddate>=:enddate;";

Query q = Session.createQuery(hql);
q.setParameter("purchasedate",Calendar.getInstance());
q.setParameter("enddate",Calendar.getInstance());

List result = q.list();

This is out of my head, so there could be some mistakes in it. Hope this helps.

smayemba · **Joined:** Sat May 15, 2004 4:27 am **Posts:** 79

Thank u Dencel,

i did the same query than you :

Code:

SimpleDateFormat sdf = new SimpleDateFormat(Constants.DATE_FORMAT);
Calendar currentCal = new GregorianCalendar();
currentCal.setTime(new java.util.Date());
String datetime = sdf.format(currentCal.getTime());       

Query q = session.createQuery("FROM Contract as co WHERE :mydate >= co.purchaseDate and :mydate <= co.endDate");
q.setParameter("mydate", datetime);
java.util.List contracts = q.list();

---------------
Sebastien