This question is about how composite keys that use <key-many-to-one> mappings affect HQL queries. Obligatory hours searching, reading documentation and FAQs are already spent. ;)
Middlegen created my initial *.hbm.xml files based on my legacy database.
Snippets from the files are at the end of this post, I've excluded the non-key properties and other junk. Notice the <composite-id> id mappings to a PK class and the use of <key-many-to-one>. The POJO and PK are generated using hbm2java.
Now the behaviour I get for an HQL like the following which matches 4 records in the SubCodeDatum table, each with a different SubCode parent, but all with the same SystemCode parent.
Code:
"from SubCodeDatum subCodeDatum where subCodeDatum.dataElementValue like '%File%'"
Results in the following SQL:
Code:
[7/6/04 17:13:04:495 CDT] 14783e63 SystemOut O Hibernate: select subcodedat0_.CODE as CODE, subcodedat0_.SUB_CODE as SUB_CODE, subcodedat0_.BEGIN_DT as BEGIN_DT, subcodedat0_.DATA_ELEMENT_NAME as DATA_ELE4_, subcodedat0_.UPDATE_NU as UPDATE_NU, subcodedat0_.DATA_ELEMENT_VALUE as DATA_ELE6_, subcodedat0_.CREATE_DT as CREATE_DT, subcodedat0_.CREATE_USER as CREATE_U8_, subcodedat0_.MDFCTN_DT as MDFCTN_DT, subcodedat0_.MDFCTN_USER as MDFCTN_10_ from UI_SUB_CODE_DATA subcodedat0_ where (subcodedat0_.DATA_ELEMENT_VALUE like '%File%' )
[7/6/04 17:13:04:505 CDT] 14783e63 SystemOut O Hibernate: select systemcode0_.CODE as CODE0_, systemcode0_.UPDATE_NU as UPDATE_NU0_, systemcode0_.DESCRIPTION as DESCRIPT3_0_, systemcode0_.CREATE_DT as CREATE_DT0_, systemcode0_.CREATE_USER as CREATE_U5_0_, systemcode0_.MDFCTN_DT as MDFCTN_DT0_, systemcode0_.MDFCTN_USER as MDFCTN_U7_0_ from UI_SYSTEM_CODE systemcode0_ where systemcode0_.CODE=?
[7/6/04 17:13:04:515 CDT] 14783e63 SystemOut O Hibernate: select subcode0_.CODE as CODE0_, subcode0_.SUB_CODE as SUB_CODE0_, subcode0_.BEGIN_DT as BEGIN_DT0_, subcode0_.UPDATE_NU as UPDATE_NU0_, subcode0_.END_DT as END_DT0_, subcode0_.CREATE_DT as CREATE_DT0_, subcode0_.CREATE_USER as CREATE_U7_0_, subcode0_.MDFCTN_DT as MDFCTN_DT0_, subcode0_.MDFCTN_USER as MDFCTN_U9_0_ from UI_SUB_CODE subcode0_ where subcode0_.CODE=? and subcode0_.SUB_CODE=? and subcode0_.BEGIN_DT=?
[7/6/04 17:13:04:545 CDT] 14783e63 SystemOut O Hibernate: select subcode0_.CODE as CODE0_, subcode0_.SUB_CODE as SUB_CODE0_, subcode0_.BEGIN_DT as BEGIN_DT0_, subcode0_.UPDATE_NU as UPDATE_NU0_, subcode0_.END_DT as END_DT0_, subcode0_.CREATE_DT as CREATE_DT0_, subcode0_.CREATE_USER as CREATE_U7_0_, subcode0_.MDFCTN_DT as MDFCTN_DT0_, subcode0_.MDFCTN_USER as MDFCTN_U9_0_ from UI_SUB_CODE subcode0_ where subcode0_.CODE=? and subcode0_.SUB_CODE=? and subcode0_.BEGIN_DT=?
[7/6/04 17:13:04:545 CDT] 14783e63 SystemOut O Hibernate: select subcode0_.CODE as CODE0_, subcode0_.SUB_CODE as SUB_CODE0_, subcode0_.BEGIN_DT as BEGIN_DT0_, subcode0_.UPDATE_NU as UPDATE_NU0_, subcode0_.END_DT as END_DT0_, subcode0_.CREATE_DT as CREATE_DT0_, subcode0_.CREATE_USER as CREATE_U7_0_, subcode0_.MDFCTN_DT as MDFCTN_DT0_, subcode0_.MDFCTN_USER as MDFCTN_U9_0_ from UI_SUB_CODE subcode0_ where subcode0_.CODE=? and subcode0_.SUB_CODE=? and subcode0_.BEGIN_DT=?
[7/6/04 17:13:04:555 CDT] 14783e63 SystemOut O Hibernate: select subcode0_.CODE as CODE0_, subcode0_.SUB_CODE as SUB_CODE0_, subcode0_.BEGIN_DT as BEGIN_DT0_, subcode0_.UPDATE_NU as UPDATE_NU0_, subcode0_.END_DT as END_DT0_, subcode0_.CREATE_DT as CREATE_DT0_, subcode0_.CREATE_USER as CREATE_U7_0_, subcode0_.MDFCTN_DT as MDFCTN_DT0_, subcode0_.MDFCTN_USER as MDFCTN_U9_0_ from UI_SUB_CODE subcode0_ where subcode0_.CODE=? and subcode0_.SUB_CODE=? and subcode0_.BEGIN_DT=?
Looks like it is loading all the parent records too! Is this because the the Composite PK are defined to have references to the parent? From the <key-many-to-one> mapping?
If I were to replace the <key-many-to-one> mapping with <key-property> mappings and then add additional <many-to-one lazy="true" inverse="true"> associations, these extra SQL would not be executed?
Assuming that even works, what are the downsides to this approach? How could I get Middlegen to generate *.hbm.xml in that fashion?
For that matter, what is the point of <key-many-to-one> in the first place?
The composite id class structure I'm using here also makes it a
p.i.t.a. to query on an id attribute "owned" by the top most parent class. For example, at 3 deep I've got "subCodeDatum.comp_id.subCode.comp_id.systemCode.code = :code", and we've got tables much deeper coming up yet. I'm hoping to find something simpler to use. :)
Thank you for any assistance you can provide.
--gus
The XML snippets are:
SystemCode:
Code:
<class
name="xxx.SystemCode"
table="UI_SYSTEM_CODE"
>
<id
name="code"
type="java.lang.String"
column="CODE"
>
<generator class="assigned" />
</id>
<!-- bi-directional one-to-many association to SubCode -->
<bag
name="subCodes"
lazy="true"
inverse="true"
>
<key>
<column name="CODE" />
</key>
<one-to-many
class="xxx.SubCode"
/>
</bag>
SubCode:
Code:
<class
name="xxx.SubCode"
table="UI_SUB_CODE"
>
<composite-id name="comp_id" class="xxx.SubCodePK">
<!-- bi-directional many-to-one association to SystemCode -->
<key-many-to-one
name="systemCode"
class="xxx.SystemCode"
>
<column name="CODE" />
</key-many-to-one>
<key-property
name="subCode"
column="SUB_CODE"
type="java.lang.String"
length="30"
/>
<key-property
name="beginDt"
column="BEGIN_DT"
type="java.sql.Date"
length="10"
/>
</composite-id>
<!-- associations -->
<!-- bi-directional one-to-many association to SubCodeDatum -->
<bag
name="subCodeData"
lazy="true"
inverse="true"
>
<key>
<column name="CODE" />
<column name="SUB_CODE" />
<column name="BEGIN_DT" />
</key>
<one-to-many
class="xxx.SubCodeDatum"
/>
</bag>
</class>
SubCodeData
Code:
<class name="xxx.SubCodeDatum"
table="UI_SUB_CODE_DATA"
>
<composite-id name="comp_id" class="xxx.SubCodeDatumPK">
<!-- bi-directional many-to-one association to SubCode -->
<key-many-to-one
name="subCode"
class="xxx.SubCode"
>
<column name="CODE" />
<column name="SUB_CODE" />
<column name="BEGIN_DT" />
</key-many-to-one>
<key-property
name="dataElementName"
column="DATA_ELEMENT_NAME"
type="java.lang.String"
length="100"
/>
</composite-id>
<property
name="dataElementValue"
type="java.lang.String"
column="DATA_ELEMENT_VALUE"
not-null="true"
length="1000"
/>
<!-- associations -->
</class>
Login
FAQ
Search
