在hibernate标准中使用sum和算术结果作为顺序键 [英] Using sum and arithmetic result as order key in hibernate criteria

问题描述

  SELECT anId，SUM（fieldA）AS A，SUM（fieldB） ）AS B，SUM（fieldA）+ SUM（fieldB）AS'total'FROM tableA GROUP BY anId ORDER BY'total'DESC LIMIT 5 
  

解决方案

以下条件应该可以做到这一点：

  Criteria criteria = hibernateSession 
 .createCriteria（YourEntity.class）; 
 criteria.setProjection（Projections 
 .projectionList（）
 .add（Projections.property（anId）。as（prop1））
 .add（Projections.sum （fieldA）。as（prop2））
 .add（Projections.sum（fieldB）。as（prop3））
 .add（Projections.sqlProjection（
sum（fieldA）+ sum（fieldB）as total，
 new String [] {total}，
 new Type [] {StandardBasicTypes.INTEGER}），total）
 .add（Projections.groupProperty（remarks）））; 
 criteria.addOrder（Order.desc（total））; 
 criteria.setMaxResults（5）; 
 criteria.setResultTransformer（Transformers.ALIAS_TO_ENTITY_MAP）; 
 List list = criteria.list（）; 
 for（Object object：list）{
 Map< Object，Object> map =（Map< Object，Object>）对象; 
 System.out.println（map）; 
} 
  

How can I express this query in hibernate criteria.

SELECT anId, SUM(fieldA) AS A, SUM(fieldB) AS B, SUM(fieldA)+SUM(fieldB) AS 'total' FROM tableA GROUP BY anId ORDER BY 'total' DESC LIMIT 5

解决方案

following criteria should do the trick

        Criteria criteria = hibernateSession
                .createCriteria(YourEntity.class);
        criteria.setProjection(Projections
                .projectionList()
                .add(Projections.property("anId").as("prop1"))
                .add(Projections.sum("fieldA").as("prop2"))
                .add(Projections.sum("fieldB").as("prop3"))
                .add(Projections.sqlProjection(
                        "sum(fieldA) + sum(fieldB) as total",
                        new String[] { "total" },
                        new Type[] { StandardBasicTypes.INTEGER }), "total")
                .add(Projections.groupProperty("remarks")));
        criteria.addOrder(Order.desc("total"));
        criteria.setMaxResults(5);
        criteria.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List list = criteria.list();
        for (Object object : list) {
            Map<Object, Object> map = (Map<Object, Object>) object;
            System.out.println(map);
        }

这篇关于在hibernate标准中使用sum和算术结果作为顺序键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！