JPA @JoinTable with additional join conditions

Question

I spent a couple of hours searching and did not find anything similar to my case.

Assume for a many-to-many data model:

  Contract (any business entity)
 - contract_id
 - other fields

 Party (another business entity)
 - party_id
 - other fields

 Contract_Party (relations between first two 
 with additional role indicator, eg owner, signer, seller, etc)
 - contract_id
 - party_id
 - role

Now suppose that I want to map all contracts related to a party (unidirectional). This can be done using the following annotations in the Party entity class:

 @OneToMany @JoinTable( name="Contract_Party", joinColumns = {@JoinColumn(name="party_id", referencedColumnName="party_id")}, inverseJoinColumns = {@JoinColumn(name="contract_id", referencedColumnName="contract_id")} } private List<Contract> contracts; 

This is normal.

But I'm looking for how to map contracts to a specific role ?

 @OneToMany @??? ( "ROLE = 'SIGNER' ") private List<Contract> signedContracts; 

Technically, I'm looking for a way to add an extra condition to a JOIN statement.

So far, the following ideas have been found in similar topics:

• match the connection table as a separate object and perform role filtering using custom queries;
• Hibernate has an @JoinFormula annotation but is not able to apply it inside @JoinTable;
• Hibernate also has an @Where annotation, but adds a condition for the Contract table not for the join table;
• use @MapKeyColumn and return Map instead of List, but I can have several contracts for one role;
• create a view on the DB side (it really works :)

Thanks!

Answer 1

You can use the @WhereJoinTable annotation. It applies to the association table.

 @OneToMany @JoinTable( name="Contract_Party", joinColumns = {@JoinColumn(name="party_id",referencedColumnName="party_id")}, inverseJoinColumns = {@JoinColumn(name="contract_id", referencedColumnName="contract_id")} } @WhereJoinTable ( "ROLE = 'SIGNER' ") private List<Contract> contracts; 

Answer 2

You should use:

 @WhereJoinTable(clause = "ROLE ='SIGNER'")