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

 ...