Hi I have 4 table.Namely - Tkt,BoxTkt,CusipTkt and TktAudTrl.
Tkt has 1-1 relationship with both BoxTkt and CusipTkt.
Tkt has 1- many relationship with TktAudTrl
BoxTkt and CusipTkt have share the same primary key of Tkt.

Now when I want to perform this following hql :
from TktAudTrl at join at.tkt where at.tkt.tktNo = ?

select query for both BoxTkt and CusipTkt is also fired.

I can stop that by making constrained = true in tkt.hbm.xml in one to one mapping for boxtkt.
But this makes BoxTKt anad CusipTkt NOT NULL in Tkt which is not the case.

How can I stop this?
Tkt.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Nov 5, 2007 4:49:42 PM by Hibernate Tools 3.1.0.beta5 -->
<hibernate-mapping>
<class name="com.citi.gicap.bpmbat.ver.dao.beans.Tkt" table="TKT" >
<id name="tktNo" type="long">
<column name="TKT_NO" precision="22" scale="0" />
<generator class="sequence">
<param name="sequence">TKT_SEQ</param>
</generator>
</id>
<many-to-one name="cusip" class="com.citi.gicap.bpmbat.ver.dao.beans.Cusip" fetch="select">
<column name="CUSIP_ID" precision="22" scale="0" />
</many-to-one>
<many-to-one name="box" class="com.citi.gicap.bpmbat.ver.dao.beans.Box" fetch="select">
<column name="BOX_ID" precision="22" scale="0" />
</many-to-one>
..........

<!-- Manually done -->
<one-to-one class="com.citi.gicap.bpmbat.ver.dao.beans.CusipTkt" name="cusipTkt"/>

.............
<!-- Manually done -->
<one-to-one class="com.citi.gicap.bpmbat.ver.dao.beans.BoxTkt" name="boxTkt" />

<set name="tktAudTrls" inverse="true">
<key>
<column name="TKT_NO" precision="22" scale="0" not-null="true" />
</key>
<one-to-many class="com.citi.gicap.bpmbat.ver.dao.beans.TktAudTrl" />
</set>
</class>
</hibernate-mapping>


BoxTkt.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Nov 5, 2007 4:49:42 PM by Hibernate Tools 3.1.0.beta5 -->
<hibernate-mapping>
<class name="com.citi.gicap.bpmbat.ver.dao.beans.BoxTkt" table="BOX_TKT" >
<id name="tktNo" type="long">
<column name="TKT_NO" precision="22" scale="0" />
<!-- Manually done -->
<generator class="assigned">
<param name="property">tkt</param>
</generator>
</id>

<!-- Manually done -->
<one-to-one name="tkt" class="com.citi.gicap.bpmbat.ver.dao.beans.Tkt" constrained="true"/>

..........
.......
</class>
</hibernate-mapping>



CusipTkt.hbm.xml
[code]
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Nov 5, 2007 4:49:42 PM by Hibernate Tools 3.1.0.beta5 -->
<hibernate-mapping>
<class name="com.citi.gicap.bpmbat.ver.dao.beans.CusipTkt" table="CUSIP_TKT" >
<id name="tktNo" type="long">
<column name="TKT_NO" precision="22" scale="0" />
<generator class="assigned" />
</id>
<!-- Manually done -->
<one-to-one name="tkt" class="com.citi.gicap.bpmbat.ver.dao.beans.Tkt" constrained="true"/>
..........
</class>
</hibernate-mapping>

[/b]

TKtAudTrl.hbm.xml


<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Nov 5, 2007 4:49:42 PM by Hibernate Tools 3.1.0.beta5 -->
<hibernate-mapping>
<class name="com.citi.gicap.bpmbat.ver.dao.beans.TktAudTrl" table="TKT_AUD_TRL" >
<id name="tktStaId" type="long">
<column name="TKT_STA_ID" precision="22" scale="0" />
<generator class="sequence">
<param name="sequence">TKT_AUD_TRL_SEQ</param>
</generator>
</id>
<many-to-one name="tkt" class="com.citi.gicap.bpmbat.ver.dao.beans.Tkt" fetch="select">
<column name="TKT_NO" precision="22" scale="0" not-null="true" />
</many-to-one>
............
........
</class>

