I'm having trouble trying to create a hibernate mapping file for my application. The POJOs that I coded consist of Employee, Skill, Skill Level, and EmployeeSkill. I would like to code a method in the Employee POJO called getEmployeeSkills() which returns a collection of EmployeeSkill objects (maps skills with skill level to an employee). Given the scenario that I just described, how would I best design the Employee.hbm.xml? I really have not idea how to go about doing this. The EMPLOYEE_SKILL table is not your traditional join table (joining one table to another.) It joins one table (employee table) to 2 tables (skill and skill level tables.) Employee has a many to many relationship will skill and also skill_level, but the relationship should be skill and skill level together. I don't think <many-to-many> will work for what I just described. Can someone please help? I've been struggling with this for days and am about to give up on hibernate and just use JDBC.
Thanks in advanced!
Code:
CREATE TABLE EMPLOYEE
(
id int primary key,
name varchar(255) not null,
);
INSERT INTO EMPLOYEE(id, name) values (1, 'John Smith');
INSERT INTO EMPLOYEE(id, name) values (2, 'Jane Smith');
create table skill
(
id int primary key,
skill varchar(255) not null
);
INSERT INTO SKILL(id, skill) values (1, 'Java');
INSERT INTO SKILL(id, skill) values (2, 'C');
create table skill_level
(
id int primary key,
skill_level varchar(255) not null
);
INSERT INTO skill_level(id, skill_level) values (1, 'Beginner');
INSERT INTO skill_level(id, skill_level) values (2, 'Intermediate');
INSERT INTO skill_level(id, skill_level) values (3, 'Expert');
create table employee_skill
(
id int primary key,
employee_id int not null,
skill_id int not null,
skill_level_id int not null,
foreign key(employee_id) references employee(id),
foreign key(skill_id) references skill(id),
foreign key(skill_level_id) references skill_level(id)
);
INSERT INTO employee_skill(id, employee_id, skill_id, skill_level_id) values (1, 1, 1, 1);
INSERT INTO employee_skill(id, employee_id, skill_id, skill_level_id) values (2, 2, 2, 3);