Hibernate-分组查询、聚合函数

daoweb

/* * 两列以上就封装成Object数组，一列就List 不能追加投影。否则会覆盖掉。最后一次有效 */
    public List testProjection() {
        // select avg(sal),max(sal),count(empno) from Emp group by dept
        return HibernateSessionFactory.getSession()
                .createCriteria(Dept.class)
                // .setProjection(Property.forName("dname"))
                .setProjection(
                        Projections
                                .projectionList()
                                // .add(Property.forName("dept").group())//封装的Dept对象,里面有延迟加载，由于Dept封装了Deptno
                                // .add(Property.forName("dept.deptno").group())
                                .add(Projections.groupProperty("dept.deptno"))
                                .add(Projections.avg("sal"))// 同下
                                .add(Projections.rowCount())//count(*)
                // .add(Property.forName("sal").avg())
                // .add(Property.forName("sal").max())
                // .add(Property.forName("sal").min())
                // .add(Property.forName("sal").count())

                ).list();

    }

biz数组

public List testProjection() {
        return dao.testProjection();
    }

testsvg

public static void main(String[] args) {
        Transaction tx = null;
        try {
            tx = HibernateSessionFactory.getSession().beginTransaction();
            DeptBiz biz = new DeptBiz();
            List result = biz.testProjection();
            for (Object o : result) {
                Object[] row=(Object[])o;
                //System.out.println(row[0]==null?"unknown":((Dept)row[0]).getDname()+"\t"+row[1]+"\t"+row[4]);
                System.out.println(row[0]+"\t"+row[1]+"\t"+row[4]);

            }
            tx.commit();
        } catch (HibernateException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        if (tx != null) {
            tx.rollback();

        }
    }