关于hql中部分关联字段为空时,不报错而直接查不出字段为空的记录

当在HQL查询中涉及关联字段且该字段为空时,Hibernate会默认使用内连接导致空记录无法查询到。这是因为使用实体的点号(.)方式隐式触发了内连接,为了解决这个问题,可以改用外连接显式处理空字段的关联查询。
 select b.listNo,b.appGNo,b.trGno,b.complex,b.commName,b.commSpec,b.tradeUnit,b.tradeQty,b.unit,b.qty,b.note,
                 c.listNo,c.appGNo,c.trGno,c.complex,c.commName,c.commSpec,c.tradeUnit,c.tradeQty,c.unit,c.qty,c.note,
                 a.companyCode,a.companyName,a.billNo, 
                 a.seqNo,a.appNo,a.declareState ,a.messageIndentureType 
                 from  MessageIndentureHead a , KHMessageIndenture b , GYSMessageIndenture c  where a.id = c.messageIndentureHead.id and a.id = b.messageIndentureHead.id 
                 and ((a.messageIndentureType = 1 and b.listNo = c.outNo) 
                 or (a.messageIndentureType = 0 and c.listNo = b.outNo))

如上hql代码,查询,会出现先TradeUnit为字段空的记录将不会被查询出来。(MessageIndentureHead拥有对tradeunit的unit表的单向一对多关联)


注意到hibernate根据hql输出的sql如下:

select khmessagei1_.listNo as col_0_0_, khmessagei1_.appGNo as col_1_0_, khmessagei1_.trGno as col_2_0_,
 khmessagei1_.complex as col_3_0_, khmessagei1_.commName as col_4_0_, khmessagei1_.commSpec as col_5_0_, 
khmessagei1_.tradeUnit_code as col_6_0_, khmessagei1_.tradeQty as col_7_0_, khmessagei1_.unit_code as col_8_0_,
 khmessagei1_.qty as col_9_0_, khmessagei1_.note as col_10_0_, gysmessage2_.listNo as col_11_0_, gysmessage2_.appGNo as col_12_0_,
 gysmessage2_.trGno as col_13_0_, gysmessage2_.complex as col_14_0_, gysmessage2_.commName as col_15_0_, 
gysmessage2_.commSpec as col_16_0_, gysmessage2_.tradeUnit_code as col_17_0_, gysmessage2_.tradeQty as col_18_0_,
 gysmessage2_.unit_code as col_19_0_, gysmessage2_.qty as col_20_0_, gysmessage2_.note as col_21_0_,
 messageind0_.companyCode as col_22_0_, messageind0_.companyName as col_23_0_, messageind0_.billNo as col_24_0_,
 messageind0_.seqNo as col_25_0_, messageind0_.appNo as col_26_0_, messageind0_.declareState as col_27_0_,
 messageind0_.messageIndentureType as col_28_0_, unit3_.code as code90_0_, unit4_.code as code90_1_, unit5_.code as code90_2_,
 unit6_.code as code90_3_, unit3_.isOut as isOut90_0_, unit3_.modifyDate as modifyDate90_0_, unit3_.modifyUser as modifyUser90_0_,
 unit3_.name as name90_0_, unit4_.isOut as isOut90_1_, unit4_.modifyDate as modifyDate90_1_, unit4_.modifyUser as modifyUser90_1_, 
unit4_.name as name90_1_, unit5_.isOut as isOut90_2_, unit5_.modifyDate as modifyDate90_2_, unit5_.modifyUser as modifyUser90_2_, 
unit5_.name as name90_2_, unit6_.isOut as isOut90_3_, unit6_.modifyDate as modifyDate90_3_, unit6_.modifyUser as modifyUser90_3_,
 unit6_.name as name90_3_ 
from MessageIndentureHead messageind0_ cross join KHMessageIndenture khmessagei1_ inner join Unit unit3_ on khmessagei1_.tradeUnit_code=unit3_.code
 inner join Unit unit4_ on khmessagei1_.unit_code=unit4_.code cross join GYSMessageIndenture gysmessage2_ 
inner join Unit unit5_ on gysmessage2_.tradeUnit_code=unit5_.code inner join Unit unit6_ on gysmessage2_.unit_code=unit6_.code 
where messageind0_.id=gysmessage2_.messageIndentureHead_id and messageind0_.id=khmessagei1_.messageIndentureHead_id and
 (messageind0_.messageIndentureType=1 and khmessagei1_.listNo=gysmessage2_.outNo or messageind0_.messageIndentureType=0 and
 gysmessage2_.listNo=khmessagei1_.outNo)

注意到hibernate输出的sql是对unit表使用内连接的,这就解释了为何该字段为空的记录不会输出。

原因:

使用a.xx,去查出a关联的表中的数据会导致隐式是用内连接(原因据说是与‘n+1’问题有关)

网上找的资料:

对于Hibernate3.2.3以后的版本,如果关联实体是单个实体或单个的组件属性,HQL依然可以似乎用英文点号(.)来隐式连接关联实体或组件;但如果关联实体是集合(包括1-N关联、N-N关联和集合元素时组件等),则必须使用xxx join来显示连接关联实体或组件。


解决办法:

显示声明为外连接连接该些字段

 select b.listNo,b.appGNo,b.trGno,b.complex,b.commName,b.commSpec,uc,b.tradeQty,ud,b.qty,b.note,
 c.listNo,c.appGNo,c.trGno,c.complex,c.commName,c.commSpec,ua,c.tradeQty,ub,null,c.qty,c.note,
 a.companyCode,a.companyName,a.billNo, 
 a.seqNo,a.appNo,a.declareState ,a.messageIndentureType 
 from  MessageIndentureHead a , KHMessageIndenture b , GYSMessageIndenture c
left join c.tradeUnit ua left join c.unit ub left join b.tradeUnit uc left join b.unit ud
  where a.id = c.messageIndentureHead.id and a.id = b.messageIndentureHead.id 


评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值