JPA Hibernate manyToMany with non-unique key

I have two entities (tables): Department and Person. Both tables have a field CODE which is not unique.

How can I define manyToMany bidirec开发者_如何学Ctional relations between these tables?

1. Department has collection Persons which contains all entities with Person.CODE eq Department.CODE
2. Partner has collection Departments which contains all entities with Department.CODE eq Partner.CODE

I need the relation definition - no sql or hpql query.

it is no problem to do it with HPQL, but I need annotation.

Select persons for given departmentID:

select P.* from Person P, Deparment d  
where d.department_id = ? and 
p.code = d.code and 
? between d.validFrom and d.validTill and  
? between p.validFrom and p.validTill 

It is possible with hibernate formula?

---

You would definitely need a third table in the DB to own the many-to-many relation to start with, call it department_person. Declare two foreign keys from this that refer to Persons and to Departments table.

Afterwards, you could either:

1) define a new JPA entity DepartmentPerson and define to many-to-one relations from each foreign key. You should consequently create two one-to-many relations which would point from Persons to DepartmentPerson and from Department to DepartmentPerson yielding the Collections you need.

2) define a many-to-many relation in one of the two initial entities stating that department_person is the owner of the relation.