CMR Many to many relationships with 3 beans

[jocky4]

Hello,

I have many questions on many to many relationships.
I have followed the instructions found in the topic http://www.jboss.org/index.html?module=bb&op=viewtopic&t=37062.

But, there's no details on the java code.
I tried to implement my N-N relationships following the deployment file, but I have this error :

java.sql.SQLException: Syntax error or access violation: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM bookuser WHERE (book_id=437)' at line 1

Here are some details about my application :

It's a simple application to manage book sharing.
So a user has a lot of books and a book can belong to several users.
The table (book_user) which assumes the N-N relation has a third attribute : the buy date.

I can give you my deployment file and my java code if necessary.

My question is : What is the way to implement a N-N relationships with attribute in CMP 2.0.

Thank you for helping.

Jo

[raist_majere]

If you want to use CMP for your app, then you must normalize your design. That means that if you have to use a relationship with attributes (as in your case) or relationships between more than two entities at the same time, you must define a third entity (or class) between the two related entities and put the relation attributes there, then make a relation to the related entities...
I think I'll be clearer with an example. In your case, you should make a third entity, named Sale (for example) which will have a M:N relation to Book, and another M:N relation to User, as well as the attributes of the relation, in your case buyDate. Of course, if you use this approach, you must delete the relation you have now between Books and Users. The only problem you may encounter using this approach is that the primary key field cannot be composed of CMR fields, so the best choice you can make is to have an autogenerated PK field.
Hope this helps.

[jocky4]

Thank you for response.

I'm happy because I already implements my bean correctly.
In my case, user can buy a book only one time. So the relation between User and Sale is a N:1 relantionships.

I've done a mistake in the PK of the Sale bean. I created a composed PK :

public class BookUserPK {

 public Book book;
 public User user;

 public BookUserPK() {}

 public BookUserPK(Book pBook, User pUser) {
 this.book = pBook;
 this.user = pUser;
 }

 public boolean equals(Object pObj) {
 if (! (pObj instanceof BookUserPK))
 return false;

 BookUserPK key = (BookUserPK) pObj;

 return book.equals(key.book) && user.equals(key.user);
 }

 public int hashCode() {
 return 65521 * book.hashCode() + user.hashCode();
 }
}

It seem to be uncorrect.
How can I do ?
My tabel bookuser as three columns :
book_id
user_id
buy_date
SQL PK is book_id + user_id

Should I add a fourth column ?
How can I do an autogenerated PK field ?

Thank you

Jo

[raist_majere]

After reading your last post I realized I made a mistake :P: the Sale bean has to have 1:N relationships instead of M:N with User and Book beans.
About generating PK fields, you can take a look at this page:
http://docs.jboss.org/jbossas/admindevel326/html/ch11.chapter.html#ch11.pkgen.sect