总结：Hibernate 2 通过外连接查询多张表的数据。

chxkyy

浏览: 420227 次
性别:
来自: 上海

最近访客更多访客>>

a316988959

luoyanbo

airu

3613426477

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

Java

Hibernate FP SQL XML REST

问题：

在Hibernate中，我们经常会碰到多张表通过外键连接来查询表中的数据，需要用到<many-to-one></many-to-one>,<one-to-many></one-to-many>等连接，特别是表之间的连接多了，如果我们通过延迟加载，或立即加载的话。就要多发很多的sql查询语句。

例：

EcssFormula.hbm.xml:

xml 代码

<class name="com.ecgit.ecss.data.EcssTFormula" table="ECSS_T_FORMULA" schema="ECCM">
<id name="formulaid" type="java.lang.String">
<column name="FORMULAID" length="50" />
<generator class="uuid.hex" />
id>
<many-to-one name="ecssTFormulaRestrictByFp" class="com.ecgit.ecss.data.EcssTFormulaRestrict" outer-join="true">
<column name="FP" length="50" />
many-to-one>
<many-to-one name="ecssTFormulaRestrictByFormularesultchildtype" class="com.ecgit.ecss.data.EcssTFormulaRestrict" outer-join="true">
<column name="FORMULARESULTCHILDTYPE" length="50" />
many-to-one>
<many-to-one name="ecssTFormulaRestrictByTp" class="com.ecgit.ecss.data.EcssTFormulaRestrict" outer-join="true" >
<column name="TP" length="50" />
many-to-one>
<many-to-one name="ecssTFormulaRestrictByPro" class="com.ecgit.ecss.data.EcssTFormulaRestrict" outer-join="true" >
<column name="PRO" length="50" />
many-to-one>
<many-to-one name="ecssTFormulaRestrictByCl" class="com.ecgit.ecss.data.EcssTFormulaRestrict" outer-join="true" >
<column name="CL" length="50" />
many-to-one>
<many-to-one name="ecssTFormulaRestrictByPer" class="com.ecgit.ecss.data.EcssTFormulaRestrict" outer-join="true">
<column name="PER" length="50" />
many-to-one>
class>

EcssTFormulaRestrict.hbm.xml:

xml 代码

<class name="com.ecgit.ecss.data.EcssTFormulaRestrict" table="ECSS_T_FORMULA_RESTRICT" schema="ECCM">
<id name="restrictid" type="java.lang.String">
<column name="RESTRICTID" length="50" />
<generator class="uuid.hex" />
id>
<property name="appoint" type="java.lang.String">
<column name="APPOINT" length="50" />
property>
<property name="isany" type="java.lang.String">
<column name="ISANY" length="10" />
property>
<property name="exclude" type="java.lang.String">
<column name="EXCLUDE" length="100" />
property>
<property name="limited" type="java.lang.String">
<column name="LIMITED" length="100" />
property>
<property name="isnull" type="java.lang.String">
<column name="ISNULL" length="10" />
property>
class>

ECSS_T_FORMULA与ECSS_T_FORMULA_RESTRICT表之间有六个多对一的连接。

因此如果我们不用外连接查询，要查询一个com.ecgit.ecss.data.EcssTFormula对象就要发送1+6（com.ecgit.ecss.data.EcssTFormulaRestrict）对象连接的语句。

在Hibernate 2中我们可以在<many-to-one></many-to-one>中用outer-join=true属性。

outer-join关键字有3个值，分别是true,false,auto,默认是auto。

true: 表示使用外连接抓取关联的内容，这里的意思是当使用load(EcssTFormula.class,“id“)时,Hibernate只生成一条SQL语句将EcssTFormula与他的连接EcssTFormulaRestrict全部初始化。
false:表示不使用外连接抓取关联的内容，当load(EcssTFormula.class,“id“)时，Hibernate生成两条SQL语句，一条查询EcssTFormula表，另5条查询EcssTFormulaRestrict表。这样的好处是可以设置延迟加载，此处要将EcssTFormulaRestrict类设置为lazy=true。
auto:具体是ture还是false看hibernate.cfg.xml中的配置

注意：如果使用HQL查询OrderLineItem，如 from EcssTFormulao where o.id='id'，总是不使用外部抓取，及outer-join失效

这样的话我们就不能通过外连接来查询了。

那么我们可以直接在HQL语句中用 left join 来强制实现外连接查询。

java 代码

public List findByProperty(String propertyName, Object value)
throws SorcererException {
log.debug("finding EcssTFormula instance with property: "
+ propertyName + ", value: " + value);
List list = new ArrayList();
try {
Session session = this.getSession();
String queryString = "from EcssTFormula as formula left join formula.ecssTFormulaRestrictByFp , formula.ecssTFormulaRestrictByFormularesultchildtype" +
" , formula.ecssTFormulaRestrictByTp , formula.ecssTFormulaRestrictByPro , formula.ecssTFormulaRestrictByPer , formula.ecssTFormulaRestrictByCl where formula."
+ propertyName + "=:"+propertyName;
Query queryObject = session.createQuery(queryString);
queryObject.setParameter(propertyName, value);
List tempList = queryObject.list();
for(int i=0;i
list.add(((Object[])tempList.get(i))[0]);
}
log.debug("size:"+list.size());
this.closeSessionIfNecessary(session);
} catch (RuntimeException re) {
log.error("find by property name failed", re);
throw new SorcererException("获得公式失败", re);
} catch (Exception e) {
e.printStackTrace();
throw new SorcererException("获得公式失败", e);
}
return list;
}

