Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

I am facing problems with many-to-many relationship in tables where the relation table has an attribute other than the composite primary key. I am using Spring 1.2 ORM package for hibernate. Things are fine when the relation table contains only the composite key. The hibernate documentation also talks about the same; I could not gather any information to validate whether my mapping is correct or not.

The details are:

Hibernate version: 3.0

Mapping documents:
Customer.hbm
<hibernate-mapping >
<class name="model.Customer" table="CUSTOMER">
<id name="Id" type="integer" column="CUST_ID">
<generator class="increment"/>
</id>

<property name="FirstName" column="FIRST_NAME" type="string" not-null="true" length="50"/>
...
<property name="SecurityAnswer" column="SECURITY_ANSWER" type="string" not-null="true" length="100"/>
<set name="AdditionalRequirements" table="CUST_ADDITIONAL_REQUIREMENTS" cascade="all">
<key column="CUST_ID"/>
<many-to-many column="QUESTION_ID" class="model.AdditionalRequirements"/>
</set>
</class>
</hibernate-mapping>

AdditionalRequirements.hbm
<hibernate-mapping >
<class name="model.AdditionalRequirements" table="ADDITIONAL_REQUIREMENTS" >
<id name="Id" type="integer" column="QUESTION_ID">
<generator class="increment"/>
</id>

...
<set name="CUSTOMERs" table="CUST_ADDITIONAL_REQUIREMENTS" cascade="all">
<key column="QUESTION_ID"/>
<many-to-many column="CUST_ID" class="model.Customer"/>
</set>
</class>
</hibernate-mapping>

CustAdditionalRequirements.hbm
<hibernate-mapping>
<class name="model.CustAdditionalRequirements" table="CUST_ADDITIONAL_REQUIREMENTS">
<composite-id name="Id" class="model.CustAdditionalRequirementsPK">
<key-many-to-one name="Question" class="model.AdditionalRequirements"
column="QUESTION_ID" />
<key-many-to-one name="Cust" class="model.Customer" column="CUST_ID"/>
</composite-id>