<query name="Retrieve_AuditTrail_By_TicketNo">from TktAudTrl at left outer join at.mach mc join at.procAct join at.lkup join at.tkt where at.tkt.tktNo = ?</query>
</hibernate-mapping>




When I want to run the above specified name query these following 3 queries are generated:
1>>>

DEBUG SQL : select tktaudtrl0_.TKT_STA_ID as TKT1_7_0_, mach1_.MACH_ID as MACH1_37_1_, procact2_.PROC_ACT_ID as PROC1_18_2_, lkup3_.LKUP_ID as LKUP1_11_3_, tkt4_.TKT_NO as TKT1_28_4_, tktaudtrl0_.STA_ID as STA2_7_0_, tktaudtrl0_.MACH_ID as MACH3_7_0_, tktaudtrl0_.PROC_ACT_ID as PROC4_7_0_, tktaudtrl0_.TKT_NO as TKT5_7_0_, tktaudtrl0_.STRT_TS as STRT6_7_0_, tktaudtrl0_.END_TS as END7_7_0_, tktaudtrl0_.CRTE_BY as CRTE8_7_0_, tktaudtrl0_.CRTE_DT as CRTE9_7_0_, tktaudtrl0_.MOD_BY as MOD10_7_0_, tktaudtrl0_.MOD_DT as MOD11_7_0_, mach1_.PARAGON_AREA as PARAGON2_37_1_, mach1_.UNIT as UNIT37_1_, mach1_.MACH_NO as MACH4_37_1_, mach1_.MACH_TYP as MACH5_37_1_, mach1_.MACH_DESC as MACH6_37_1_, mach1_.CPTY as CPTY37_1_, mach1_.LOCA as LOCA37_1_, mach1_.DEL_FLAG as DEL9_37_1_, mach1_.CRTE_BY as CRTE10_37_1_, mach1_.CRTE_DT as CRTE11_37_1_, mach1_.MOD_BY as MOD12_37_1_, mach1_.MOD_DT as MOD13_37_1_, procact2_.DEPT_ID as DEPT2_18_2_, procact2_.ACT_DESC as ACT3_18_2_, procact2_.ACT_CD as ACT4_18_2_, procact2_.ESCAL_SLA_INT as ESCAL5_18_2_, procact2_.VER_NO as VER6_18_2_, procact2_.PAGE_NM_CD as PAGE7_18_2_, procact2_.SEQ_ID as SEQ8_18_2_, procact2_.DEL_FLAG as DEL9_18_2_, procact2_.CRTE_BY as CRTE10_18_2_, procact2_.CRTE_DT as CRTE11_18_2_, procact2_.MOD_BY as MOD12_18_2_, procact2_.MOD_DT as MOD13_18_2_, procact2_.DISP_ESCAL as DISP14_18_2_, lkup3_.TYP as TYP11_3_, lkup3_.LKUP_DESC as LKUP3_11_3_, lkup3_.CD as CD11_3_, lkup3_.DEL_FLAG as DEL5_11_3_, lkup3_.CRTE_BY as CRTE6_11_3_, lkup3_.CRTE_DT as CRTE7_11_3_, lkup3_.MOD_BY as MOD8_11_3_, lkup3_.MOD_DT as MOD9_11_3_, tkt4_.CUSIP_ID as CUSIP2_28_4_, tkt4_.BOX_ID as BOX3_28_4_, tkt4_.TKT_TYP as TKT4_28_4_, tkt4_.STA as STA28_4_, tkt4_.TKT_DEST as TKT6_28_4_, tkt4_.SPCL_INSTR as SPCL7_28_4_, tkt4_.STRT_TS as STRT8_28_4_, tkt4_.CPL_TS as CPL9_28_4_, tkt4_.DIVR_CNT as DIVR10_28_4_, tkt4_.MTLT_CNT as MTLT11_28_4_, tkt4_.EST_CPL_TM as EST12_28_4_, tkt4_.CHG_NO as CHG13_28_4_, tkt4_.ENVP_TYP as ENVP14_28_4_, tkt4_.MAIL_CLAS as MAIL15_28_4_, tkt4_.ISRT_CNT as ISRT16_28_4_, tkt4_.CRTE_BY as CRTE17_28_4_, tkt4_.CRTE_DT as CRTE18_28_4_, tkt4_.MOD_BY as MOD19_28_4_, tkt4_.MOD_DT as MOD20_28_4_, tkt4_.HOLD_RESN as HOLD21_28_4_, tkt4_.PARAGON_AREA_ID as PARAGON22_28_4_ from TKT_AUD_TRL tktaudtrl0_ left outer join MACH mach1_ on tktaudtrl0_.MACH_ID=mach1_.MACH_ID inner join PROC_ACT procact2_ on tktaudtrl0_.PROC_ACT_ID=procact2_.PROC_ACT_ID inner join LKUP lkup3_ on tktaudtrl0_.STA_ID=lkup3_.LKUP_ID inner join TKT tkt4_ on tktaudtrl0_.TKT_NO=tkt4_.TKT_NO where tktaudtrl0_.TKT_NO=?

