I have a legacy database that I am trying to use Hibernate with and am having trouble creating relationships between three beans. I am unable to change the schema of the database so must adapt my code to get things to work even though I know the table design is suboptimal. Here are the details:
DB tables:
d_code
-----------
code
person_id
m_id (can be null even though part of composite key so must be outside hibernate's primary key)
date
seq_num
gender
age
primary key(person_id, m_id, date, seq_num, code)
foreign key (person_id, m_id, date, seq_num)
text
------------
text
person_id
m_id (can be null even though part of composite key so must be outside hibernate's primary key)
date
seq_num
primary key(person_id, m_id, date, seq_num)
Hibernate mapping files
------------------------
Code
Code:
<hibernate-mapping package="com.demo.Code">
<class name="Code" table="d_code">
<composite-id name="CodePK" class="com.demo.CodePK">
<key-property name="Date" column="date" type="integer" />
<key-property name="Code" column="code" type="string" />
<key-property name="PersonId" column="person_id" type="integer" />
<key-property name="SequenceNumber" column="text_seq" type="integer" />
</composite-id>
<property name="MId" column="m_id" type="integer" />
</class>
</hibernate-mapping>
Text
Code:
<hibernate-mapping package="com.demo.Text">
<class name="Text" table="text">
<composite-id name="TextPK" class="com.demo.TextPK">
<key-property name="Date" column="date" type="integer" />
<key-property name="PersonId" column="person_id" type="integer" />
<key-property name="SequenceNumber" column="text_seq" type="integer" />
</composite-id>
<property name="MId" column="m_id" type="integer" />
<property name="Text" column="text" type="string" />
</class>
</hibernate-mapping>
Instance
Code:
<hibernate-mapping package="com.demo.Instance">
<class name="Instance" table="d_code">
<composite-id name="InstancePK" class="com.demo.InstancePK">
<key-property name="Date" column="date" type="integer" />
<key-property name="PersonId" column="person_id" type="integer" />
</composite-id>
<property name="Gender" column="gender" type="string" />
<property name="Age" column="age" type="integer" />
<loader query-ref="DefaultLoader"/>
</class>
<sql-query name="DefaultLoader">
<return alias="Ins" class="com.demo.Instance"/>
select distinct dc.person_id as {Ins.InstancePK.PersonId},
dc.date as {Ins.InstancePK.Date},
dc.age as {Ins.Age},
dc.gender as {Ins.Gender}
from d_code dc
where dc.person_id = :pid
</sql-query>
</hibernate-mapping>
This all works fine, but I am having trouble creating relations between the three beans. There should be a one-to-many relationship going from Instance to Text as instance is really just the distinct demographic information that is stored in the same table as code. (It should have been two tables as there is much duplication across each code for a single person and date, but I can't change that.) Also, there should be a one-to-many relationship going from Text to Code as each text phrase can have many codes. The problem is I keep getting various errors (like foreign key must have same number of columns as primary key).
Can anyone suggest the best way to handle this?
Login
FAQ
Search