hibernate 报表查询,实现行变列

http://5211422.iteye.com/blog/322542

这篇文章中介绍了sql语句实现的行变列.

但是如果使用Hibernate怎么来做呢,今天摸索了一天,终于小有所成,通过Hibernat的投影(Projections)实现了行变列的变态排版,同时还要考虑到分页的支持,做起来还是有点麻烦的.下面是具体代码:

 

 

public Map<String, Object> getSiteHistoryDataPageItems(final List<Variables> sitevars,
            final Date startdate, final Date enddate, final int start, final int limit) {
        return (Map<String, Object>) getHibernateTemplate().executeWithNativeSession(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException {
                // 设置查询条件
                DetachedCriteria detachedCriteria = DetachedCriteria.forClass(SiteData.class);
                detachedCriteria.add(Restrictions.in("variables", sitevars));

                // 行变列
                String[] columnAliases = new String[sitevars.size()];// 列别名
                Type[] types = new Type[sitevars.size()];//列类型
                StringBuilder sqlsb = new StringBuilder();//sql语句
                               // 遍历所有vars设置列别名
                for (int i = 0; i < sitevars.size(); i++) {
                    columnAliases[i] = "field" + sitevars.get(i).getId();
                    types[i] = Hibernate.FLOAT;
                    sqlsb.append("sum(case variables_id when ");
                    sqlsb.append(sitevars.get(i).getId());
                    sqlsb.append(" then data_value end) as field");
                    sqlsb.append(sitevars.get(i).getId());
                    if (i != sitevars.size() - 1)
                        sqlsb.append(", ");
                }
                detachedCriteria.setProjection(Projections.projectionList().add(Projections.property("dataTime").as("data_time")).add(Projections.sqlGroupProjection(sqlsb.toString(), "data_time", columnAliases, types)));
                               // 这一行很重要主要用于对返回结果集的使用
                detachedCriteria.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);

                detachedCriteria.add(Restrictions.between("dataTime", startdate, enddate));
                detachedCriteria.addOrder(Order.desc("dataTime"));

                Criteria executableCriteria = detachedCriteria.getExecutableCriteria(session);
                // Get the orginal orderEntries
                OrderEntry[] orderEntries = HibernateUtils.getOrders(executableCriteria);
                // Remove the orders
                executableCriteria = HibernateUtils.removeOrders(executableCriteria);
                // get the original projection
                Projection projection = HibernateUtils.getProjection(executableCriteria);

                               // 由于要使用分页,这里返回行变列以后的记录总数
                int totalCount = ((Integer) executableCriteria.setProjection(Projections.countDistinct("dataTime")).uniqueResult())
                .intValue();

                executableCriteria.setProjection(projection);
                               // 需要再次设置一下结果转换器
                executableCriteria.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
                // Add the orginal orderEntries
                executableCriteria = HibernateUtils.addOrders(executableCriteria, orderEntries);

                List<Map> rows = new ArrayList<Map>();

                // 处理结果集
                List result = HibernateUtils.getPageResult(executableCriteria, start, limit);
                for (Object aResult : result) {
                    Map<String, Object> row = new HashMap<String, Object>();

                    Map map = (Map) aResult;

                    // 时间列
                    Date date = (Date) map.get("data_time");
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    row.put("date", sdf.format(date));

                    // 变量列
                    for (Variables v : sitevars) {
                        Float f = (Float) map.get("field" + v.getId());
                        row.put("field" + v.getId(), f);
                    }
                    rows.add(row);
                }

                Map<String, Object> map = new HashMap<String, Object>();
                map.put("totalCount", totalCount);
                map.put("rows", rows);

                return map;
            }
        });
    }