How to convert a SQL query into hibernate criteria?

issue with sql query conversion to hibernate query

• Hi I have like month wise table jan, feb..dec and I have locatin and actioncount fields in each with location repeated in each table. I have this query written roughly in SQL, I have the month domain objects, Now I have to convert it to Hibernate Query (HQL or Criteria api or anything else..). How do I convert it? The count of months is provided as a list and is variable like so the below sql is from this list monthsToQuery = [oct,nov,dec,jan] .. this is also variable list. It can be [feb, mar, apr] or [jul] select loc, sum(tcount) from ( (select location as loc, sum(actioncount) as tcount from oct group by location) left-join (select location as loc, sum(actioncount) as tcount from nov group by location) left-join (select location as loc, sum(actioncount) as tcount from dec group by location) left-join (select location as loc, sum(actioncount) as tcount from jan group by location) ) group by loc I am doing left joins because I dont want to loose any locations among different months. Addition: I also have a date range as input. So far I am getting a list of months from the range and getting results for each month separatley. I need to write the query to give the final required result in 1 query. here is what I have until now: // sTblList - list of all month domains in the date range.. def getSummary(sTblList,SfromDate,StoDate,res_id, groupCol,sumCol){ try{ Date fromDate = new Date().parse("yyyy-MM-dd", SfromDate); Date toDate = new Date().parse("yyyy-MM-dd", StoDate); def resourceInstance=Resources.get(res_id); sTblList.each{ def OnemonthList=it.createCriteria().get { eq('graresource',resourceInstance) between('currentdate', fromDate, toDate) projections { sum(sumCol,'tcount') groupProperty(groupCol) } } return sumMap // sumMap should have all months results combined } I read some places that instead of nesting criterias I can also use alias in criteria. I am new to this.. does anyone know further?

• Answer:

  if your model uses inheritance abstract class Month { string location; int actionCount; } class January extends Month { } session.CreateCriteria(Month.class) .SetProjection(Projections.ProjectionList() .Add(Projections.GroupProperty("Location")) .Add(Projections.Sum("ActionCount"))) or an interface class Month implements HasActionCount { string location; int actionCount; } session.CreateCriteria(HasActionCount.class) .SetProjection(Projections.ProjectionList() .Add(Projections.GroupProperty("Location")) .Add(Projections.Sum("ActionCount"))) Updated: the following works for NHibernate and SQLite (should also work in Hibernate) class Month { public virtual int Id { get; set; } public virtual string Location { get; set; } public virtual int ActionCount { get; set; } } class January : Month { } class February : Month { } <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"> <class xmlns="urn:nhibernate-mapping-2.2" name="ConsoleApplication1.January, ConsoleApplication1" table="`January`"> <id name="Id"> <generator class="identity" /> </id> <property name="Location" /> <property name="ActionCount" /> </class> <class xmlns="urn:nhibernate-mapping-2.2" name="ConsoleApplication1.February, ConsoleApplication1" table="`February`"> <id name="Id"> <generator class="identity" /> </id> <property name="Location" /> <property name="ActionCount" /> </class> </hibernate-mapping> // works as expected IList<Month> months = session.CreateCriteria<Month>().List<Month>(); // returns the location and sum of each month though IList<Object[]> sums = (Object[])session.CreateCriteria<Month>() .SetProjection(Projections.ProjectionList() .Add(Projections.GroupProperty("Location")) .Add(Projections.Sum("ActionCount"))) .List();