hibernate-hql 实战小结

发布于 2024-02-10 01:57:46 字数 4812 浏览 16 评论 0

查询报名记录

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 条:

汇总报名记录,并按照统计次数降序:

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

效果如下:

如上 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

转换成 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";

结果如下:

可以看到,分页成功了:

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。
列表为空,暂无数据

关于作者

0 文章
0 评论
23 人气
更多

推荐作者

沧笙踏歌

文章 0 评论 0

山田美奈子

文章 0 评论 0

佚名

文章 0 评论 0

岁月无声

文章 0 评论 0

暗藏城府

文章 0 评论 0

    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
    原文