美文网首页
hibernate hql实战小结

hibernate hql实战小结

作者: 一一小知 | 来源:发表于2017-09-08 11:26 被阅读47次

    表关联查询汇总排名

    • 查询报名记录
    select o.id as id, o.name as org_name from t_sign_up t, t_student s, t_org o where t.student_id = s.id and s.org_id=o.id
    

    结果如下,可以看到1个单位下有4条报名记录,另外一个单位下有2条:

    image.png
    • 汇总报名记录,并按照统计次数降序:
    select o.id as id, o.name as org_name, count(t.id) as org_count from t_sign_up t, t_student s, t_org o where t.student_id = s.id and s.org_id=o.id group by o.name order by org_count desc
    

    效果如下:

    image.png

    如上sql,在Mysql测试成功,移植到hql时,需要给经过重名的orgCount加上引号,否则会报出现报错,报找不到orgCount字段,如下:

    select o.id as id, o.name as orgName, count(t.id) as orgCount from SignUp t, Student s, Org o where t.studentId = s.id and s.orgId=o.id group by o.name order by 'orgCount' desc
    

    数据比较多的时候,需要进行分页展示,相关的,我们要构建countQuery来查询结果总数目,在如上的sql上进行统计,如下:

    String countQuery = "select count(*) from (select o.id as id, o.name as orgName, count(t.id) as orgCount from SignUp t, Student s, Org o where t.studentId = s.id and s.orgId=o.id group by o.name) as result"
    

    执行过程中,会报错:

    九月 08, 2017 3:22:22 下午 org.hibernate.hql.ast.ErrorCounter reportError
    严重: line 1:61: unexpected token: count
    ERROR 2017 09 08 15:22:22 com.opensymphony.xwork2.util.logging.commons.CommonsLogger.error(CommonsLogger.java:42) Exception occurred during processing request: unexpected token: ( near line 1, column 22 [select count(*) from (select o.id as id, o.name as orgName, count(t.id) as orgCount from com.lmscn.lms.model.SignUp t, com.lmscn.lms.model.Student s, com.lmscn.lms.model.Org o where t.studentId = s.id and s.orgId=o.id group by o.name) as result]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 22 [select count(*) from (select o.id as id, o.name as orgName, count(t.id) as orgCount from com.lmscn.lms.model.SignUp t, com.lmscn.lms.model.Student s, com.lmscn.lms.model.Org o where t.studentId = s.id and s.orgId=o.id group by o.name) as result] 
    org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: ( near line 1, column 22 [select count(*) from (select o.id as id, o.name as orgName, count(t.id) as orgCount from com.lmscn.lms.model.SignUp t, com.lmscn.lms.model.Student s, com.lmscn.lms.model.Org o where t.studentId = s.id and s.orgId=o.id group by o.name) as result]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 22 [select count(*) from (select o.id as id, o.name as orgName, count(t.id) as orgCount from com.lmscn.lms.model.SignUp t, com.lmscn.lms.model.Student s, com.lmscn.lms.model.Org o where t.studentId = s.id and s.orgId=o.id group by o.name) as result]
        at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:660)
        at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
        at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)
        at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:339)
        at javacommon.base.BaseHibernateDao$PageQueryUtils.pageQuery(BaseHibernateDao.java:151)
        at javacommon.base.BaseHibernateDao$PageQueryUtils.access$1(BaseHibernateDao.java:147)
        at javacommon.base.BaseHibernateDao.pageQuery(BaseHibernateDao.java:121)
    

    还是hibernate解析过程中出了问题,主要是对中间产生的result虚拟临时表没有办法识别,难道这就要放弃,只能使用JDBC来进行分页了?
    不然!经过思考,我们统计count的目的就是为了得到总数,也有一个折中的办法,如下:
    使用distinct进行去重,这样查到两条记录(也就是说共有两个单位),如下:

    select count(distinct o.id) from t_sign_up t, t_student s, t_org o where t.student_id = s.id and s.org_id=o.id
    
    image.png

    转换成hql,如下:

    String countQuery = "select count(distinct o.id) from SignUp t, Student s, Org o where t.studentId = s.id and s.orgId=o.id";
    

    结果如下:

    image.png

    可以看到,分页成功了:

    image.png

    相关文章

      网友评论

          本文标题:hibernate hql实战小结

          本文链接:https://www.haomeiwen.com/subject/hvegjxtx.html