Mapping a join table

Laurens · **Joined:** Wed Apr 05, 2006 3:19 pm **Posts:** 43

How would I have to map this?

Code:

User
- userID
- userName

Role
- roleID
- roleName (FK: status.statusName)
- userName (FK: users.userName)

Status
- statusID
- statusName

Would the Role object look like this?

Code:

........
Long roleID
Status status
User user
.........

or like this?

Code:

..........
Long roleID
List statusses;
List users;
...........

I'm not really sure about all this... The situation is like this: a user has a role (admin/user/....). The possible roles are described in the "status" table. One role can be assigned to a user by adding the user with a status in the "roles" table. A possible setup could be this:

Code:

Users: 
1 - User1
2 - AnotherUser

Roles: 
1 - Admin - User1
2 - User - AnotherUser

Status: 
1 - Admin
2 - User

How do you suggest the mapping? I'm getting all confused about the one-to-many many-to-one and lists/sets...

john_yeager · **Posted:** Sun Apr 09, 2006 6:25 pm

I think that before you work on mapping your tables to classes, you need to fix up your tables.

First, it is my understanding that a foreign key must reference a primary key column. So your Role.roleName and Role.userName columns should be changed to the corresponding ID values.

But I would further suggest that you re-read what you have written where you say the "roles are described in the 'status table.'" To me this suggests that the Status table is misnamed. How about the following database design instead?

User
- userID (pk)
- userName

UserRole
- userID (pk) (fk: User.userID)
- roleID (pk) (fk: Role.roleID)

Role
- roleID (pk)
- roleName

In this case, there is a many-to-many association between Users and Roles. That is, a User may have multiple Roles, and multiple Users may have the same Role. The User table is mapped to the User class, and the Role table is mapped to the Role class. The UserRole table is not mapped to any class; It is a link table that supports the many-to-many association. Both the userID and roleID columns make up the primary key of the UserRole table.
Here is what the classes would look like for the many-to-many relationship:

User Class:
- id
- name
- Set (or List) of Roles

Role Class:
- id
- name
- Set (or List) of Users

Of course, you do not have to map the association in both directions. That is, although you may want to know what Roles a given User has, you may not care to have a list of Users for a given Role. In that case, you can leave the set of Users off of the Role class.

(Note: You stated that "a user has role," but I am assuming that a User can actually have multiple roles. If not, there should be a many-to-one relationship between Users and Roles, not a many-to-many.)

I hope this helps,
John

Laurens · **Joined:** Wed Apr 05, 2006 3:19 pm **Posts:** 43

