Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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!

like image 250
Vitaljok Avatar asked Jan 14 '15 14:01

Vitaljok


People also ask

How do I join two tables in JPA?

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:

What is the use of @jointable in JPA?

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.

What are the different types of join types in JPA?

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. ...

What is a LEFT OUTER JOIN in JPA?

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


2 Answers

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;
like image 141
jjbravo Avatar answered Oct 04 '22 02:10

jjbravo


You must use:

@WhereJoinTable(clause = "ROLE ='SIGNER'")
like image 32
Thiago Araújo Avatar answered Oct 04 '22 04:10

Thiago Araújo