<property name="Answer" column="ANSWER" type="string" not-null="false" length="1000"/>
</class>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():
public class CustomerDAOImpl extends HibernateDaoSupport implements CustomerDAO {

public java.lang.Integer save(model.Customer customer)
throws org.hibernate.HibernateException {
System.out.println("inside save");
return (java.lang.Integer)this.getHibernateTemplate().save((Object) customer);
}



Full stack trace of any exception that occurs:
org.springframework.orm.hibernate3.HibernateSystemException: IllegalArgumentException occurred calling getter of model.AdditionalRequirements.Id; nested exception is org.hibernate.PropertyAccessException: IllegalArgumentException occurred calling getter of model.AdditionalRequirements.Id
org.hibernate.PropertyAccessException: IllegalArgumentException occurred calling getter of model.AdditionalRequirements.Id


Name and version of the database you are using:
MySQL 4.1

The generated SQL (show_sql=true):
No SQL shown on console though show_sql=true

Debug level Hibernate log excerpt:

Other relevant information:
The table DDL are:
CREATE TABLE `additional_requirements` ( `QUESTION_ID` decimal(4,0) NOT NULL default '0', `QUESTION_DESC` text NOT NULL, `QUESTION_STATUS` char(1) default NULL, PRIMARY KEY (`QUESTION_ID`) )
CREATE TABLE `customer` ( `CUST_ID` decimal(12,0) NOT NULL default '0', `FIRST_NAME` varchar(50) NOT NULL default '', `LAST_NAME` varchar(50) NOT NULL default '', ...
PRIMARY KEY (`CUST_ID`) )

CREATE TABLE `cust_additional_requirements` ( `cust_id` decimal(12,0) NOT NULL default '0', `question_id` decimal(4,0) NOT NULL default '0', `answer` text, PRIMARY KEY (`cust_id`,`question_id`), KEY `CUST_ADDITIONAL_REQ_FK1` (`question_id`), KEY `CUST_ADDITIONAL_REQ_FK2` (`cust_id`), CONSTRAINT `CUST_ADDITIONAL_REQ_FK2` FOREIGN KEY (`cust_id`) REFERENCES `customer` (`CUST_ID`), CONSTRAINT `CUST_ADDITIONAL_REQ_FK1` FOREIGN KEY (`question_id`) REFERENCES `additional_requirements` (`QUESTION_ID`) )


POJO used
3 POJOs are being used i.e. Customer.java, CustAdditionalRequirements.java, AdditionalRequirements.java wherein the relationships are as follows:
Customer has private java.util.Set additionalRequirements; variable
AdditionalRequirements has: // collections private java.util.Set cUSTOMERs;
CustAdditionalRequirements has
// primary key
private model.CustAdditionalRequirementsPK id;
// fields
private java.lang.String answer;

where CustAdditionalRequirementsPK has
private model.AdditionalRequirements question;
private model.Customer cust;

The test program is:
/*
* Test method
*/
public final void testSave() {
// TODO Auto-generated method stub
try {
FileSystemXmlApplicationContext context = new FileSystemXmlApplicationContext("E:\\springapp-servlet.xml");
BeanFactory bf = context.getBeanFactory();
CustomerDAO custDao = (CustomerDAO) bf.getBean("iCustomerDao");

Set customers = new HashSet();

Customer cust = new Customer(null, null, "abc", "XYZ","JKS",...,"answer");

Integer custNbr = custDao.save(cust);

AdditionalRequirements additionalRequirements = new AdditionalRequirements();
additionalRequirements.setId(new Integer(1));
additionalRequirements.setQuestionDesc("Question1");
additionalRequirements.setQuestionStatus("A");

CustAdditionalRequirementsPK custAdditionalRequirementsPK = new CustAdditionalRequirementsPK();
custAdditionalRequirementsPK.setQuestionId(new Integer(1));
custAdditionalRequirementsPK.setCustId(custNbr);

CustAdditionalRequirements custAdditionalRequirements = new CustAdditionalRequirements();
custAdditionalRequirements.setId(custAdditionalRequirementsPK);
custAdditionalRequirements.setAnswer("Yes");

cust.addToAdditionalRequirements(additionalRequirements);
custDao.saveOrUpdate(cust);
}catch(Exception e) {
e.printStackTrace();
}
}

Although we are using annotations in version 3 I believe the problem you are having is due to defining the relationship from the role (join) table. In its hbm file you have declared the two many-to-one relationships. Take them out entirely then instead add a one-to-many relationship on the two main classes and ofcourse a Collection type plus get/set to store the reference in from each main class. And hey presto that should be fine. We have done that very same thing since our role tables all have information about the role itself to collect and it works fine.

Thanks shaunybee for the reply

I changed the relation mapping file i.e. CustAdditionalReq.hbm to remove the many-to-one relationship in composite key to look like following:

<composite-id name="Id" class="qc.qwbs.betawebsite.tester.model.CustAdditionalRequirementsPK">
<key-property name="Question" type="integer"
column="QUESTION_ID"/>
<key-property name="Cust" type="integer"
column="CUST_ID"/>
</composite-id>

And the individual hbm files to have one-to-many mappings instead of many-to-many i.e.
<set name="AdditionalRequirements"
table="CUST_ADDITIONAL_REQUIREMENTS" cascade="all">
<key column="CUST_ID"/>
<one-to-many class="qc.qwbs.betawebsite.tester.model.AdditionalRequirements"/>
</set>

And also changed the relation POJO to contain integers for cust_id and question instead of actual objects.

Still gives me the same problem.. May be I have not understood your reply.. Would u plz help me further?

Whoops - sorry my explanation was a little vague as I couldn't remember the names of your classes. No what you need to do is:
1)Have a m:n relationship in Customer to AdditionalRequirements
2) Have a n:m relationship in AdditionalRequirements to Customer
3) Have a 1:m relationship in Customer to CustAdditionalRequirements
4) Have a 1:m relationship in AdditionalRequirements to CustAdditionalRequirements

Obviously the respective class proerpties and set/get methods need to be added to the classes too i.e. in Customer have a List for the many AdditionalRequirements and vice versa in AdditionalRequirements. Then in both Customer and Additionalrequirements have a List of CustAdditonalRequirements.

The PK class is fine I think, while the CustAdditionalRequirements class needs to define NO relationships just its members.

Done ..:)

Thanks shaunybee, it worked :-)

Hello,

we have a similar problem. We have "Experiments" and "Samples". Experiment can have 1 to Many Samples - and a samle can be used in 1 to Many Experiments. --> ManyToMany relationship. Additionaly we want to add to the relations-table an attribute. So we have defined 3 classes:

- experiment.class
- sample.class
- exp_sample_join.class

exp_sample_join.class defines nothing than an @id column and the additional attribute (role).
In experiment.class and sample.class we are defining the ManyToMany-Relations and define as @JOINTABLE the exp_sample_join - Table.

Now Hibernate fails on startup with failure on creating exp_sample_join - table (propably because it already exists due to defining the ManyToMany -Relation).



But how can we get the additional attribute (role) to the join-table ? And how can we prevent to have to explicit declare an @Id Column in exp_sample_join.class (there are already the joined PK from the ManyToMany-Rel-Definitions) ?