Question

Hibernate如何写多对多的命名查询?

Hibernate中是不存在中间表的, 那我要在hbm.xml文件中写一个多对多命名查询应该怎么写呢?

======表关系: 多对多=====
t_kqinfo - 考勤表 - 对应的对象: TKqinfo
t_users - 用户表 - 对应的对象: TUsers
relationship_3 - 中间表

=======SQL语句=======
select kq.kqDate,kq.kqTime,kq.reason
from t_kqinfo kq,t_users u,relationship_3 rel
where kq.kqid = rel.kqid
and u.uid = rel.uid
and kq.kqid = rel.kqid
and u.uid = rel.uid
and u.uid = ?

===========下面是1对多的例子=================

比如:
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<query name="hql_A">
select r.roleName,count(u) from SysUser u
join u.sysRole r left join r.sysRight rt
where rt.rightText=? group by r.roleName having count(u)>? order by count(u)
</query>
</hibernate-mapping>
==========正确写法========
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<query name="HQL_showKQinfo">
select kq.kqDate,kq.kqTime,kq.reason
from TKqinfo as kq
left join kq.TUserses as u
where u.uid=?
</query>
</hibernate-mapping>
写成这样就对了。

Answer 1

搞得很别扭的，不如建立一下关联来得方便、性能也会被优化。

实质是执行ANSI SQL、不执行HQL。
Hibernate 3.6之后，可以使用Work来执行ANSI SQL执行。