As a bit of background, I'm using Spring Data JPA Specifications to dynamically build queries. Specifications uses the JPA Criteria API.
I think the core of my problem is I need to perform a Right Join on a table, which EclipseLink (my provider) doesn't support.
For a simple example, let's suppose -
| TableA | TableB |
|---|---|
| id (pk) | id (pk) |
| aField | aField |
| bField | table_a_id |
@Entity
@Table(name = "TableA")
public class TableAEntity{
private String id;
private String aField;
private String bField;
@OneToMany(mappedBy = "tableA", , fetch=FetchType.EAGER)
private Set<TableBEntity> tableBSet;
}
@Entity
@Table(name = "TableB")
public class TableBEntity{
private String id;
private String aField;
@ManyToOne
@JoinColumn(name = "table_a_id", referencedColumnName = "id")
TableAEntity tableA;
}
I need to be able to fetch the tableBSet collection without resulting in N+1 queries. I've tried @BatchFetch and @JoinFetch annotations in EclipseLink without positive results. I've tried using two roots in Critera and adding -
builder.equal(tableARoot.get(TableAEntity_.id),
tableBRoot.get(TableBEntity_.tableA).get(TableAEntity_.id))
As an attempt to manually perform a right join in SQL but that isn't working either.
The kicker is changing the strong side of the relationship isn't an option because I need to able to do this in reverse as well. I have an two APIs using the entities and it needs to return the Set or single Entity that is related to the parent in both cases.
The design I'm working with is significantly more complicated than this with multiple relationships on each entity and and the need to fetch the collections for most of them varying on certain parameters received by the API.
At this point, I'm vested in the frameworks that are chosen and need to get this working efficiently with Spring Data Specifications, which is using JPA Criteria API.
I'm new to the Criteria API and I've read everything I can but haven't been able to implement a solution that is fetching the weak collection without N+1 queries. I felt like combing roots was the only way (which I know isn't great because I'll end up with a cartesian product) but I'm unable to get that work. fetch() and join() don't seem to help because I can only do left joins.
What is the best way to go about doing this or am I trying to do too much with the Criteria API?
@JoinFetch or root.fetch() is the correct way. I was paging the results, which was making it seem like data wasn't being returned due to the duplicate rows in the product of multiple tables. I had to create a custom pagination to query by IDs for the request page to work around this.