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 orderEntriesOrderEntry[] orderEntries = HibernateUtils.getOrders(executableCriteria);// Remove the ordersexecutableCriteria = HibernateUtils.removeOrders(executableCriteria);// get the original projectionProjection 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 orderEntriesexecutableCriteria = 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;}});}?
?
?
?
?
?
1 楼 tianju517 2009-06-06 感觉还是很麻烦 2 楼 魔力猫咪 2009-06-07 Hibernate用到这个份上，还不如直接SQL呢。 3 楼 bcw104 2009-06-08 魔力猫咪 写道Hibernate用到这个份上，还不如直接SQL呢。