使用Hibernate条件转换内部查询

Question

我正在尝试将下面的嵌套查询转换为Hibernate Criteria，但无法做到。实际上，尝试对结果集中的行进行计数和求和。

有人有什么想法吗？

提前谢谢。

sql prettyprint-override"> 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;

下面是为上述查询编写的标准，

>

对于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);

对于内部查询填充结果集并传递给主查询，

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

但如何将这个子查询传递给主查询还不清楚。

Answer 1

使用

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

或者您可以根据需求使用其他版本的sqlRetiction

我认为这个方法很好。或者由于您没有提供与代码相关的详细信息，因此没有回答。