One to One and Many Relationships

howudodat · **Joined:** Wed Feb 17, 2010 12:46 pm **Posts:** 6

I have some questions on relationship mapping. here is the db config:

Code:

CREATE TABLE tContactInfoType
 (
        id                      int(8) auto_increment not null primary key,
        InfoType                varchar (20)
);
INSERT INTO tContactInfoType VALUES 
     (1,'Home'),
     (2,'Work'),
     (3,'Fax'),
     (4,'Mobile'),
     (5,'Alt1'),
     (6,'Alt2'),
     (7,'Main'),
     (8,'E-mail');

CREATE TABLE tContactType
 (
        id                      int(8) auto_increment not null primary key,
        ContactType             varchar (20)
);
INSERT INTO tContactType VALUES 
     (1,'Billing'),
     (2,'Office');


CREATE TABLE Contacts
 (
        id                      int(8) auto_increment not null primary key,
        xid                     int (8), 
        Last                    varchar (100), 
        First                   varchar (100), 
        Middle                  varchar (100), 
        Title                   varchar (100),
        Type                    int(8)
);
CREATE TABLE ContactInfo
 (
        id                      int(8) auto_increment not null primary key, 
        xid                     int (8), 
        ContactInfo             varchar (100), 
        Type                    int (8),
        Private                 Boolean
);

Here is a sample select:

Code:

mysql> select * from Contacts, ContactInfo where ContactInfo.xid = Contacts.id order by ContactInfo.xid, ContactInfo.type;
+----+------+---------+---------+--------+-------+------+----+------+----------------------------------------+------+---------+
| id | xid  | Last    | First   | Middle | Title | Type | id | xid  | ContactInfo                            | Type | Private |
+----+------+---------+---------+--------+-------+------+----+------+----------------------------------------+------+---------+
|  1 |   20 | Fooo    | Bar     | W      |       | 1    | 11 |    1 | 111-222-3333                           |    2 |   false |
|  1 |   20 | Fooo    | Bar     | W      |       | 1    |  1 |    1 | foobar@gmail.com                       |    8 |   false |
+----+------+---------+---------+--------+-------+------+----+------+----------------------------------------+------+---------+

one to one relationship: Contacts.Type - tContactType.id
one to one relationship: ContactInfo.Type - tContactInfoType.id
one to many relationship: Contacts.id -> ContactInfo.xid

My vision of how the data looks is:

Code:

Carlson, Peter - Office Contact
   111-222-3333, Office
   222-333-4444, Fax
   peter@foo.bar, email
Smith, John - Billing Contact
   333-444-5555, Office
   444-555-6666, Fax
   js@foo.bar, email

I can't figure out how to make the class object for this or the hbm file. I dont need someone to write my code :) but a couple of lines of it would be nice

Here is the hbm file for Contacts:

Code:

    <class name="DBO_Contacts" table="Contacts">
        <id name="id" column="id">
            <generator class="native"/>
        </id>
        <property name="xid"/>
        <property name="Last"/>
        <property name="First"/>
        <property name="Middle"/>
        <property name="Title"/>
        
        <one-to-one name="Type" class="DBO_tContactType" />
        
    </class>

However I get an error: "Duplicate property mapping of Type found in dbObjects.DBO_ContactInfo"
What do I do for the one-one relationships?

Also not sure how to define the one to many yet. I'm thinking something like:

Code:

<set name="ContactInfo" table="CONTACTINFO">
   <key column="id"/>
        <many-to-many column="XID" class="ContactInfo"/>
</set>

Peter

nordborg · **Posted:** Wed Feb 17, 2010 7:09 pm

Quote:

one to one relationship: Contacts.Type - tContactType.id
one to one relationship: ContactInfo.Type - tContactInfoType.id

I am pretty sure this is not true. The example you posted clearly shows that the two contacts both have relations to the same tContactInfoType:s. Both Peter and John have an email and fax+office numbers. I also assume that you might want to store more than one "Billing contact" in the future. I guess you should use many-to-one relations instead.

Code:

<many-to-one name="Type" class="DBO_tContactType" column="Type" />

Quote:

However I get an error: "Duplicate property mapping of Type found in dbObjects.DBO_ContactInfo"

You didn't post the mapping for this class.

I haven't checked the rest of it. I guess there may be other issues. Fix the <many-to-one>:s first and don't try to fix everything in one go. Take one step at a time. Read the at least the first chapter here: http://docs.jboss.org/hibernate/stable/ ... e/en/html/

howudodat · **Joined:** Wed Feb 17, 2010 12:46 pm **Posts:** 6

Agreed to take one issue at a time.
1. The Dupliate Property Mapping was a dumb typo and fixed.
2. The one to one relationships I believe are working ( they aren't relationships as much as they are lookups )

Code:

111-222-3333, Office
222-333-4444, Fax

In this situation the Type (Office, Fax) can only be one element in the tContactInfoType Table. It is really a way to have a lookup of data. tContactInfoType enumerates and provides a text representation of the ContactInfo.Type field.

3. So that leaves the one to manys. Each contact can have many ContactInfo records
The relationship is ContactInfo.xid = Contact.id. I can't figure out what goes in the key column field and in the one-to-many column field...which is which? And is it one-to-many or many-to-one?

Code:

    <class name="DBO_Contacts" table="Contacts">
        <id name="id" column="id">
            <generator class="native"/>
        </id>
        <property name="Last"/>
        <property name="First"/>
        <property name="Middle"/>
        <property name="Title"/>

      <one-to-one name="Type" class="DBO_tContactType" fetch="join"/>
      <set name="ContactInfo" table="ContactInfo">
         <key column="PARENT_OR_CHILD???"/>
         <one-to-many column="PARENT_OR_CHILD???"
      </set>
    </class>

kingmaker · **Joined:** Thu Feb 18, 2010 1:57 am **Posts:** 2

Thanks for sharing this information...

howudodat · **Joined:** Wed Feb 17, 2010 12:46 pm **Posts:** 6

Ok, after some hair pulling and trial and error here is what I have for mapping that works:

Code:

<hibernate-mapping package="dbObjects">

    <class name="DBO_Contacts" table="Contacts">
        <id name="id" column="id">
            <generator class="native"/>
        </id>
        <property name="xid"/>
        <property name="Last"/>
        <property name="First"/>
        <property name="Middle"/>
        <property name="Title"/>

      <one-to-one name="Type" class="DBO_tContactType" fetch="join"/>
      <map name="ContactInfo" lazy="false" order-by="Type asc">
         <key column="xid"/>
         <map-key column="id" type="long"/>
         <one-to-many class="DBO_ContactInfo"/>
      </map>
    </class>

Peter