2>>

DEBUG SQL : select cusiptkt0_.TKT_NO as TKT1_30_0_, cusiptkt0_.TRAY_CNT as TRAY2_30_0_, cusiptkt0_.SLA_DT as SLA3_30_0_, cusiptkt0_.ATTCH_DOC_TYP as ATTCH4_30_0_, cusiptkt0_.PROSTS_AVAIL_STA as PROSTS5_30_0_, cusiptkt0_.CRTE_BY as CRTE6_30_0_, cusiptkt0_.CRTE_DT as CRTE7_30_0_, cusiptkt0_.MOD_BY as MOD8_30_0_, cusiptkt0_.MOD_DT as MOD9_30_0_ from CUSIP_TKT cusiptkt0_ where cusiptkt0_.TKT_NO=?

3>>

28 Dec 2007 21:29:17,788 main 133379 CALV4B23 10.227.4.72 DEBUG SQL : select boxtkt0_.TKT_NO as TKT1_10_0_, boxtkt0_.MACH_ID as MACH2_10_0_, boxtkt0_.FORM_NO as FORM3_10_0_, boxtkt0_.PRT_PRTY as PRT

but the last 2 query should be avoided .
Please can anybody have any guess.How to stop it?

Thanks in advance

Two possibilities come to mind:

1. Are you sure that BoxTkt and CusipTkt shouldn't be modeled as subclasses of Tkt, either using table-per-hierarchy or table-per-class?

2. Try using the "lazy" attribute on the one-to-one relationships. It's there to solve exactly this problem.

Thanks for reply David,

Im sure BoxTkt and CusipTkt is not subclasses of Tkt.

Actually, <b>any one of the Tkt could be either BoxTkt or CusipTkt.</b>
So Tkt has one-to-one relationship with BoxTkt and CusipTkt.I think you can suggest any good approach here.

I am using table per class.I have tried lazy attribute in every possible one-to-one mapping.but without any result.

only one way I have able to stop the query generation i. e in Tkt.hbm.xml i have made one to one relation <b>coonstrained = true</b> with both Cusip and BoxTkt.Then During JOINING extra query is not firing.

But the problem is::::
<b>In Tkt, both BoxTkt and CusipTkt became NOT NULL/b> but that is not the purpose of my design.

So what can i do now? Could you plz suggest.