I am running on MS Sql Server 2005. I am very new to Hibernate and I need help convery some SQL that I wrote in JDBC in my Java Program to Hibernate HQL.


I am not sure how to handel the Datediff and if I should try and make this HQL or should I pass native SQL query through the Hibernate Session using the Session's createSQLQuery method. Thanks for any help you can give me.


Here is my code I need to convert to Hibernte

PreparedStatement selectSched = connection
.prepareStatement("SELECT b.end_time, "
+ " Datediff(mi,b.end_time,a.start_time) diff "
+ "FROM (SELECT Row_number() "
+ " OVER(ORDER BY start_time, end_time) AS rid, "
+ " start_time, "
+ " end_time "
+ " FROM sched_view "
+ " WHERE empid = ? "
+ " AND flddatescheduled BETWEEN ? AND ?) a, "
+ " (SELECT Row_number() "
+ " OVER(ORDER BY start_time, end_time) AS rid, "
+ " start_time, "
+ " end_time "
+ " FROM sched_view "
+ " WHERE empid = ? "
+ " AND flddatescheduled BETWEEN ? AND ?) b "
+ "WHERE a.rid = (b.rid + 1) "
+ " AND Datediff(mi,b.end_time,a.start_time) >= ?");

selectSched.setString(1, qEmpId);
selectSched.setString(2, qDate);
selectSched.setString(3, qDate);
selectSched.setString(4, qEmpId);
selectSched.setString(5, qDate);
selectSched.setString(6, qDate);
selectSched.setInt(7, qTimeNeeded);


There is view with that I am using my my qurey

String SQL = "CREATE VIEW sched_view " + "AS "
+ " (SELECT fldstarttime start_time, "
+ " fldendtime end_time, "
+ " ts.flddatescheduled flddatescheduled, "
+ " tr.fldemployeeid empid "
+ " FROM saloniris.dbo.tblticketsrow tr, "
+ " saloniris.dbo.tblticketssummary ts "
+ " WHERE tr.fldticketid = ts.fldticketid "
+ " AND tr.fldstarttime IS NOT NULL "
+ " AND tr.fldticketid = ts.fldticketid "
+ " AND tr.fldstarttime IS NOT NULL "
+ " AND flddatevoided IS NULL " + " UNION "
+ " SELECT NULL, " + " fldstart end_time, "
+ " flddate, " + " fldemployeeid "
+ " FROM saloniris.dbo.tblscheduling " + " UNION "
+ " SELECT fldend start_time, "
+ " NULL end_time, " + " flddate, "
+ " fldemployeeid "
+ " FROM saloniris.dbo.tblscheduling)";