sql查询转换为hibernate查询的问题

Question

嗨，我有喜欢按月表jan，feb..dec和我有定位和动作计数字段在每个位置重复在每个表中。

我粗略地用SQL编写了这个查询，我有Month域对象，现在我必须将它转换为Hibernate查询(HQL或Criteria api或其他任何..)。我如何转换它？月数是以列表的形式提供的，并且是可变的，因此下面的sql来自这个列表monthsToQuery = [oct,nov,dec,jan]。这也是变量列表。它可以是feb、mar、apr或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

我在做左连接，因为我不想在不同的月份之间丢失任何位置。

另外:我还有一个日期范围作为输入。到目前为止，我从范围中获得了一个月份列表，并分别获得了每个月的结果。我需要编写查询，以便在1个查询中给出最终所需的结果。这是我到目前为止所拥有的：

// 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
    }

我读到了一些地方，我可以在条件中使用别名，而不是嵌套条件。我是个新手..还有人知道更多吗？

Answer 1

如果您的模型使用继承

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")))

或者一个接口

class Month implements HasActionCount
{
    string location;
    int actionCount;
}

session.CreateCriteria(HasActionCount.class)
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.GroupProperty("Location"))
        .Add(Projections.Sum("ActionCount")))

更新:以下代码适用于NHibernate和SQLite (应该也适用于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();