Search code examples
javahibernatejpajoin

JPA @JoinTable with extra join conditions

I have spent couple of hours searching around and did not found anything similar to my case.

Let's assume following 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, e.g. owner, signer, seller, etc)
- contract_id
- party_id
- role

Now let's assume I want to map all contracts related to party (uni-directional). It can be done using following annotations in 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;

That is fine.

But what I'm looking for is how to map contracts with particular role?

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

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

So far found following ideas in similar topics:

  • map join table as separate entity, and perform filtering by role using custom queries;
  • Hibernate has @JoinFormula annotation, but no way to apply it inside @JoinTable;
  • Hibernate also has @Where annotation, but it adds condition for Contract table not for join table;
  • use @MapKeyColumn and return Map instead of List, but I can have multiple contracts per one role;
  • create a view on DB side (this one works indeed :)

Thanks!

Solution

  • You can use @SQLJoinTableRestriction 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")}
}
@SQLJoinTableRestriction( "ROLE = 'SIGNER' ")
private List<Contract> contracts;

    UPDATE: As of Hibernate 6.3, @WhereJoinTable is deprecated in favour of @SQLJoinTableRestriction