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

Question

<?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>

Answer 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">
<!-- 
    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>

Answer 2

<?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>

Answer 3

<?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>

Answer 4

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

Answer 5

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=?

Answer 6

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。

Answer 7

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;
}}

Answer 8

搞定！！！！！！！谢谢这位老兄。
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     应为username2、用类的概念写。
就行。

Answer 9

多表操作 不建议用Hibernate

Answer 10

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=?也行。