many-to-many pilot error?

Chas · **Posted:** Fri Feb 24, 2006 1:18 pm

I'm new to hibernate and am having problems with a many-to-many relation. I have the following tables; surveys, linksurveyssections, surveysections, surveyquestions and linksurveyquestions. A survey can have multiple sections and a section can have multiple questions. Also a section can belong to multiple surveys and a question can belong to multiple sections. So there is a many to many relation between survey and surveysection. There is also a many-to-many relation between surveyquestion and survey section.

I'm using a link table between survey and section and between section and question. There may be a better way to do this? I'm all ears but even if there is a better way hibernate should be able to handle this as I have it defined.

If your willing to help and you need more info I'll be glad to supply it.

Hibernate seems to do the right thing for the link between survey and sections. Here is the sql from hibernate; as a sub-question is there a way to know the value assigned?

Code:

select linksurvey0_.surveyId_fk as surveyId2_1_
, linksurvey0_.linkId as linkId1_
, linksurvey0_.linkId as linkId9_0_
, linksurvey0_.surveyId_fk as surveyId2_9_0_
, linksurvey0_.surveySectionId_fk as surveySe3_9_0_ 
from linkSurveysSections linksurvey0_ 
where linksurvey0_.surveyId_fk=?

However the query for the link between survey sections and survey questions doesn't seem correct.

Code:

select linksurvey0_.surveyquestionId_fk as surveyqu3_1_
, linksurvey0_.linkId as linkId1_
, linksurvey0_.linkId as linkId10_0_
, linksurvey0_.surveysectionId_fk as surveyse2_10_0_
, linksurvey0_.surveyquestionId_fk as surveyqu3_10_0_ 
from linkSurveyquestions linksurvey0_ 
where linksurvey0_.surveyquestionId_fk=?

I think the where clause is wrong and should be
'where linksurvey0_.surveysectionId_fk=?'

I may have a problem in one of my xml files but I can't find it.

Thanks for your time.

Here are my xml files

Survey.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">

<hibernate-mapping package="com.dis.dqcs.data">
    <class name="Survey" table="surveys">
        <id name="surveyId" column="surveyId" type="long">
            <generator class="native"/>
        </id>
        <property name="shortDescription" column="shortDescription" type="string" length="45"/>
        <property name="longDescription" column="longDescription" type="string" length="45"/>
        <set name="linkSurveysections" cascade="save-update" lazy="true">
           <key column="surveyId_fk"/>
           <one-to-many class="LinkSurveySurveysection"/>
        </set>
        <many-to-one name="surveytemplate" column="surveytemplateId_fk" 
           class="SurveyTemplate" cascade="save-update" />
    </class>
</hibernate-mapping>

LinkSurveySurveysection.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">

<hibernate-mapping package="com.dis.dqcs.data">
    <class name="LinkSurveySurveysection" 
          table="linkSurveysSections">
        <id name="linkId" column="linkId" type="long">
            <generator class="native"/>
        </id>
       <many-to-one name="survey" column="surveyId_fk" 
                class="Survey" cascade="save-update" />
        <many-to-one name="surveysection" column="surveySectionId_fk" 
              class="SurveySection" cascade="save-update" />
    </class>
</hibernate-mapping>

SurveySection.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">

<hibernate-mapping package="com.dis.dqcs.data">
    <class name="SurveySection" table="surveySections">
        <id name="surveysectionId" column="surveySectionId" type="long">
            <generator class="native"/>
        </id>
        <property name="shortDescription" column="shortDescription" type="string"/>
        <property name="longDescription" column="longDescription" type="string"/>
       <set name="linkSurveySurveysection" cascade="save-update" 
             lazy="true">
           <key column="surveysectionId_fk"/>
           <one-to-many class="LinkSurveySurveysection"/>
        </set>       
        <set name="linkSurveysectionSurveyquestion" cascade="save-update" 
           lazy="true">
           <key column="surveyquestionId_fk"/>
           <one-to-many class="LinkSurveysectionSurveyquestion"/>
        </set>
  
    </class>
</hibernate-mapping>

LinkSurveysectionSurveyquestion.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">

<hibernate-mapping package="com.dis.dqcs.data">
    <class name="LinkSurveysectionSurveyquestion" 
          table="linkSurveyquestions">
        <id name="linkId" column="linkId" type="long">
            <generator class="native"/>
        </id>
       <many-to-one name="surveysection" column="surveysectionId_fk" 
                class="SurveySection" cascade="save-update" />
       <many-to-one name="surveyquestion" column="surveyquestionId_fk" 
              class="Surveyquestion" cascade="save-update" />
    </class>
</hibernate-mapping>

Surveyquestion.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">