Hi, thank you for your helpful reply. I have already thought about every recommendation you have given, but the problem is that I use tomcat authentication, which requires a setup like this. (see http://www.onjava.com/pub/a/onjava/2002/06/12/form.html for an example). So I'm more or less stuck with this type of database setup.

pepelnm · **Posted:** Mon Apr 10, 2006 6:16 am

Wich DB are you using?

I had the same problem with MySQL 4 and Tomcat.

But since release 5, MySQL supports views. So now I am using the same approach suggested by john_yeager. Tomcat sees the same way a view or a table so now I have a clean implementation.

Laurens · **Joined:** Wed Apr 05, 2006 3:19 pm **Posts:** 43

woops, double post.... see my reply beneath this one.

Laurens · **Joined:** Wed Apr 05, 2006 3:19 pm **Posts:** 43

I'm using mysql 5. How exactly did you set the views up? This could certainly be a solution. Could you also post an example of the corresponding mapping?

pepelnm · **Posted:** Mon Apr 10, 2006 6:53 am

This is from my Tomcat's context.xml

Code:

   <Realm className="org.apache.catalina.realm.JDBCRealm"
      connectionName="**********"
      connectionPassword="**********"
      connectionURL="jdbc:mysql://localhost/xxx"
      driverName="com.mysql.jdbc.Driver"
      
      userTable="usuario"
      userCredCol="password"

      userNameCol="user"
      
      userRoleTable="v_usuario_rol"
      roleNameCol="rol"
   />

This my 'usuario' table:

Code:

CREATE TABLE  `xxx`.`usuario` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user` char(12) character set latin1 NOT NULL default '',
  `password` char(12) character set latin1 NOT NULL default '',
  `tipo` char(1) character set latin1 NOT NULL default '',
  `idEmpresa` int(10) unsigned NOT NULL default '0',
  `nombre` char(40) character set latin1 NOT NULL default '',
  `admin` tinyint(1) NOT NULL default '0',
  `cif` char(9) character set latin1 NOT NULL default '',
  `email` varchar(45) character set latin1 NOT NULL default '',
  `telefono` varchar(45) character set latin1 NOT NULL default '',
  `contacto` varchar(45) character set latin1 NOT NULL default '',
  `fax` varchar(45) character set latin1 NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `user` (`user`),
  KEY `FK_usuario_empresa` (`idEmpresa`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

This is my view definition

Code:

CREATE VIEW `v_usuario_rol` AS
select `u`.`user` AS `user`,_latin1'xxx' AS `rol` from `usuario` `u` where (`u`.`tipo` = _latin1'U')
union
select `u`.`user` AS `user`,_latin1'usuario' AS `rol` from `usuario` `u` where (`u`.`tipo` = _latin1'C')
union
select `u`.`user` AS `user`,_latin1'nocliente' AS `rol` from `usuario` `u` where (`u`.`tipo` = _latin1'N')
union
select `u`.`user` AS `user`,_latin1'cliente' AS `rol` from `usuario` `u` where (`u`.`tipo` = _latin1'E');

I don't have a roles class nor a roles table. Depends on your aplication.
As you can see to define a view is very easy. It's simply a select.

Laurens · **Joined:** Wed Apr 05, 2006 3:19 pm **Posts:** 43

Both pepelnm and john_yeager have been credited for their help! I have managed to get my database structure right and created a view so tomcat can still do authentication. Thank you both!

I'm going to start mapping now.

Laurens · **Joined:** Wed Apr 05, 2006 3:19 pm **Posts:** 43

I'm close, very close. at some point it worked, but I got an error, I fixed the error but now I'm getting another error. Apparently hibernate generates a query with a field that doesn't exist:

Code:

36:45,781 DEBUG AbstractCollectionPersister:511 - Static SQL for collection: objects.User.roles
36:45,781 DEBUG AbstractCollectionPersister:512 -  Row insert: insert into userroles [b](userID, roleID, elt)[/b] values (?, ?, ?)
36:45,781 DEBUG AbstractCollectionPersister:513 -  Row update: update userroles set elt=? where userID=? and roleID=?
36:45,781 DEBUG AbstractCollectionPersister:514 -  Row delete: delete from userroles where userID=? and roleID=?
36:45,781 DEBUG AbstractCollectionPersister:515 -  One-shot delete: delete from userroles where userID=?

I haven't got a field named "elt" in 'userroles'. Because of that, SQL throws an exception and the application can't run. What is this field?
These are the mappings for user and role:

User

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
   <class name="objects.User" table="users">
      <cache usage="nonstrict-read-write" />
      <id name="userID" column="userID">
            <generator class="native" />
        </id>
        <property name="userName" />
        <property name="password" />
        <property name=".......
        ..........
      
      <list name="roles" table="userroles" inverse="true" cascade="all-delete-orphan">
         <cache usage="nonstrict-read-write" />
          <key column="userID" />
         <index column="roleID" />
          <many-to-many class="objects.Role" />
      </list>
    </class>
</hibernate-mapping>

Role

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
   <class name="objects.Role" table="roles">
      <cache usage="read-only" />
      <id name="roleID" column="roleID">
            <generator class="native" />
        </id>
        <property name="roleName" />
        
        <list name="users" table="userroles" inverse="true" cascade="all-delete-orphan">
         <cache usage="nonstrict-read-write" />
          <key column="roleID" />
         <index column="userID" />
          <many-to-many class="objects.User" />
      </list>
    </class>
</hibernate-mapping>

Classes:

Code:

public class User{
    private Long userID;
    private String userName;
    private String pass..........

    private List roles;
.........
}

Code:

public class Role{
    private Long roleID;
    private String roleName;
    
    private List users;
   ..........
}

Where does that "elt" come from?

Laurens · **Joined:** Wed Apr 05, 2006 3:19 pm **Posts:** 43

Is there a solution for this?

Maybe use one-to-many/many-to-one? What would be the (dis)advantage(s) of this, compared to many-to-many?

pepelnm · **Posted:** Tue Apr 11, 2006 4:56 am

Well, the only strange thing I see is inverse="true" in both mappings.
It should be in one map only, shouldn't it?
But I'm not sure. Try it.

Laurens · **Joined:** Wed Apr 05, 2006 3:19 pm **Posts:** 43

I tried to use sets and it solved the problem... weird thing. :)

angela · **Posted:** Tue May 02, 2006 2:05 am

Laurens wrote:

I tried to use sets and it solved the problem... weird thing. :)

could you please post your solution? I have the same problem..i'm using a JDBC Realm of Tomcat as well for authentication...

I also tried reverse engineering with the HIbernate Tools, but without success :-(

Kind Regards
Angela

angela · **Posted:** Tue May 02, 2006 2:37 am

i also succeeded with the following definition:

Code:

 <set name="profile" table="TBENU_PROFIL_MATRIX"  fetch="join" >
          <key column="FK_TBENU_ILAUFNR" />
          <many-to-many column="FK_TPROFIL_ILAUFNR" class="najsre7.model.Profil" />
      </set> 

finally! :-) Now it would be nice to generate that with the Hibernate Tools ;-)

cu
angela

angela · **Posted:** Tue May 02, 2006 6:04 am

i'm having now another problem. I want to add a new Benutzer and assign a profile to it.

So i'm getting an existing Profil Object with hibernate and fill this Profile into the Set, afterwards I assign this Set to the Benutzer Object.

Code:

   Benutzer benutzer = new Benutzer();
      benutzer.setBenutzerName(benutzerName);
      benutzer.setPasswort(passwort);
      benutzer.seteMail(emailAdresse);
      benutzer.setMutUser(benutzerName);
      benutzer.setPersonId(new Integer(persId));
      benutzer.setAnzahlLogin(new Integer(0));
      benutzer.setBenutzerStatus(benutzerManager.getBenutzerStatus(new Integer(1)));

      // add benutzer to the userProfile!
      Profil profil = benutzerManager.getProfil(ProfilTyp.USER);
      Set profileSet = new HashSet();
      profileSet.add(profil);
      benutzer.setProfile(profileSet);

Then when saving the Benutzer object in the Manager Class I'm getting this exception (using hibernate with spring):

Code:

2006-05-02 11:56:25,115 ERROR Could not synchronize database state with session in (AbstractFlushingEventListener.java:300)
org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [najsre7.model.Benutzer#51]
   at org.hibernate.persister.entity.AbstractEntityPersister.check(AbstractEntityPersister.java:1635)
   at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2208)
   at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2118)
   at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2374)

these are the sql statements hibernate is executing:

Code:

insert into NAJSRE7_WEB.TBENUTZER 
(DMUTDAT,FK_TBENUSTAT_ISTATUS,SBENUTZERNAME,SPASSWORT,SEMAIL,DLASTLOGIN,IANZLOGIN,SMUTUSER,SSTVUSER,FK_TPER_IPERSNR,ILAUFNUMMER) VALUES
(?      ,?                   ,?            ,?        ,?     ,?         ,?        ,?       ,?       ,?              ,?          )

update NAJSRE7_WEB.TBENUTZER set DMUTDAT=?,
FK_TBENUSTAT_ISTATUS=?,
SBENUTZERNAME=?,
SPASSWORT=?,
SEMAIL=?,
DLASTLOGIN=?,
IANZLOGIN=?,
SMUTUSER=?,
SSTVUSER=?,
FK_TPER_IPERSNR=?
where ILAUFNUMMER=?
and DMUTDAT=?

I can't see any SQL insert statement from the join table TBENU_PROFIL_MATRIX ! :-(

Did i something wrong in the mapping file? Do i have to define a cascade or inverse attribute?

kind regards
angela