hibernate: mapping help

Thread view:

Matteo - 22 May 2008 01:34 GMT

Hello all,
I'm fairly new to hibernate, though I'm not new to ORM concepts.
I read the official h8 version 3 tutorial, as well as other articles and
examples out there.

Now the problem is that I can't figure out how the best way to map the
following tables:

table USERS (
`userID` int(11) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`lastName` varchar(255) NOT NULL,
`nickname` varchar(20) NOT NULL,
PRIMARY KEY (`userID`),
KEY `nickname` (`nickname`) ---> indice
)

table FRIENDS (
`userID` int(11) unsigned NOT NULL default '0',
`friendID` int(11) unsigned NOT NULL default '0',
PRIMARY KEY (`userID`,`friendID`),
KEY `friendID` (`friendID`),
CONSTRAINT `friends_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `users`
(`userID`),
CONSTRAINT `friends_ibfk_2` FOREIGN KEY (`friendID`) REFERENCES `users`
(`userID`)
)

table MESSAGES (
`messageID` int(11) unsigned NOT NULL auto_increment,
`from` int(11) unsigned NOT NULL,
`to` int(11) unsigned NOT NULL,
`message` varchar(4000) default NULL,
PRIMARY KEY (`messageID`),
KEY `from` (`from`),
KEY `to` (`to`),
CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`from`) REFERENCES `users`
(`userID`),
CONSTRAINT `messages_ibfk_2` FOREIGN KEY (`to`) REFERENCES `users`
(`userID`)
)

I'm stuck with my implementation of the "friendship" relationship, as I
decided to define it with a directed-graph, thus user.A and user.B are
"friends" if and only if
( FRIENDS.userID = user.A ; FRIENDS.friendID = user.B ) AND (
FRIENDS.userID = user.B ; FRIENDS.friendID = user.A )
Hence, the composite-id (userID, friendID) of FRIENDS table.

As for MESSAGES table, both FROM and TO fields referentiate USERS.userID
(and I'm stuck with that too )

Thanks in advance !
Matteo

Reply to this Message

Lew - 22 May 2008 03:43 GMT

> I'm stuck with my implementation of the "friendship" relationship, as I
> decided to define it with a directed-graph, thus user.A and user.B are
> "friends" if and only if
> ( FRIENDS.userID = user.A ; FRIENDS.friendID = user.B ) AND (
> FRIENDS.userID = user.B ; FRIENDS.friendID = user.A )
> Hence, the composite-id (userID, friendID) of FRIENDS table.

The "Hence" clause doesn't follow - that would be the primary key even if you
define a "friend" as any row in the FRIENDS table, even if the converse row is
absent. There is no connection between the "AND" of the rule you stated and
that primary key. In fact, nothing in the data definition you showed enforces
that "A" must appear in two rows of the table, once as the "userID" and once
as the "friendID". I wouldn't bother enforcing that, either.

> As for MESSAGES table, both FROM and TO fields referentiate USERS.userID
> (and I'm stuck with that too )

There's nothing to be "stuck with" there - it's a perfectly reasonable data
design.

Is this a MySQL database? You didn't say.

Couldn't you just map the tables in the very straightforward way? The
relation between USERS and USERS is many-to-many. Seems like you could fake
it as a one-to-many, though.

I don't know Hibernate well, but it seems like using <entity-name> might help.
<http://www.hibernate.org/hib_docs/v3/reference/en/html/mapping.html#mapping-enti
tyname>

The use of a join table looks like you need to read
<http://www.hibernate.org/hib_docs/v3/reference/en/html/associations.html#assoc-b
idirectional-join>

I found pretty much your exact problem mentioned in 2005:
<http://objectmix.com/jdbc-java/41501-hibernate-persons-friends-newbie-mapping-qu
estion.html>

Signature

Lew

Reply to this Message

Matteo - 22 May 2008 09:31 GMT

>> I'm stuck with my implementation of the "friendship" relationship, as
>> I decided to define it with a directed-graph, thus user.A and user.B
[quoted text clipped - 10 lines]
> table, once as the "userID" and once as the "friendID". I wouldn't
> bother enforcing that, either.

Well, in fact I was referring to the business logic and to the Java
class that enforces it. The underlying database knows nothing about this
rule, you are right.

> Is this a MySQL database? You didn't say.

Yes sorry, it's a MySQL db

> Couldn't you just map the tables in the very straightforward way? The
> relation between USERS and USERS is many-to-many. Seems like you could
> fake it as a one-to-many, though.

It was pretty late -my local time- when I posted this message. This
morning everything sounds clearer and straightforward.
I will fake the 1-to-many relation, as the data navigation always starts
from a given user (any one user has 0 or more friends in his/her Set)

> I don't know Hibernate well, but it seems like using <entity-name> might
> help.
[quoted text clipped - 5 lines]
> I found pretty much your exact problem mentioned in 2005:
> <http://objectmix.com/jdbc-java/41501-hibernate-persons-friends-newbie-mapping-qu
estion.html>

That's exactly what I need.
Thanks a lot!

[btw, sorry for cross-posting. I found java.database right after having
posted to java.help. I thought database was more appropriate]

Matteo

Reply to this Message

Lew - 23 May 2008 01:06 GMT

> That's exactly what I need.
> Thanks a lot!

Good, glad it helped.

> [btw, sorry for cross-posting. I found java.database right after having
> posted to java.help. I thought database was more appropriate]

You didn't cross-post, you multi-posted. Cross-posting has all the newsgroups
in the address lines at once, and all replies also go to all the groups
(unless you set followup, which you should). Multi-posting is independent
posting of substantially the same message in different groups, much worse.

I would be surprised if nearly all the readers of clj.databases didn't also
read the other Java newsgroups as well, at least ...help and ...programmer.

Anyway, it is good that you found your answer. Since I don't know Hibernate
well at all, could you explain to the group what worked and what exactly you
had to do for it? I would learn a lot from that.

Signature

Lew

Reply to this Message

Matteo - 24 May 2008 10:47 GMT

> Anyway, it is good that you found your answer. Since I don't know

> Hibernate well at all, could you explain to the group what worked and
> what exactly you had to do for it? I would learn a lot from that.

Granted I'm an absolute beginner with Hibernate, so my solution is
likely not to be perfect -and I'd like to hear a more expert advice on
that, here's what I come up with:

About the composite key of FRIENDS table: remember I had doubts (not
about its correctness, as it is perfectly legal and common to have
composite keys in DB tables). if you read through the Hibernate manual,
they keep stressing composite keys as being the cause of all evil. From
a certain point of view, they are right.

A composite key can be translated to a surrogate key quite easily:
(fieldA, fieldB) primary key NOT NULL
is equal to
surrogateKey PRIMARY KEY NOT NULL
(fieldA, fieldB) UNIQUE NOT NULL

This will save you from a lot of headhaches with Hibernate.

Revised version of Friends table (I changed the name to Friendship)
table FRIENDSHIP (
`id` int(11) unsigned NOT NULL PRIMARY KEY,
`userID` int(11) unsigned NOT NULL,
`friendID` int(11) unsigned NOT NULL,
UNIQUE KEY (`userID`,`friendID`),
CONSTRAINT `friendship_ibfk_1` FOREIGN KEY (`userID`) REFERENCES
`users`
(`userID`),
CONSTRAINT `friendship_ibfk_2` FOREIGN KEY (`friendID`) REFERENCES
`users`
(`userID`)
)

hibernate mapping:
----- Friendship.hbm.xml ---------
<class name="Friendship" table="friendship">
<id column="id" name="id">
<generator class="native"/>
</id>
<properties ...
<many-to-one class="User" column="userID" name="friendA"
not-null="true"/>
<many-to-one class="User" column="friendID" name="friendB"
not-null="true"/>
</class>

----- User.hbm.xml ---------
<class name="User" table="Users">
<id column="userID" name="id">
<generator class="native"/>
</id>
<properties ...

<set name="friends" table="friends" inverse="true" cascade="all">
<key column="friendID"/>
<one-to-many class="Friendship"/>
</set>

<set name="messages" table="messages" cascade="all" inverse="true">
<key column="receiver"/>
<one-to-many class="Message"/>
</set>
</class>

As for MESSAGES table:
<class name="Message" table="messages">
<id column="messageID" name="id">
<generator class="native"/>
</id>
<many-to-one class="User" column="sender" name="sender"
not-null="true"/>
<many-to-one class="User" column="receiver" name="receiver"
not-null="true"/>
</class>

Again, I think this is far from being perfect, but it works for me and
for now I'm going to be happy with this solution, unless someone here
would give me a better one (eg. I can think of reverting the Friendship
table to FRIENDS, and having a java class Friend subclass User class...A
Friend is a User itself in the end...)

comments/suggestions?
thanks

Matteo

Reply to this Message

Lew - 24 May 2008 12:05 GMT

> A composite key can be translated to a surrogate key quite easily:
> (fieldA, fieldB) primary key NOT NULL
[quoted text clipped - 3 lines]
>
> This will save you from a lot of headhaches with Hibernate.

Unfortunately this causes headaches in the data model. Entity tables can
sport such surrogate keys, but linking tables, where "fieldA" and "fieldB" are
both keys into other tables, get pretty fubared when you throw a superfluous
surrogate key onto the them. The extra key gets in the way of JOINs.

Signature

Lew

Reply to this Message

hibernate: mapping help

Free Magazines