用 spring 提供的 HibernateDaoSupport 多表查询问题。

[jrg9522]

public List getMenuListByUsername(String username)
{
try {
String queryString = "from tb_MENU as menu,tb_USER as user,tb_ROLE as role,tb_PURVIEW as purview where (user.ROLE_ID=role.ID and role.ID=purview.ROLE_ID and purview.MENU_ID=menu.ID) and user.name=?";

return getHibernateTemplate().find(queryString, username);
} catch (RuntimeException re) {
log.error("find by property name failed", re);
throw re;
}
}

但产生异常。
org.hibernate.hql.ast.QuerySyntaxException: tb_MENU is not mapped [from tb_MENU as menu,tb_USER as user,tb_ROLE as role,tb_PURVIEW as purview where (user.ROLE_ID=role.ID and role.ID=purview.ROLE_ID and purview.MENU_ID=menu.ID) and user.name=?]

在hibernate+spring结合项目中如何用多表查询？？？？

[jrg9522]

from TbMenu as menu,TbUser as user,TbRole as role,TbPurview as purview where (user.role_id=role.id and role.id=purview.role_id and purview.menu_id=menu.id) and user.name=?

抛新的异常。
org.hibernate.QueryException: could not resolve property: role_id of: com.dstadmin.hibernate.TbUser [from com.dstadmin.hibernate.TbMenu as menu,com.dstadmin.hibernate.TbUser as user,com.dstadmin.hibernate.TbRole as role,com.dstadmin.hibernate.TbPurview as purview where (user.role_id=role.id and role.id=purview.role_id and purview.menu_id=menu.id) and user.name=?]

没有理解透hql。

[jrg9522]

hql是用类来查询的话。有外键关系的字段，类里头是直接是外键类的一个实例。怎么查询。
比如上面的TbUser类。

package com.dstadmin.hibernate;
public abstract class AbstractTbUser implements java.io.Serializable {

private Integer id;
private TbRole tbRole;
private String username;
private String password;

public AbstractTbUser() {
}

public AbstractTbUser(TbRole tbRole, String username, String password) {
this.tbRole = tbRole;
this.username = username;
this.password = password;
}

public Integer getId() {
return this.id;
}

public void setId(Integer id) {
this.id = id;
}

public TbRole getTbRole() {
return this.tbRole;
}

public void setTbRole(TbRole tbRole) {
this.tbRole = tbRole;
}

public String getUsername() {
return this.username;
}

public void setUsername(String username) {
this.username = username;
}

public String getPassword() {
return this.password;
}

public void setPassword(String password) {
this.password = password;
}

}

[jrg9522]

搞定！！！！！！！谢谢这位老兄。
from TbMenu as menu,TbUser as user,TbRole as role,TbPurview as purview where (user.tbRole=role and role=purview.tbRole and purview.tbMenu=menu) and user.username=?

原来错误
1、name 应为username

2、用类的概念写。
就行。

[xql80329]

多表操作 不建议用Hibernate

[jrg9522]

select menu from TbMenu as menu,TbUser as user,TbRole as role,TbPurview as purview where (user.tbRole.id=role.id and role.id=purview.tbRole.id and purview.tbMenu.id=menu.id) and user.username=?

也行。

[睿音]

[Quote=引用楼主 jrg9522 的帖子:]
tb_MENU is not mapped
[/Quote]
tb_MENU配入mapping中了吗？

[jrg9522]

<?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">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
<class name="com.dstadmin.hibernate.TbMenu" table="tb_menu" catalog="db_dst">
<id name="id" type="java.lang.Integer">
<column name="id" />
<generator class="identity" />
</id>
<many-to-one name="tbMenu" class="com.dstadmin.hibernate.TbMenu" fetch="select">
<column name="parent_menu_id" not-null="true" />
</many-to-one>
<property name="name" type="java.lang.String">
<column name="name" length="45" not-null="true" />
</property>
<property name="icon" type="java.lang.String">
<column name="icon" length="45" not-null="true" />
</property>
<property name="action" type="java.lang.String">
<column name="action" length="45" not-null="true" />
</property>
<property name="target" type="java.lang.String">
<column name="target" length="45" not-null="true" />
</property>
<property name="expanded" type="java.lang.String">
<column name="expanded" length="45" not-null="true" />
</property>
<property name="type" type="java.lang.String">
<column name="type" length="45" not-null="true" />
</property>
<property name="orderId" type="java.lang.String">
<column name="order_id" length="45" not-null="true" />
</property>
<set name="tbPurviews" inverse="true">
<key>
<column name="menu_id" not-null="true" />
</key>
<one-to-many class="com.dstadmin.hibernate.TbPurview" />
</set>
<set name="tbMenus" inverse="true">
<key>
<column name="parent_menu_id" not-null="true" />
</key>
<one-to-many class="com.dstadmin.hibernate.TbMenu" />
</set>
</class>
</hibernate-mapping>

[jrg9522]

<?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">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
<class name="com.dstadmin.hibernate.TbPurview" table="tb_purview" catalog="db_dst">
<id name="id" type="java.lang.Integer">
<column name="id" />
<generator class="identity" />
</id>
<many-to-one name="tbMenu" class="com.dstadmin.hibernate.TbMenu" fetch="select">
<column name="menu_id" not-null="true" />
</many-to-one>
<many-to-one name="tbRole" class="com.dstadmin.hibernate.TbRole" fetch="select">
<column name="role_id" not-null="true" />
</many-to-one>
</class>
</hibernate-mapping>

[jrg9522]

<?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">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
<class name="com.dstadmin.hibernate.TbRole" table="tb_role" catalog="db_dst">
<id name="id" type="java.lang.Integer">
<column name="id" />
<generator class="identity" />
</id>
<property name="name" type="java.lang.String">
<column name="name" length="45" not-null="true" />
</property>
<set name="tbPurviews" inverse="true">
<key>
<column name="role_id" not-null="true" />
</key>
<one-to-many class="com.dstadmin.hibernate.TbPurview" />
</set>
<set name="tbUsers" inverse="true">
<key>
<column name="role_id" not-null="true" />
</key>
<one-to-many class="com.dstadmin.hibernate.TbUser" />
</set>
</class>
</hibernate-mapping>

[jrg9522]

<?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">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
<class name="com.dstadmin.hibernate.TbUser" table="tb_user" catalog="db_dst">
<id name="id" type="java.lang.Integer">
<column name="id" />
<generator class="identity" />
</id>
<many-to-one name="tbRole" class="com.dstadmin.hibernate.TbRole" fetch="select">
<column name="role_id" not-null="true" />
</many-to-one>
<property name="username" type="java.lang.String">
<column name="username" length="45" not-null="true" />
</property>
<property name="password" type="java.lang.String">
<column name="password" length="45" not-null="true" />
</property>
</class>
</hibernate-mapping>

[睿音]

注意下大小写~~~~！！
tb_MENU 与com.dstadmin.hibernate.TbMenu

[睿音]

呃，不是大小写问题。。。。发的太快了~~~~
是用类名来写看看~~~

[睿音]

from TbMenu as menu,TbUser as user,TbROLE as role,TbPURVIEW as purview where (user.ROLE_ID=role.ID and role.ID=purview.ROLE_ID and purview.MENU_ID=menu.ID) and user.name=?