converting inner query using Hibernate Criteria

Question

I'm trying to convert below nested query into Hibernate Criteria, but not able to do. Actually, trying to count and sum the rows from the result set.

so anybody have any ideas ?

Thanks in advance.

 SELECT DISTINCT host_platform,
      host_manufacturer,
      COUNT(phy_1),
      COUNT (vir_1),
      SUM (server_cost) AS server_cost,
      SUM (book_value) AS book_value,
      SUM (maintenance_cost) AS main_cost,
      SUM (current_cost) AS curr_cost 
 FROM (SELECT DISTINCT host_platform,
      host_manufacturer,
      phy_1,
      vir_1,
      server_cost,
      book_value,
      maintenance_cost,
      current_cost
         FROM tf_server_list_v
         where host_platform = 'UNIX')
                --ipaddress = '142.125.21.70')
         GROUP BY host_platform, host_manufacturer;

Here below is the criteria written for above query,

1. For select main query,

   Criteria mainCriteria = getSession().createCriteria(TfServerListNew.Class);
   
   ProjectionList projOSList1 = Projections.projectionList();
   projOSList1.add(Projections.property("hostPlatform"), "hostPlatform");
   ProjectionList projectionList1 = Projections.projectionList();
   projectionList1.add(Projections.distinct(projOSList));
   projectionList1.add(Projections.alias(Projections.property("hostManufacturer"), "hostManufacturer"));
   projectionList1.add(Projections.alias(Projections.count("physicalFlag"), "physical"));
   projectionList1.add(Projections.alias(Projections.count("virtualFlag"), "virtual"));
   projectionList1.add(Projections.alias(Projections.sum("serverCost"), "serverCost"));
   projectionList1.add(Projections.alias(Projections.sum("bookValue"), "bookValue"));
   projectionList1.add(Projections.alias(Projections.sum("mainCost"), "mainCost"));
   projectionList1.add(Projections.alias(Projections.sum("currCost"), "currCost"));
   
   mainCriteria.setProjection(projectionList1);
   

2. For inner query to populate result set and pass to main query,

   final DetachedCriteria criteria = DetachedCriteria.forClass(TfServerListNew.class);
   ProjectionList projOSList = Projections.projectionList();
   projOSList.add(Projections.property("hostPlatform"), "hostPlatform");
   ProjectionList projectionList = Projections.projectionList();
   projectionList.add(Projections.distinct(projOSList));
   projectionList.add(Projections.alias(Projections.property("hostManufacturer"), "hostManufacturer"));
   projectionList.add(Projections.alias(Projections.property("physicalFlag"), "physical"));
   projectionList.add(Projections.alias(Projections.property("virtualFlag"), "virtual"));
   projectionList.add(Projections.alias(Projections.property("serverCost"), "serverCost"));
   projectionList.add(Projections.alias(Projections.property("bookValue"), "bookValue"));
   projectionList.add(Projections.alias(Projections.property("mainCost"), "mainCost"));
   projectionList.add(Projections.alias(Projections.property("currCost"), "currCost"));
   

But how to pass this subquery to main query is not getting.

Answer 1

Use

mainCriteria.add(Restrictions.sqlRestriction("your nested query"));

or you can use other versions of sqlRestriction based on requirement

I think This method is good one.Or as you have not provided details no answer related to your code.