SQL语句中new Map的使用和日期的处理

数据库结构：

 

获取每一年、每个月的dayCount总数:

 

Sql语句如下：

 最后的效果如：

项目中使用new Map:

select new Map(year(date) as year,month(date) as month,sum(dayCount) as total) from Counter group by year(date), month(date) order by year(date)

 

该查询返回了一个Map的对象，内容是别名与被选择的值组成的名-值映射。
[{total=3, month=1, year=2010}, {total=465, month=2, year=2010}...]

 

String hql = "select new Map(year(date) as year,month(date) as month,sum(dayCount) as total) from Counter group by year(date), month(date) order by year(date)";
		List list = this.baseDao.getHibernateTemplate().find(hql);
		Iterator it = list.iterator();
		List<Counters> countersList = new ArrayList<Counters>();
		while(it.hasNext()){
			Map map =(Map) it.next();				
			Counters counter = new Counters();
			counter.setYear((Integer)map.get("year"));
			counter.setMonth((Integer)map.get("month"));
			counter.setTotal((Long)map.get("total"));
			countersList.add(counter);
		}

 ...

另：

查出数据库中的最大日期，即最新的那条记录

select max(cou.date) from Counter cou

 最大日期的对象：

String hql = "from Counter counter where counter.date = (select max(cou.date) from Counter cou )";
List<Counter> list =  this.getBaseDao().getHibernateTemplate().find(hql);

 ...

评论

1 楼 cancan7 2012-03-27  

这样的查询有什么好处呢？