hibernate中怎么进行多表关联查询呢（本人初学，请大家多多指教）

[cyc123007512]

查询语句(惭愧了，这个弄成sql直接查询了)：
StringBuffer sql=new StringBuffer("select userid,username,deptname,dutyname,titlename,takeOfficeDate,telePhone,email")
.append(" from Employees e inner join Dept a on e.deptid=a.deptid ")
.append(" inner join Duty b on e.dutyid=b.dutyid")
.append(" left join Title c on e.titleid=c.titleid ");

主表employees对应的实例 Employees（存放的是姓名，部门编号，职务编号，职称编号）
表dept 类Dept(部门编号，部门名称)
表duty 类Duty(职务编号，职务名称)
表title 类Title(职称编号，职称名称)

要我想要在页面中显示
姓名 部门名称 职务名称 职称名称

查询方法：
public List<Employees> getSomeThing(查询条件1 2 3 ){
return (List<Employees>)getHibernateTemplate().find(）;
}

Employees这个里面没有deptname,dutyname,titlename这些属性啊，显示的时候怎么处理呢

不知道问题说清楚了没有，不过我想要的结果应该讲清楚了。请大家指教一下

[crazylaa]

多表关联就用sql么好了。

[hwlxjh]

使用对象，用Hibernate反射映射实体类，在实体映射文件中设置lazy为false，直接对象查询，可以加载出来！！！没有问题

[Mars_Ma_OK]

关注,顶起!!

我也有这样的问题想问! 楼下的帮忙教教

[LightningMan]

在主键表<many-to-xxx>结点属性里设置lazy="false"意思就是当session关闭后是否缓存加载和当前表有主外键关系的其他表的数据,这样你查一下主表的数据后,和这主表相关的其他表数据都查出来了,就可以用了!
Employees em=EmployeesBiz.xxxx();
要得到和Employees相关的表title的数据就可以em.title.getXXXXX();

[不姓贾_请叫我甄先生]

多表关联查询就是使用多对1或1对多进行配置实用了

[cyc123007512]

Employees配置文件：
<?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.zoye.oa.model.hr.Employees" table="employees" schema="dbo" catalog="zoyeoa">
<id name="userid" type="java.lang.String">
<column name="userid" length="4" />
<generator class="assigned" />
</id>
<many-to-one name="title" class="com.zoye.oa.model.hr.Title" fetch="select">
<column name="titleid" />
</many-to-one>
<many-to-one name="duty" class="com.zoye.oa.model.hr.Duty" fetch="select">
<column name="dutyid" />
</many-to-one>
<many-to-one name="dept" class="com.zoye.oa.model.hr.Dept" fetch="select">
<column name="deptid" />
</many-to-one>
<property name="username" type="java.lang.String">
<column name="username" length="8" not-null="true" />
</property>
<property name="password" type="java.lang.String">
<column name="password" length="32" not-null="true" />
</property>
<property name="sex" type="java.lang.Integer">
<column name="sex" not-null="true" />
</property>
<property name="takeOfficeDate" type="java.lang.String">
<column name="takeOfficeDate" length="10" />
</property>
<property name="email" type="java.lang.String">
<column name="email" length="64" />
</property>
<property name="demissionDate" type="java.lang.String">
<column name="demissionDate" length="10" />
</property>
<property name="telePhone" type="java.lang.String">
<column name="telePhone" length="16" />
</property>
<property name="remarks" type="java.lang.String">
<column name="remarks" length="512" />
</property>
<property name="officialDate" type="java.lang.String">
<column name="officialDate" length="10" />
</property>
<property name="educateBackground" type="java.lang.String">
<column name="educateBackground" length="8" />
</property>
<property name="idCard" type="java.lang.String">
<column name="idCard" length="18" />
</property>
<property name="birthDay" type="java.lang.String">
<column name="birthDay" length="10" />
</property>
<set name="depts" inverse="true">
<key>
<column name="deptmanager" length="4" />
</key>
<one-to-many class="com.zoye.oa.model.hr.Dept" />
</set>
</class>
</hibernate-mapping>

[cc785490225]

不懂，帮忙顶

[cc785490225]

Dept配置文件：
<?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.zoye.oa.model.hr.Dept" table="dept" schema="dbo" catalog="zoyeoa">
<id name="deptid" type="java.lang.Integer">
<column name="deptid" />
<generator class="assigned" />
</id>
<many-to-one name="employees" class="com.zoye.oa.model.hr.Employees" fetch="select">
<column name="deptmanager" length="4" />
</many-to-one>
<property name="deptname" type="java.lang.String">
<column name="deptname" length="32" />
</property>
<property name="deptsuper" type="java.lang.Integer">
<column name="deptsuper" />
</property>
<property name="deptstate" type="java.lang.Integer">
<column name="deptstate" not-null="true" />
</property>
<set name="titleDepts" inverse="true">
<key>
<column name="deptid" />
</key>
<one-to-many class="com.zoye.oa.model.hr.TitleDept" />
</set>
</class>
</hibernate-mapping>

[cyc123007512]

建表的时候一件建立了关联关系：
--员工表
create table employees(
userid char(4) primary key not null,
username varchar(8) not null,
password varchar(32) not null,
sex int not null,
deptid int,
dutyid int foreign key references duty(dutyid),
takeOfficeDate char(10),
email varchar(64),
demissionDate char(10),
telePhone varchar(16),
remarks varchar(512),
officialDate char(10),
titleid int foreign key references title(titleid),
educateBackground varchar(8),
idCard varchar(18),
birthDay char(10)
);

--部门表
create table dept(
deptid int primary key ,
deptname varchar(32),
deptmanager char(4) foreign key references employees(userid),
deptsuper int,
deptstate int not null default 1
);
--增加员工表的外键约束
alter table employees add constraint empdeptfkey foreign key (deptid) references dept (deptid);

执行下面这条语句都报错：
select e.userid,e.username,a.deptname from Employees e left join Dept a on e.deptid=a.deptid


org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: on near line 1, column 94 [select e.userid,e.username,a.deptname from com.zoye.oa.model.hr.Employees e left join Dept a on e.deptid=a.deptid ]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 94 [select e.userid,e.username,a.deptname from com.zoye.oa.model.hr.Employees e left join Dept a on e.deptid=a.deptid ]
org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:642)
org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424)