tempList 中存放的是一个Object数组，Object[7]，Object[0]为EcssTFormula的实例，后面的六个是EcssTFormulaRestrict的实例。这样查询的语句只是一个sql语句：

sql 代码

Hibernate: select this.FORMULAID as FORMULAID6_, this.FP as FP6_, this.FORMULARE
SULTCHILDTYPE as FORMULAR3_6_, this.TP as TP6_, this.PRO as PRO6_, this.CL as CL
6_, this.PER as PER6_, this.CALCULATEID as CALCULAT8_6_, this.FORMULANAME as FOR
MULAN9_6_, this.FORMULATYPE as FORMULA10_6_, this.FORMULARESULTTYPE as FORMULA11
_6_, this.FORMULAPRECISION as FORMULA12_6_, this.FORMULAMARKETDATE as FORMULA13_
6_, this.FORMULADATETYPE as FORMULA14_6_, this.DEFAULTVALUE as DEFAULT15_6_, thi
s.ISCALCULATE as ISCALCU16_6_, this.ISSYSTEMFORMULA as ISSYSTE17_6_, this.ISCONT
ACTID as ISCONTA18_6_, this.FORMULACONTENT as FORMULA19_6_, this.USERCONTENT as
USERCON20_6_, ecsstformu1_.RESTRICTID as RESTRICTID0_, ecsstformu1_.APPOINT as A
PPOINT0_, ecsstformu1_.ISANY as ISANY0_, ecsstformu1_.EXCLUDE as EXCLUDE0_, ecss
tformu1_.LIMITED as LIMITED0_, ecsstformu1_.ISNULL as ISNULL0_, ecsstformu2_.RES
TRICTID as RESTRICTID1_, ecsstformu2_.APPOINT as APPOINT1_, ecsstformu2_.ISANY a
s ISANY1_, ecsstformu2_.EXCLUDE as EXCLUDE1_, ecsstformu2_.LIMITED as LIMITED1_,
ecsstformu2_.ISNULL as ISNULL1_, ecsstformu3_.RESTRICTID as RESTRICTID2_, ecsst
formu3_.APPOINT as APPOINT2_, ecsstformu3_.ISANY as ISANY2_, ecsstformu3_.EXCLUD
E as EXCLUDE2_, ecsstformu3_.LIMITED as LIMITED2_, ecsstformu3_.ISNULL as ISNULL
2_, ecsstformu4_.RESTRICTID as RESTRICTID3_, ecsstformu4_.APPOINT as APPOINT3_,
ecsstformu4_.ISANY as ISANY3_, ecsstformu4_.EXCLUDE as EXCLUDE3_, ecsstformu4_.L
IMITED as LIMITED3_, ecsstformu4_.ISNULL as ISNULL3_, ecsstformu5_.RESTRICTID as
RESTRICTID4_, ecsstformu5_.APPOINT as APPOINT4_, ecsstformu5_.ISANY as ISANY4_,
ecsstformu5_.EXCLUDE as EXCLUDE4_, ecsstformu5_.LIMITED as LIMITED4_, ecsstform
u5_.ISNULL as ISNULL4_, ecsstformu6_.RESTRICTID as RESTRICTID5_, ecsstformu6_.AP
POINT as APPOINT5_, ecsstformu6_.ISANY as ISANY5_, ecsstformu6_.EXCLUDE as EXCLU
DE5_, ecsstformu6_.LIMITED as LIMITED5_, ecsstformu6_.ISNULL as ISNULL5_ from EC
CM.ECSS_T_FORMULA this, ECCM.ECSS_T_FORMULA_RESTRICT ecsstformu1_, ECCM.ECSS_T_F
ORMULA_RESTRICT ecsstformu2_, ECCM.ECSS_T_FORMULA_RESTRICT ecsstformu3_, ECCM.EC
SS_T_FORMULA_RESTRICT ecsstformu4_, ECCM.ECSS_T_FORMULA_RESTRICT ecsstformu5_, E
CCM.ECSS_T_FORMULA_RESTRICT ecsstformu6_ where 1=1 and this.FP=ecsstformu1_.REST
RICTID(+) and this.FORMULARESULTCHILDTYPE=ecsstformu2_.RESTRICTID(+) and this.TP
=ecsstformu3_.RESTRICTID(+) and this.PRO=ecsstformu4_.RESTRICTID(+) and this.CL=
ecsstformu5_.RESTRICTID(+) and this.PER=ecsstformu6_.RESTRICTID(+)

分享到：

eclipse 设置快捷键的地方 | Tomcat下用IE下载*.rar文件出现乱码的解决 ...

2007-12-10 12:28
浏览 9042
评论(0)
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论