issue with sql query conversion to hibernate query

Question

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?

I would start by normalizing the schema, and have a single table with a month column. Everything will be much easier this way. — JB Nizet
– JB Nizet, Commented Jan 13, 2012 at 10:26
we have separated the data into 12 tables for performance and summation purposes, it is a huge table otherwise with 50 million records. — pri_dev
– pri_dev, Commented Jan 13, 2012 at 23:12
With an index on the month column, it will probably be much faster than joining on all those tables. 50 million is no so much for a good database. — JB Nizet
– JB Nizet, Commented Jan 14, 2012 at 9:00
I do agree 50 million is not much for good database like oracle or some others.. but we want to make a generic schema and code accordingly which is good for any database.. like MySQL etc.. — pri_dev
– pri_dev, Commented Jan 14, 2012 at 20:53

Firo · Accepted Answer · 2012-01-18 17:46:58Z

1

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();

edited Jan 18, 2012 at 17:46

answered Jan 13, 2012 at 10:37

Firo

30.8k4 gold badges57 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

pri_dev Over a year ago

Thanks for the tip.. so the month domain class would store all all location and action counts from the 12 monthly tables is it? Because the inserts in the 12 tables are done through a nightly job that runs in the background, so that insert process has to be done even for this base domain?

Firo Over a year ago

you have to map all months seperatly but hibernate is smart enough to know that when you query for the base class it has to query for all inherited classes and you get back a list of inherited classes (january, ...). the sql generated should look like your query. same goes with interfaces, i am not sure if the interface has to be mapped though, maybe not.

pri_dev Over a year ago

I also just read on some related posts that the class month needs to be abstract?

Firo Over a year ago

dunno but it would makes sense

pri_dev Over a year ago

I am going to try this.. but i have another selector in the query which is a date range. I give that range also as an input to each of the nested query. Suppose I also put date field in the month class, so irrespective of the months in the range, it would query all 12 tables I suppose, which I want to avoid. I have added my implementation which I have so far..

|

Collectives™ on Stack Overflow

issue with sql query conversion to hibernate query

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related