Hello,
I am a newbie at Hibernate so please have patience!
I am using Hibernate 3.1 with Sybase ASE 15.x backend.
I have a question regarding surrogate keys and its effect on table relationships.
Scenario:
Say I have 3 tables.
tb_user: userid char(20) Pri Key, first_name char(40), last_name char40)
tb_user_groups: groupid char(20) Pri Key, group_description char(40)
tb_groupmember: groupid char(20) Pri Key, userid char(20) Pri Key
The above is what we have typically in our databases. I have read that adding a surrogate key column is a good thing as far as Hibernate is concerned, especially for a table like tb_groupmember from above, where otherwise an ID Java class would be neccessary.
So what I did was I added an identity column to each table called "recordid", set it as the Primary Key, and changed the existing Primary key as a unique key instead.
Pretty simple and sensible so far.
Let's say I am working on tb_groupmember. The mapping for this table consists of 2 classes, tbUser and tbUserGroups.
Code:
tbGroupmember.hbm.xml
<hibernate-mapping>
<class name="com.natixis.web.hibernate.mapping.TbGroupmember" table="tb_groupmember" schema="dbo" catalog="fxrep">
<id name="recordid" type="java.lang.Integer">
<column name="recordid" />
<generator class="assigned" />
</id>
<many-to-one name="tbUserGroup" class="com.natixis.web.hibernate.mapping.TbUserGroup" fetch="select">
<column name="groupid" length="20" not-null="true" />
</many-to-one>
<many-to-one name="tbUser" class="com.natixis.web.hibernate.mapping.TbUser" fetch="select">
<column name="userid" length="20" not-null="true" />
</many-to-one>
</class>
</hibernate-mapping>
tbUser.hbm.xml
<hibernate-mapping>
<class name="com.natixis.web.hibernate.mapping.TbUser" table="tb_user" schema="dbo" catalog="fxrep">
<id name="recordid" type="java.lang.Integer">
<column name="recordid" />
<generator class="assigned" />
</id>
<property name="userid" type="java.lang.String">
<column name="userid" length="20" not-null="true" unique="true" />
</property>
<property name="firstName" type="java.lang.String">
<column name="first_name" length="30" />
</property>
<property name="lastName" type="java.lang.String">
<column name="last_name" length="30" />
</property>
<set name="tbGroupmembers" inverse="true">
<key>
<column name="userid" length="20" not-null="true" />
</key>
<one-to-many class="com.natixis.web.hibernate.mapping.TbGroupmember" />
</set>
</class>
</hibernate-mapping>
tbUserGroup.hbm.xml
<hibernate-mapping>
<class name="com.natixis.web.hibernate.mapping.TbUserGroup" table="tb_user_group" schema="dbo" catalog="fxrep">
<id name="recordid" type="java.lang.Integer">
<column name="recordid" />
<generator class="assigned" />
</id>
<property name="groupid" type="java.lang.String">
<column name="groupid" length="20" not-null="true" unique="true" />
</propery>
<property name="description" type="java.lang.String">
<column name="description" length="100" not-null="true" />
</property>
<set name="tbGroupmembers" inverse="true">
<key>
<column name="groupid" length="20" not-null="true" />
</key>
<one-to-many class="com.natixis.web.hibernate.mapping.TbGroupmember" />
</set>
</class>
</hibernate-mapping>
Say I want to perform a search by userid on tb_user_groups. The query would look something like
Code:
from tbUserGroups as model where model.tbUser.userid = ?
What I am seeing is that Hibernate will indeed perform a join between tb_user_groups and tb_user as expected. But instead of doing "tb_user_groups.
userid = tb_user.
userid", it does "tb_user_groups.
userid = tb_user.
recordid".
This despite the fact that I have the relationship defined in the tbUserGroups.hbm.xml mapping file as below:
Code:
<many-to-one name="tbUser" class="com.natixis.web.hibernate.mapping.TbUser" fetch="select">
<column name="userid" length="20" not-null="true" />
</many-to-one>
So I am a little confused here. Is there not a way to prevent Hibernate from automatically adding this join to tbUser's primary key which is recordid? It's a surrogate key and holds no business meaning.
I mean, I can see eliminating the surrogate key from tbUser since it doesn't really serve much of a purpose, but I'd like to know if there is a way here.
Perhaps I am doing something terribly wrong here, which wouldn't be out of the question!
Thanks!!!
Nick
Login
FAQ
Search
