Composite Key with Hibernate

Question

In order to generate the next SQL code:

create table users (
    user_name varchar(15) not null primary key, 
    user_pass varchar(15) not null);

create table user_roles(
    username varchar(15) not null,
    role_name varchar(15) not null, 
    primary key(usernmae, rolename)
);

You can use code such as:

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

<hibernate-mapping>
    <class name="databaselayer.users.UserDB" table="users">
        <id name="username" type="string" column="user_name">
                <meta attribute="scope-set">public</meta>      
        </id>    
        <property name="password" type="string" column="user_pass"  not-null="true"/>
        <set name="roles" cascade="save-update" inverse="true">
                <key column="user_name"/>
                <one-to-many class="databaselayer.users.RoleDB"/>
        </set>                       
    </class>
   <class name="databaselayer.users.RoleDB" table="user_roles">
     <composite-id>
            <key-many-to-one name="username" class="databaselayer.users.UserDB" column="user_name"/>
            <key-property name="role" type="string" column="role_name"/>
        </composite-id>             
    </class>          
</hibernate-mapping>

You may need to have your classes as well as hbm mapping files in your classpath.
Next I post my Ant schema target:
Note ${basedir}/build/WEB-INF/classes contains both *.class and *.hbm.xml files.

<target name="schema" description="Generate DB schema from the O/R mapping files">
    <hibernatetool destdir="${basedir}">      
        <classpath path="${basedir}/build/WEB-INF/classes">
            <fileset dir="${basedir}/build/WEB-INF/classes">
         <include name="**/*"/>
     </fileset>    
        </classpath>
        <configuration configurationfile="${basedir}/hibernate.cfg.xml"/>
        <hbm2ddl 
            drop="true" 
         create="true"
         export="true"
         outputfilename="libamo2-ddl.sql"
           delimiter=";"
         format="true"/>       
    </hibernatetool>
</target>

Related links from Hibernate documentation

Composite ID
Components as composite Id

Answer 1

Look in the hibernate docs for the 'composite-id' element. The following may at least give you the intent -- replace your id element with:

<composite-id>
    <key-property name="username"/>
    <key-property name="role"/>
</composite-id>

Note that it's strongly recommended to instead make your ID a separate class ('component') that implements hashCode and equals properly, and that is Serializable. Otherwise you'll have only very awkward ways to lookup your object using session.get() or session.load().

Answer 2

Maybe in fact something like this:

<class name="User" table="users">
  <id name="username" column="user_name"/>
  <property name="password" column="user_pass" not-null="true"/>
  <set name="roles" table="user_roles">
    <key column="user_name"/>
    <element type="string" column="role_name" />
  </set>
</class>

This will map the roles to a collection in the User class, and roughly matches the schema you gave. It won't generate a primary key across the columns in user_roles though.

You'll generally get better results from Hibernate with more normalised schemas though. For instance, in this case, you don't have a table for roles themselves; but what you seem to be describing would fairly naturally be modelled as a User and Role class, with a many-to-many relationship between them. Something like this:

<class name="User" table="users">
  <id name="username" column="user_name"/>
  <property name="password" column="user_pass" not-null="true"/>
  <set name="roles" table="user_roles">
    <key column="user_name"/>
    <many-to-many column="role_name" class="Role"/>
  </set>
</class>
<class name="Role" table="roles">
  <id name="name" column="role_name"/>
</class>

Now this will produce a schema with tables users, roles and user_roles and now user_roles will have a primary key across the user name and role name column. You also now have somewhere to hang additional information about roles, such as descriptions.

Also, the error you're posting is a ClassNotFoundException which seems unlikely to be an actual problem with your mapping as such.

You can do this using a composite ID if you must but I'd say it's a load of pain that you just don't want to get into for something this simple.

Answer 3

Sergio, look at this composite key mapping in hibernate tutorial. Might help you out.