Hi,


Can any one help out me? I am struggling with this problem from last couple of days. I have two tables employee and employee_io. employeeIO has a composite-id with three feilds like below.

CREATE TABLE `employee_io` (
`cardno` varchar(16) NOT NULL default '',
`holderno` varchar(16) default NULL,
`holdername` varchar(64) default NULL,
`iodate` date NOT NULL default '0000-00-00',
`iotime` time NOT NULL default '00:00:00',
PRIMARY KEY (`cardno`,`iodate`,`iotime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

and employee table is like below

CREATE TABLE `employee` (
`employee_id` varchar(16) NOT NULL default '',
`first_name` varchar(50) NOT NULL default '',
`last_name` varchar(50) NOT NULL default '',
`email_company` varchar(50) NOT NULL default '',
`email_alternate` varchar(50) default NULL,
`cardno` varchar(16) default NULL,
PRIMARY KEY (`employee_id`),
KEY `FK_CARDNO` (`cardno`),
CONSTRAINT `FK_CARDNO` FOREIGN KEY (`cardno`) REFERENCES `employee_iodata` (`cardno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1



How should i map these two tables

Show this post: http://forum.hibernate.org/viewtopic.php?t=971839

Two solutions:

1.- Use a surrogate key, and them, use unique constraint.

2.- Your employee_io class is a value class. Map in the employee.hbm.class using composite-element.

Hi,

Thanks for fast reply first. Can u explain in detail for both solutions. Will be more helpful if u reply with the code for second solution.

The relation is one-to-many, the employee_io is relationed with many instances of employee. Is it true?

Well, then, my second solution is wrong, because employeeIO class has its own lifecycle.

You must use the first solution, create a surrogate key.

Hi,


Thanks for reply. Here one employee can have more than one instance of employee_io. So it many-to-one i think. I have never used surogate key. Can u help out how to implement it.

Thanks in advance.

As per your table definitions following would be an implementaion of surrogate key

1. Create a new primary key column in table employee_io which is of type sequence or uuid with column name employee_io_id

2. On employee_io table, create a unique index or a unique constraint on columns (cardno + iodate + iotime) This will retain you existing primary key unique check

3. Drop existing FK column cardno on employee table.

4. Migrate the new emloyee_io_id column created on employee_io table as the FK to employee table

Hi,

Thank you for the quick reply. I can not change the database as it is designed by the client. So please help me out with out changing the database and table references. Please help me.


Thanks

Hi,


It is very urgent. Please help me with out changing database and table references.

Try to use the composite-id element.

I've never used this element, but in googgle codesearh, you can find some examples:

http://www.google.com/codesearch?hl=es&lr=&q=file%3Ahbm.xml+composite-id&btnG=B%C3%BAsqueda


For example, look this example:

http://www.google.com/codesearch?hl=es&q=+file:hbm.xml+composite-id+show:6uIj77wbdTY:3tfvcx0NVQY:LCsoJlFTKm4&sa=N&cd=4&ct=rc&cs_p=http://middleware.internet2.edu/signet/releases/signet-0.1-bin.tar.gz&cs_f=webapp/signet/WEB-INF/classes/edu/internet2/middleware/signet/SubjectAttributeValue.hbm.xml#a0




You can use this element with your composite-key.

Good luck!!

Hi,


I already tried using <composite-id> before posting in to form,to which am getting error like

Foreign key (FK4722E6AE2EADB939:employee [cardno])) must have same number of columns as the referenced primary key (employee_iodata [cardno,iodate,iotime])
at org.hibernate.mapping.ForeignKey.alignColumns(ForeignKey.java:86)


Here are my mappings

employee_io mapping


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.s4.domain.EmployeeIOData" table="employee_iodata"
dynamic-update="false" dynamic-insert="false"
select-before-update="false" optimistic-lock="version">

<!-- <id name="cardno" column="cardno" type="java.lang.String">
<generator class="assigned" />

</id> -->

<composite-id name="employeeIO"
class="com.s4.domain.EmployeeIO">
<key-property name="cardno" column="cardno"
type="java.lang.String" />
<key-property name="iodate" column="iodate"
type="java.lang.String" />
<key-property name="iotime" column="iotime"
type="java.lang.String" />
</composite-id>
<property name="holderno" type="java.lang.String" update="true"
insert="true" access="property" column="holderno" length="16" />

<property name="holdername" type="java.lang.String"
update="true" insert="true" access="property" column="holdername"
length="64" />

<property name="iostatus" type="java.lang.String" update="true"
insert="true" access="property" column="iostatus" length="16" />
<property name="iogateno" type="java.lang.String" update="true"
insert="true" access="property" column="iogateno" length="16" />
<property name="iogatename" type="java.lang.String"
update="true" insert="true" access="property" column="iogatename"
length="64" />

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-Activity.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>


employee.hbm.xml is as follows
<?xml version="1.0" encoding="UTF-8"?>

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

<hibernate-mapping>
<class name="com.s4.domain.Employee" table="employee"
dynamic-update="false" dynamic-insert="false"
select-before-update="false" optimistic-lock="version">


<id name="employee_id" column="employee_id"
type="java.lang.String">
<generator class="assigned" />

</id>

<property name="first_name" type="java.lang.String"
update="true" insert="true" access="property" column="first_name"
length="50" />

<property name="last_name" type="java.lang.String" update="true"
insert="true" access="property" column="last_name" length="50" />

<property name="email_company" type="java.lang.String"
update="true" insert="true" access="property" column="email_company"
length="50" />

<property name="email_alternate" type="java.lang.String"
update="true" insert="true" access="property"
column="email_alternate" length="50" />



<many-to-one name="cardno" class="com.s4.domain.EmployeeIOData"
cascade="save-update" outer-join="auto" update="true" insert="true"
access="property" column="cardno" />

</class>

</hibernate-mapping>

Here i have to get reference of cardno into employee.


Thank u in advance.

You have got a bad design of your databasa:

1.- You have a employee_io table. This table has got a multiple-column ley. The key is formed by (cardno, iodate, iotime) columns.

2.- Then, you've got another table, employee. This table has got a foreign key. cardno column references at employee.cardno. This situation is wrong, because the primary key of your employee_io table is a multiple column.

Is the cardno column of your employee_io table unique? If it is true, cardno would be the column key.

Hi,

I agree with u but as it is already deployed i cannot change it now.
Here are my employee and employee_io tables.



CREATE TABLE `employee_io` (
`cardno` varchar(16) NOT NULL default '',
`holderno` varchar(16) default NULL,
`holdername` varchar(64) default NULL,
`iodate` date NOT NULL default '0000-00-00',
`iotime` time NOT NULL default '00:00:00',
`iogateno` varchar(16) default NULL,
`iogatename` varchar(64) default NULL,
`iostatus` varchar(16) default NULL,
PRIMARY KEY (`cardno`,`iodate`,`iotime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


CREATE TABLE `employee` (
`employee_id` varchar(16) NOT NULL default '',
`first_name` varchar(50) NOT NULL default '',
`last_name` varchar(50) NOT NULL default '',
`email_company` varchar(50) NOT NULL default '',
`email_alternate` varchar(50) default NULL,
`cardno` varchar(16) default NULL,
PRIMARY KEY (`employee_id`),
KEY `FK_CARDNO` (`cardno`),
CONSTRAINT `FK_CARDNO` FOREIGN KEY (`cardno`) REFERENCES `employee_iodata` (`cardno`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Please help out for this situation.


Thanks in advance

Hi,

Please help me how to map those two tables? I tried it by using composite-id, which gave me errors. Is there any alternative to overcome these type of table mappings??

Does hibernate not provide the solutions for this kind of situation?? If yes what is the solution for this kind of mapping??

Though your database schema is not 100% ok,
I think you can add to the many-to-one:




If that doesn't work (because cardno is part of the composite key), you might need to add a read-only property to the EmployeeIOData class for that property
(property with insert='false', update='false')

And hope it is always unique (you will get into trouble if the many-to-one actually refers to multiple records).
Consider adding a unique constraint for that column (just to be sure!)