CODEWITHSUNDEEP
javahibernatejpajoin
Vitaljok
Vitaljok

Reputation: 623

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:

Thanks!

Upvotes: 13

Views: 33981

Answers (2)

jjbravo
jjbravo

Reputation: 286

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

Upvotes: 27

Thiago Ara&#250;jo
Thiago Ara&#250;jo

Reputation: 820

You must use:

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

Upvotes: 4

Related Questions