Hibernate Community • View topic - Many to Many with "join" table ... how?

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Many to Many with "join" table ... how?

Page 1 of 1

[ 8 posts ]

Previous topic | Next topic

Author

Message

MikeC

Post subject: Many to Many with "join" table ... how?

Posted: Sat Mar 20, 2004 2:55 pm

Newbie

Joined: Thu Mar 18, 2004 12:29 pm
Posts: 9

I'm trying to model a many to many relationship using a "join" table. I've taken a glance through the docs, and I'm a bit lost right now.

How does hibernate know what the join table is? Section 6.3 of the docs doesn't really say, or I'm not understanding it (likely).

Using the prototypical "book"/"author" relationship (1 book authored by many, 1 author writes many books), what would the mappings look like for this? In the database I'd having something like

book:
id int
title varchar
...

author:
id int
name varchar
...

book_author_join
book_id int (FK to book:id)
author_id int (FK to author:id)
...

Pointers to the docs where this is explained welcome.

Thanks

Top

anthony

Post subject:

Posted: Sat Mar 20, 2004 3:11 pm

Hibernate Team

Joined: Thu Dec 18, 2003 9:55 am
Posts: 1977
Location: France

for example, in author mapping file

Code:

<bag name="books" table="book_author_join " cascade="all">
    <key column="id"/>
    <many-to-many column="author_id" class="book"/>
</bag>

[/code]

Top

alistair

Post subject:

Posted: Mon Mar 22, 2004 12:31 am

Beginner

Joined: Wed Mar 03, 2004 6:02 am
Posts: 46
Location: Kuala Lumpur, Malaysia

It is not my intention to hijack MikeC's question but I would like a 2nd consecutive thread here since it might prove useful to MikeC later on once he gets his mapping files sorted out.

I'm doing the same thing as MikeC. My mapping is fine. The issue now is that some HQLs are not executing properly when joins are needed.

e.g.

Code:

1) select u.email from User u ---> executes fine.

2) from User --> Throws error: Too many tables. MySQL can only use 31 tables in 
a join

3) from Role --> same error as in 2

4) select r.roleName from Role r --> executes fine.

I'm using non-lazy associations in ALL my relationships. Do you think this is causing my problem ?

Regards,

alistair

Top

alistair

Post subject:

Posted: Mon Mar 22, 2004 12:42 am

Beginner

Joined: Wed Mar 03, 2004 6:02 am
Posts: 46
Location: Kuala Lumpur, Malaysia

An update on my problem:

I went ahead and set lazy="true" for both mapping files for User.hbm.xml and Role.hbm.xml. So now the following queries are working fine:

Code:

from Role --> works!

from User --> works!

<set
       name="roles"
       table="user_role"
       lazy="true"
       inverse="true"
       cascade="save-update"
       sort="unsorted"
>

Question: Since I have complex relationships between all my entities (with polymorphic associations etc..), should I just set all relationships to lazy ?

Thanks

Alistair

Top

gavin

Post subject:

Posted: Mon Mar 22, 2004 12:52 am

Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia

Quote:

Since I have complex relationships between all my entities (with polymorphic associations etc..), should I just set all relationships to lazy ?

It is normal practice in Hibernate to map all collection-valued associations lazy="true" and the same goes even for most single-valued associations.

Top

alistair

Post subject:

Posted: Mon Mar 22, 2004 2:54 am

Beginner

Joined: Wed Mar 03, 2004 6:02 am
Posts: 46
Location: Kuala Lumpur, Malaysia

I've set all possible associations (mainly collections types with Set) to lazy=true. However:

Code:

select elements(u.roles) from User u ---> works fine

select elements(r.user) from Role r --> gave me the [i]Too many tables. MySQL can only use 31 tables in a join[/i] SQLException

Will my inheritance hierachy cause a problem? I'm using joined subclasses:

Code:

                                                  +---------- Junior --- leaf nodes ...
                                                  |
                +-------- Executive ---+
                |                                 |
                |                                 +---------- Senior -- lead nodes ...
                |
User <----+-------- Administrative --- etc.. (leaf nodes)

Thanks a lot.

Regards

Alistair

Top

gavin

Post subject:

Posted: Mon Mar 22, 2004 3:04 am

Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia

We do not recommend deep joined-subclass hierarchies, especially not with MySQL.

Top

alistair

Post subject:

Posted: Mon Mar 22, 2004 3:15 am

Beginner

Joined: Wed Mar 03, 2004 6:02 am
Posts: 46
Location: Kuala Lumpur, Malaysia

Gavin,

Actually as the diagram suggests, my hierachy is only 2 levels deep starting with the "User" superclass. Nevertheless, I'm going to try the tpc strategy to see if my problem goes away.
Thanks.

Regards,

alistair

Top

Page 1 of 1

[ 8 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum