[Hibernate] Error when sql query is been created

Prasad Iyer Wed, 24 Sep 2003 23:31:12 -0700

Hi,

I have this two joined-subclass in a mapping file.

This two joined-subclass has one-to-many relationship between them and is giving me a

problem, when I am trying to create a inner join on the tables

And following is the Hibernate query I want to generate

"from MultiplePersonalisedResponse as multiple inner join multiple.singlePersonalisedResponse as single"

Following is the mapping file :-

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
    <class name="com.webify.eligibility.BenefitResponse.BenefitResponse" table="w_ce_benefitresponse">
        <id name="id" column="id" length="36" type="string">
      <generator class="com.webify.eligibility.db.util.Uuid"/>
    </id>
       <property name="benefitResponseType" column="benefit_response_type" type="character"/>
        <property name="rawContent" column="raw_content" type="binary" length="8000"/>
        <property name="userid" column="user_id" type="string" length="36"/>
        <property name="uploadDate" column="upload_date" type="date"/>
        <property name="status" column="status" type="string"/>

        

    <joined-subclass name="com.webify.eligibility.BenefitResponse.SinglePersonalisedResponse"
    table="w_ce_single_personalised_response">
    <key column="benefit_response_id"/>
        <component class="com.webify.eligibility.BenefitResponse.MemberInformation" name="memberInformation">
      <property name="gender" column="gender" type="character"/>
         <property name="groupID" column="group_id" type="string"/>
         <property name="coverageStatus" column="coverage_status" type="string"/>
         <property name="serviceStartDate" column="service_start_date" type="date"/>
         <property name="serviceEndDate" column="service_end_date" type="date"/>
         <property name="coverageStartDate" column="coverage_start_date" type="date"/>
        <property name="coverageEndDate" column="coverage_end_date" type="date"/>
         <property name="coverageLevel" column="coverage_level" type="string"/>
        <property name="memberFirstName" column="member_first_name" type="string"/>
        <property name="memberLastName" column="member_last_name" type="string"/>
         <property name="identifier1" column="member_identifier1" type="string"/>
         <property name="identifier2" column="member_identifier2" type="string"/>
         <property name="dateOfBirth" column="member_dateOfBirth" type="date"/>
         <property name="payer" column="member_payer" type="string"/>
         <property name="relationship" column="member_relationship" type="character"/>
    </component>


       <component class="com.webify.eligibility.BenefitResponse.PrimaryCarePhysician" name="primaryCarePhysician">
         <property name="name" column="name" type="string"/>
        <property name="pcdID" column="pcd_id" type="string"/>
        <property name="phoneNo" column="phone_no" type="string"/>
         <property name="email" column="email" type="string"/>
         <property name="address" column="address" type="string"/>
       </component>

       <property name="personalisedContent" column="personalised_content" type="binary" length="8000"/>
        <property name="creationDate" column="creation_date" type="date"/>
  <many-to-one class="com.webify.eligibility.BenefitResponse.MultiplePersonalisedResponse"
   name="multiplePersonalisedResponse"
   column="multiple_personalised_response_id"/>
        <bag name="responseBenefitInformations" cascade="all" inverse="true" lazy="true">
        <key column="single_personalised_response_id"/>
        <one-to-many class="com.webify.eligibility.BenefitResponse.ResponseBenefitInformation"/>
        </bag>

    </joined-subclass>

        

        

    <joined-subclass name="com.webify.eligibility.BenefitResponse.MultiplePersonalisedResponse" table="w_ce_multiple_personalised_response">
    <key column="benefit_response_id"/>
        <bag name="singlePersonalisedResponse" inverse="true" lazy="true" cascade="all">
        <key column="multiple_personalised_response_id" />
        <one-to-many class="com.webify.eligibility.BenefitResponse.SinglePersonalisedResponse" />
        </bag>
        <property name="personalisedContent" column="personalised_content" type="binary" length="8000"/>
        <property name="creationDate" column="creation_date" type="date"/>
    </joined-subclass>

        

     </class>

</hibernate-mapping>

And following is the SQL query been generated

select
multiple.id as id0_,
sin0_.id as id1_,
multiple_1.personalised_content as personal2_14_0_,
multiple_1.creation_date as creation3_14_0_,
multiple.benefit_response_type as benefit_2_12_0_,
multiple.raw_content as raw_cont3_12_0_,
multiple.user_id as user_id12_0_,
multiple.upload_date as upload_d5_12_0_,
multiple.status as status12_0_,
sin0__1.gender as gender13_1_,
sin0__1.group_id as group_id13_1_,
sin0__1.coverage_status as coverage4_13_1_,
sin0__1.service_start_date as service_5_13_1_,
sin0__1.service_end_date as service_6_13_1_,
sin0__1.coverage_start_date as coverage7_13_1_,
sin0__1.coverage_end_date as coverage8_13_1_,
sin0__1.coverage_level as coverage9_13_1_,
sin0__1.member_first_name as member_10_13_1_,
sin0__1.member_last_name as member_11_13_1_,
sin0__1.member_identifier1 as member_12_13_1_,
sin0__1.member_identifier2 as member_13_13_1_,
sin0__1.member_dateOfBirth as member_14_13_1_,
sin0__1.member_payer as member_15_13_1_,
sin0__1.member_relationship as member_16_13_1_,
sin0__1.name as name13_1_,
sin0__1.pcd_id as pcd_id13_1_,
sin0__1.phone_no as phone_no13_1_,
sin0__1.email as email13_1_,
sin0__1.address as address13_1_,
sin0__1.personalised_content as persona22_13_1_,
sin0__1.creation_date as creatio23_13_1_,
sin0__1.multiple_personalised_response_id as multipl24_13_1_,
sin0_.benefit_response_type as benefit_2_12_1_,
sin0_.raw_content as raw_cont3_12_1_,                         --This is were the error is getting generated
sin0_.user_id as user_id12_1_,                                       --This is were the error is getting generated
sin0_.upload_date as upload_d5_12_1_,                         --This is were the error is getting generated
sin0_.status as status12_1_                                             --This is were the error is getting generated
from w_ce_benefitresponse multiple inner join w_ce_multiple_personalised_response multiple_1
on multiple.id=multiple_1.benefit_response_id, w_ce_single_personalised_response sin0_
left outer join w_ce_single_personalised_response sin0__1 on sin0_.id=sin0__1.benefit_response_id
where multiple.id=sin0_.multiple_personalised_response_id

I get the error