Mapping one property to two columns (tomcat authentication)

henrikp · **Joined:** Wed Jul 26, 2006 2:26 pm **Posts:** 1

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.1

Hi,

I have the following scenario. These are my tables:

-------------------------------
| USER |
-------------------------------
| PK | ID |
| VARHCHAR | USERNAME |
| VARCHAR | PASSWORD|
--------------------------------

-------------------------------
| ROLES |
-------------------------------
| PK | ID |
| VARHCHAR | USERNAME |
| VARCHAR | ROLENAME |
--------------------------------

- Implementation is in MySQL 5.0.
- I am using hibernate 3.1 on Java 1.5
- Note that the link between the user table and the roles table is through the USERNAME column.
- The default value for ROLENAME is "administrator" (In fact, this is the only role)
- Assume that we have a class User with properties username and password
- We are not really interested in mapping any values from the ROLES table (including ROLENAME)

How can I make hibernate add, delete and update the ROLES.USERNAME every time the USER.USERNAME is modified? So far I have looked at:
- one-to-one mapping of ROLES>ROLENAME to class User (added property String rolename) using/not using the property-ref attribute
- join mapping, using/not using the property-ref attribute

For the people that are interested, this particularly nasty database schema is forced upon you if you choose Tomcat 5 container-based authentication. No way around it....

Thanks!

Sincerely,
Henrik Pettersen
Advanced Computational Laboratory
Cancer Research UK

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

You could use an <sql-insert> element to insert new rows in the ROLES table when adding new rows in the USER table. This bypasses mapping ROLES entirely. Put the <sql-insert> element on the User mapping, and have it call a stored proc that creates the user according to the correct parameters, and the role according to the user.

If you want to map ROLES, then map the USER->ROLES relationship as a <set> of <one-to-many> that just happens to only ever have one member. The User class will need two constructors: a no-arg for use by hibernate, that does nothing special, and a different constructor for use by API users, that creates the set of Roles.

pepelnm · **Posted:** Wed Aug 16, 2006 3:36 am

As you are using MySQL5 that supports views, I suggest you something like this:
-------------------------------
| USER |
-------------------------------
| PK | ID |
| VARHCHAR | USERNAME |
| VARCHAR | PASSWORD|
--------------------------------

-------------------------------
| ROLE |
-------------------------------
| PK | ID |
| VARCHAR | ROLENAME |
--------------------------------

-------------------------------
| USER_ROLE |
-------------------------------
| PK | USER_ID |
| PK | ROLE_ID |
--------------------------------

Code:

create view `V_USER_ROLE` as
select
   `U`.`USERNAME` as `USERNAME`,
   `R`.`ROLENAME` as `ROLENAME`
from
   `USER` as`U`,
   inner join `USER_ROLE` as `UR` on `UR`.`USER_ID` = `U`.`ID`
   inner join `ROLE` as `R` on `UR`.`ROLE_ID` = `R`.`ID`

In Tomcat context.xml:

Code:

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

   userTable="USER"
   userCredCol="PASSWORD"

   userNameCol="USERNAME"

   userRoleTable="V_USER_ROLE"
   roleNameCol="ROLENAME"
/> 

For tenwit:
"What the heck is something?"
Would it be considered rude language?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

No, "heck" is very definitely a polite swear word. It may be the first euphemism, even. Why do you ask?

pepelnm · **Posted:** Thu Aug 17, 2006 3:02 am

For tenwit:
Because of this thread. http://forum.hibernate.org/viewtopic.php?t=957915

I found 'heck' as a substitution for 'hell' and I wanted to know what was its "rudeness level".

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Heh. All good then. That's some memory you've got, remember stuff that happened in April..