救命啊，搞了好久了

[shuangfang]

就是使用对象呀

[cc785490225]

这个是查到了啊，他说的是展示。Employees 没有deptid,deptname属性，只有Dept这个实体的属性，在页面上怎么解析Employees 中的Dept中的deptname和deptid
[Quote=引用 17 楼 lightningman 的回复:]
在主键表<many-to-xxx>结点属性里设置lazy="false"意思就是当session关闭后是否缓存加载和当前表有主外键关系的其他表的数据,这样你查一下主表的数据后,和这主表相关的其他表数据都查出来了,就可以用了!
Employees em=EmployeesBiz.xxxx();
要得到和Employees相关的表title的数据就可以em.title.getXXXXX();
[/Quote]

[shuangfang]

建立关联之后，不就在pojo中生成对象了吗，就用那个对象调用。把所有的东西发到我邮箱中1142816528我看下

[pingqunnayang]

select a.name as 姓名,a.dept.name as 部门名称，a.duty.name as 职务名称，a.title.name as 职称名字 from Employees a


这样不就可以了吗？ 你的职工表和其他三张表都是many-to-one的关系 直接查询就OK了

[welllit]

用sql直接查多方便，多表用hibernate不太好用

[AnnieBingshuang]

[Quote=引用 25 楼 pingqunnayang 的回复:]
select a.name as 姓名,a.dept.name as 部门名称，a.duty.name as 职务名称，a.title.name as 职称名字 from Employees a


这样不就可以了吗？ 你的职工表和其他三张表都是many-to-one的关系 直接查询就OK了
[/Quote]
同意！！

[cyc123007512]

要沉了，自己顶一下。
大家帮帮忙啊

[亲努力啊]

先用SQL写出来在转换成 HQL语句

[cyc123007512]

我主要想知道怎么展示
[Quote=引用 21 楼 jianglang_a 的回复:]
先用SQL写出来在转换成 HQL语句
[/Quote]

[kaisa316]

创建一个类，让他的构造方法包含你想要查询字段的类型及个数。比如

public class Util {
	private Integer userid;
	private String username;
	private String deptname;
	private String dutyname;
	private String titlename;
	private String takeOfficeDate;
	private String telePhone;
	private String email;
	
	public Util(Integer  userid,String username,String deptname,String dutyname,String titlename,String takeOfficeDate,String telePhone,String email) {
		this.userid = userid;
		this.username = username;
		this.deptname = deptname;
		this.dutyname = dutyname;
		this.titlename = titlename;
		this.takeOfficeDate = takeOfficeDate;
		this.telePhone = telePhone;
		this.email = email;
	}

	public Integer getUserid() {
		return userid;
	}

	public void setUserid(Integer userid) {
		this.userid = userid;
	}

	public String getUsername() {
		return username;
	}

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

	public String getDeptname() {
		return deptname;
	}

	public void setDeptname(String deptname) {
		this.deptname = deptname;
	}

	public String getDutyname() {
		return dutyname;
	}

	public void setDutyname(String dutyname) {
		this.dutyname = dutyname;
	}

	public String getTitlename() {
		return titlename;
	}

	public void setTitlename(String titlename) {
		this.titlename = titlename;
	}

	public String getTakeOfficeDate() {
		return takeOfficeDate;
	}

	public void setTakeOfficeDate(String takeOfficeDate) {
		this.takeOfficeDate = takeOfficeDate;
	}

	public String getTelePhone() {
		return telePhone;
	}

	public void setTelePhone(String telePhone) {
		this.telePhone = telePhone;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}
	
}

在查询的时候就可以这样写了 select new com.util.Util( userid,username,deptname,dutyname,titlename,takeOfficeDate,telePhone,email),在页面取值也用Util这个类的属性不就可以了吗。
Good luck!

[huangchbao]

public List<Object[]> findAll(){
String hql="select e.username,dept.deptname,duty.dutyname,title.titlename
from Employees e,Dept dept,Duty duty,Title title
where e.deptname=d.deptname and e.dutyname=duty.dutyname and
e.titlename=title.titlename";
List<Object[]> list=session.createQuery(hql).list();
}

然后用List<Object[]> list=类名.findAll();
然后你就可以用这个list了.
list[0]就是姓名
list[1]是部门名称
list[2]是职务名称
list[3]是职称名称