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!
Joining Tables with JPA Specifications We can observe from our data model that the Author entity shares a one-to-many relationship with the Book entity: The Criteria Query API allows us to join the two tables when creating the Specification. As a result, we'll be able to include the fields from the Book entity inside our queries:
JPA JAVA EE @JoinTable can be used to map following associations to database table: bidirectional many-to-one/one-to-many, unidirectional many-to-one, and one-to-one (both bidirectional and unidirectional) associations.
JPA Join Types 1 Overview. In this tutorial, we'll look at different join types supported by JPA. ... 2 Sample Data Model. Let's look at our sample data model that we'll use in the examples. ... 3 Inner Joins. We'll start with inner joins. ... 4 Outer Join. ... 5 Joins in the WHERE Clause. ... 6 Multiple Joins. ... 7 Fetch Joins. ... 8 Summary. ...
This is also referred to as a left outer join. JPA doesn't provide right joins where we also collect non-matching records from the right entity. Although, we can simulate right joins by swapping entities in the FROM clause. 5. Joins in the WHERE Clause 5.1. With a Condition
You can use @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;
You must use:
@WhereJoinTable(clause = "ROLE ='SIGNER'")
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With