Hibernate relation OneToMany with non unique key

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 defin开发者_Python百科ition - 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 ...)

UPDATED

First off, you mention that you want to use all features which ORM offers. If this is the case then you need to use a schema that Hibernate is friendly toward. There are annotations available in both the JPA persistence API and with Hibernate-specific API that allow you to more easily work with 'legacy' databases. But if you want full and correct Hibernate use, then you have to design your schema according to what Hibernate expects.

You can do what you are asking in this case by using a join formula instead of a join column for your relationships. The join formula is a valid SQL fragment (which may reduce portability). I've left the actual SQL out in the example below.

public class Person {
    ...
    @OneToMany
    @JoinFormula("sql fragment")
    private List<Department> departments;
    ...
}

public class Department {
    ...
    @OneToMany
    @JoinFormula("sql fragment")
    private List<Person> people;
    ...
}

You should also consider treating code as a Hibernate object:

@Embeddable
public class Code {
    ...
    @Column(nullable = false, length = 20)
    private String code;
    ...
}

Rather than just a string so that the use of a code in entity relationships is more effectively managed by the Hibernate persistence context and the row mapping process.

Lastly, consider actually mapping the relationship between Person and Department as a join table in your schema. You'd then be able to take advantage of the @ManyToMany and @JoinTable annotations to do actual schema-based relationships in your entities.

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.