Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.databases > #135 > unrolled thread

hibernate: mapping help

Back to article view | Back to comp.lang.java.databases

Contents

  hibernate: mapping help "Matteo" <matteo@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
    Re: hibernate: mapping he "Lew" <lew@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
      Re: hibernate: mapping he "Matteo" <matteo@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
        Re: hibernate: mapping he "Lew" <lew@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
          Re: hibernate: mapping he "Matteo" <matteo@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
            Re: hibernate: mapping he "Lew" <lew@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000

#135 — hibernate: mapping help

  To: comp.lang.java.databases
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

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [next] | [standalone]

#136 — Re: hibernate: mapping he

  To: comp.lang.java.databases
Matteo wrote:
> 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-entityname> 


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-bidirectional-join>

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

-- 
Lew

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#137 — Re: hibernate: mapping he

  To: comp.lang.java.databases
Lew wrote:
> Matteo wrote:
>> 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.

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.
> <http://www.hibernate.org/hib_docs/v3/reference/en/html/mapping.html#mapping-entityname> 
> 
> 
> 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-bidirectional-join> 
> 
> 
> I found pretty much your exact problem mentioned in 2005:
> <http://objectmix.com/jdbc-java/41501-hibernate-persons-friends-newbie-mapping-question.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

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#138 — Re: hibernate: mapping he

  To: comp.lang.java.databases
Matteo wrote:
> 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.

-- 
Lew

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#139 — Re: hibernate: mapping he

  To: comp.lang.java.databases
Lew wrote:
  > 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

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#140 — Re: hibernate: mapping he

  To: comp.lang.java.databases
Matteo wrote:
> 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.

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.

-- 
Lew

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.java.databases

csiph-web