hql语句总结

1、使用in且按照in里面的集合顺序查询结果

 

List<Integer> employeeIds = dao.getHibernateTemplate().findByNamedParam("select e.employeeId from Employee e where e.workId in (:teamWorkIds) order by FIELD(e.workId, :teamWorkIds)", "teamWorkIds", teamWorkIds);

 

注意这里的order by FIELD

 

2、当取某一个对象关联的一个对象作为插叙结果，有可能会出现以下异常：

Hibernate异常：query specified join fetching, but the owner of the fetched association was not present in the select list

 

这句意思是：你fetch的东东，需要在返回对象中存在，如果不存在需要把fetch删除，直接使用join

 

3、选择不重复

String hql = "select distinct m.project from Milepost m left join  m.project "; 

上面这个结合第二条看

 

4、group by 使用

select sum(l.costWorktime) from Log l left join l.project p left join l.employee e where p.projectId=? and l.workDate between ? and ? group by e.employeeId order by sum(l.costWorktime)

 

还有就是mysql的between他是闭区间的

 

5、对于hibernate中使用count(distinct 列表1，列表2)....group by 出错

 

网络查询原因，http://blog.csdn.net/hymer2011/article/details/6588040

hql有如下两个限制：

HQL（SQL）不支持select count(distinct x, y) from xx;

HQL不支持select count(*) from (select distinct x, y from xx);

即：HQL不支持from语句中的子查询

  可以支持单个的count(distinct x)

 

后来改成这样使用原生的：

 

String sql1 = "select count(distinct l.project_id, l.sub_project_id), l.employee_id from tbl_log l where l.work_date between ? and ? and l.employee_id in (:employeeIds) group by l.employee_id order by FIELD(l.employee_id, :employeeIds)";
				return session.createSQLQuery(sql1).setDate(0, fromDate).setDate(1, toDate).setParameterList("employeeIds", employeeIds).list();

按月来统计：

select sum(l.costWorktime), DATE_FORMAT(l.workDate, '%Y-%m') from Log l where 1=1 group by DATE_FORMAT(l.workDate, '%Y-%m')