Odd SQL generated for one-to-many

skanjo · **Joined:** Fri Jun 09, 2006 4:27 pm **Posts:** 10

Environment:

I want to create a unidirectional one-to-many from an Employment record to BenefitEligibility records. So I created the following mapping for Employment:

Code:

<hibernate-mapping schema="SANDBOX">

   <typedef class="hr.employee.dao.hibernate.usertype.EmployeeIdParameterizedUserType" name="encrypted_id">
      <param name="encoding">encrypted</param>
   </typedef>

   <class 
      name="hr.employee.domain.Employment" 
      table="PSFT_JOB">

      <id 
         name="id"
         column="EMPLID"
         type="encrypted_id"/>

      <set 
         name="benefitEligibilities" 
         cascade="all" 
         inverse="true">
         <key column="EMPLID" not-null="true"/>
         <one-to-many class="hr.employee.domain.BenefitEligibility"/>
      </set>

      <!-- snipped the rest -->
   </class>

</hibernate-mapping>

and for BenefitEligibility I created the following mapping:

Code:

<hibernate-mapping schema="SANDBOX">

   <typedef class="hr.employee.dao.hibernate.usertype.EmployeeIdParameterizedUserType" name="encrypted_id">
      <param name="encoding">encrypted</param>
   </typedef>

   <class 
      name="hr.employee.domain.BenefitEligibility" 
      table="PSFT_ELIGIBILITY">
      
      <composite-id 
         name="id" 
         class="hr.employee.domain.BenefitEligibilityId">
         <key-property 
            name="elgibilityId" 
            column="ELIGIBILITY_ID" 
            type="integer"
            access="field"/>
         <key-property
            name="employeeId" 
            column="EMPLID" 
            type="encrypted_id"
            access="field"/>
      </composite-id>

      <property 
         name="eligibilityCode" 
         column="ELIGIBILITY_CD" 
         type="string"/>

   </class>

</hibernate-mapping>

This works fine and dandy for those cases when I get a BenefitEligibility record or when I lazy load a set of BenefitEligibility records from an Employment record. However, the SQL generated when performing a lazy load from an employment record is odd. I get the following:

Code:

    select
        benefiteli0_.EMPLID as EMPLID1_,
        benefiteli0_.ELIGIBILITY_ID as ELIGIBIL1_1_,
        benefiteli0_.ELIGIBILITY_ID as ELIGIBIL1_18_0_,
        benefiteli0_.EMPLID as EMPLID18_0_,
        benefiteli0_.ELIGIBILITY_CD as ELIGIBIL3_18_0_ 
    from
        PSFT_DATAMART.PSFT_ELIGIBILITY benefiteli0_ 
    where
        benefiteli0_.EMPLID=?

Has anybody seen this before? Have I not mapped this correctly or is this a bug? The records returned are correct. I just don't know why the primary key columns are duplicated in the query.

CWitherow · **Posted:** Fri Sep 22, 2006 3:34 pm

Look at the BenefitEligibility POJO. It has the composite key properties (elgibilityId and employeeId) AND the composite key object (id as hr.employee.domain.BenefitEligibilityId, which also contains properties for the composite key components) declared. As such the SQL is generated to populate all of these, hence duplication. From p63 in the H3 manual, this appears to be expected althought it doesn't mention exactly how the SQL will look.

Curtis ...

skanjo · **Joined:** Fri Jun 09, 2006 4:27 pm **Posts:** 10

I am not using the mapped composite identifier approach here but rather the component as composite identifier approach. I have not duplicated my properties in both the id class and the domain class. The mapped attribute of composite-id defaults to false so Hibnernate should not be attempting to populate key fields on the domain object in addition to the same fields on the id object. However, this does provide a possible explanation as to why this SQL would be generated in the first place.