<hibernate-mapping package="com.dis.dqcs.data">
    <class name="Surveyquestion" table="surveyquestions">
        <id name="questionId" column="questionId" type="long">
            <generator class="native"/>
        </id>
        <property name="shortDescription" column="shortDescription" type="string"/>
        <property name="longDescription" column="longDescription" type="string"/>
       <set name="linkSurveysectionSurveyquestion" cascade="save-update" 
             lazy="true">
           <key column="surveyquestionId_fk"/>
           <one-to-many class="LinkSurveysectionSurveyquestion"/>
        </set>
    </class>
</hibernate-mapping>

Chas · **Posted:** Tue Feb 28, 2006 12:58 pm

I ran schema-export to see how hibernate would create my data tables and I've listed this below. I guess the following should be no surprise
foreign key (surveyquestionId_fk) references surveySections (surveySectionId)
when is should be surveyquestionId_fk references surveyquestions...

now if I could just figure out why?

Code:

create table applicationRoles (roleId bigint not null auto_increment, roleName varchar(255), shortDescription varchar(255), longDescription varchar(255), primary key (roleId))
create table linkSurveyquestions (linkId bigint not null auto_increment, surveysectionId_fk bigint, surveyquestionId_fk bigint, primary key (linkId))
create table linkSurveysSections (linkId bigint not null auto_increment, surveyId_fk bigint, surveySectionId_fk bigint, primary key (linkId))
create table linkUserStore (linkId bigint not null auto_increment, userId_fk bigint, storeId_fk bigint, roleId_fk bigint, primary key (linkId))
create table storeRoles (storeRoleId bigint not null auto_increment, roleName varchar(255), shortDescription varchar(255), longDescription varchar(255), primary key (storeRoleId))
create table storeTypes (storeTypeId bigint not null auto_increment, shortDescription varchar(255), longDescription varchar(255), primary key (storeTypeId))
create table stores (storeid bigint not null auto_increment, dqcsNumber integer, idqNumber integer, street varchar(45), secondary varchar(20), city varchar(20), state varchar(20), zip varchar(20), phone varchar(20), email varchar(45), storeTypeId_fk bigint, primary key (storeid))
create table surveySections (surveySectionId bigint not null auto_increment, shortDescription varchar(255), longDescription varchar(255), primary key (surveySectionId))
create table surveyTemplates (surveyTemplateId bigint not null auto_increment, shortDescription varchar(255), longDescription varchar(255), primary key (surveyTemplateId))
create table surveyquestions (questionId bigint not null auto_increment, shortDescription varchar(255), longDescription varchar(255), primary key (questionId))
create table surveys (surveyId bigint not null auto_increment, shortDescription varchar(45), longDescription varchar(45), surveytemplateId_fk bigint, primary key (surveyId))
create table users (UserId bigint not null auto_increment, loginId varchar(45), loginPassword varchar(45), firstName varchar(45), lastName varchar(45), active integer, applicationRoleId_fk bigint, primary key (UserId))

alter table linkSurveyquestions add index FK3E1E13D9C599D374 (surveyquestionId_fk)
   , add constraint FK3E1E13D9C599D374 foreign key (surveyquestionId_fk) references surveySections (surveySectionId)
alter table linkSurveyquestions add index FK3E1E13D9A7EBC249 (surveyquestionId_fk)
   , add constraint FK3E1E13D9A7EBC249 foreign key (surveyquestionId_fk) references surveyquestions (questionId)
alter table linkSurveyquestions add index FK3E1E13D9B6FE6D49 (surveysectionId_fk)
   , add constraint FK3E1E13D9B6FE6D49 foreign key (surveysectionId_fk) references surveySections (surveySectionId)
   
alter table linkSurveysSections add index FK7F71724DB6FE6D49 (surveySectionId_fk)
   , add constraint FK7F71724DB6FE6D49 foreign key (surveySectionId_fk) references surveySections (surveySectionId)
alter table linkSurveysSections add index FK7F71724DF10E6289 (surveyId_fk)
   , add constraint FK7F71724DF10E6289 foreign key (surveyId_fk) references surveys (surveyId)

Chas · **Posted:** Tue Feb 28, 2006 2:13 pm

That was easy. I think the schema-export is a good debug tool.

jt_1000 · **Posted:** Tue Feb 28, 2006 4:28 pm

Looks like your SurveySection class has incorrect definition:
change:

Code:

<set name="linkSurveysectionSurveyquestion" cascade="save-update" 
           lazy="true"> 
           <key column="surveyquestionId_fk"/>           <one-to-many class="LinkSurveysectionSurveyquestion"/> 
        </set> 

Code:

<set name="linkSurveysectionSurveyquestion" cascade="save-update" 
           lazy="true"> 
          <key column="surveysectionId_fk"/>            <one-to-many class="LinkSurveysectionSurveyquestion"/> 
        </set> 