jpa - Hibernate relation OneToMany with non unique key

Question

I am not able to describe my problem, I try it again with example:

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

How can I define manyToMany bidirectional relations between these tables?

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

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

--------- Original question -------

I need to create hibernate relation one to many between Department and Person (one Department has many persons). Department and persons has time validity (validFrom, validTill)

class Department {
  Long id;
  String code; 
  String name;
  Date validFrom;
  Date validTill;
  @OneToMany(fetch = FetchType.LAZY, mappedBy = "departmentId")
  @OnDelete(action = OnDeleteAction.CASCADE)
  private Set<Person> persons = new HashSet<Person>();
}

 class Person {
      Long id;
      String name;
      String surname;
      Date validFrom;
      Date validTill;
    }

Without ORM (hibernate) it is easy to select persons of particular department at specified date:

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

The relation has to use non unique key (CODE) instead of department ID.

Is it possible to do something similar with hibernate?

I don't need separated objects and construct queries myself.

I want use all feature which ORM offers (lazy loading, cascase persist ...)

Answer 1

You can define a Filter. It allows you to add that check to every query you do, or disable it if you like.

Example:

<class name="Department" ...>
    ...
    <many-to-one name="person" column="person_id" class="Person"/>
    <property name="validFrom" type="date" column="validFrom"/>
    <property name="validTill" type="date" column="validTill"/>
    <property name="code" type="string" column="code"/>
    ...
    <!--
        Note that this assumes non-terminal records have an eff_end_dt set to
        a max db date for simplicity-sake
    -->
    <filter name="effectiveDate"
            condition="code = :code and :asOfDate BETWEEN validFrom and validTill"/>
</class>

Regards.