Hibernate Subqueries

Question

I have a situation where I need to convert a query like :-

select hostname, avg(cpu_utilization_percentage) from cpu_utilization where timestamp In (select distinct(timestamp) from report.cpu_utilization order by timestamp desc limit 6) group by hostname

Now, this data I want to fetch using hibernate so I have used Subqueries:-

// For inner Query

DetachedCriteria subquery = DetachedCriteria.forClass(CpuUtilizationDTO.class);
subquery.setProjection(Projections.distinct(Projections.property("timeStamp"))).addOrder(Order.desc("timeStamp"));
subquery.getExecutableCriteria(session).setMaxResults(6);

// For Outer Query

Criteria query = session.createCriteria(CpuUtilizationDTO.class);
ProjectionList list = Projections.projectionList();
        list.add(Projections.groupProperty("hostName"));
        list.add(Projections.avg("cpuUtilizationpercentage"));
        query.setProjection(list);
List<Object[]> obj= (List<Object[]>)hibernateTemplate.findByCriteria(query);ction(list);

//Now to add subquery into main query I am using

query.add(Subqueries.propertyIn("timeStamp", subquery));

But everytime I am getting the average of entire data. Can anyone please help that where did I miss?

Hibernate Subqueries

0 Answers0