Join 3 tables in Spring Jpa Data
I have been struggling lately for joining three tables with spring jpa data. I have 3 objects Series
, Dossier
and Item
. Series
has a lot Dossiers
, but Dossier
has a lot Items
(Relationship). I am doing something like Series.join(Dossier_.series).join(Dossier_.items)
and I end up with a Join set. I want to make the following request:
Select Items from Series,Dossier,Item
Where Series.Id=Dossier.seriesId
and Dossier.id=Item.dossierId
and series.projectId = :param
I cannot express this statement with spring specs and api criteria .... Please shed some light
source to share
This is a JPA question.
First, I always stress that you don't have access to "tables". You must view them as domain objects. A lot of the misuse of JPA / Hibernate / other ORMs actually stems from direct "translation" of SQL or database concepts.
Back to your question, the answer is simple. First, make sure you actually have "relationships" in your domains. Storing identifiers does not help build a specific domain model. For example, you have something like:
@Entity
class Series {
@Id
Long id;
@OneToMany(mappedBy="series")
List<Dossier> dossiers;
}
@Entity
class Dossier{
@Id
Long id;
@ManyToOne
Series series;
@OneToMany(mappedBy="dossier"
List<Item> items;
}
@Entity
class Item{
@Id
Long id;
@ManyToOne
Dossier dossier;
}
The request is simple:
select s.dossiers.items from Series s where s.projectId = :param
Or, if it's wiser to have only @ManyToOne
and omit @OneToMany
s, the query is still straightforward:
from Item where i.dossier.series.projectId = :param
source to share
[Still rocky] Maybe I haven't made myself clear. I know how to express a query in HQL. The problem is using Spring Data Specifications using the criteria api to build this query.
//Let exampine the following piece of code
public class CustomItemSpecs {
public static Specification<Item> createSpecificationFromSearchForm(final SearchForm searchForm) {
return new Specification<Item>() {
@Override
public Predicate toPredicate(Root<Item> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
CriteriaQuery<Item> cq = cb.createQuery(Item.class);
CriteriaQuery<Series> sb = cb.createQuery(Series.class);
Root<Series> series = sb.from(Series.class);
Join<Series,Dossier> join1 = series.join(Series_.dossiers);
Join<Dossier, Item> join2 = join1.join(Dossier_.items);
}
}
}
As you can see, I manage to do two separate joins. The problem is I want to join Series, Dossier And Items in order to fulfill the above query. Note that join2 is a Dossier-Item set. I cannot make criteria for example cb.equals (join2.get (Series_.projectId),)
source to share