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