Join 3 tables in Spring Jpa Data

Question

I've been struggling lately to join 3 tables with spring data jpa. I have 3 entities, Series, Dossier and Item. Series has many Dossiers, and Dossier has many Items (Relationships). I do something like Series.join(Dossier_.series).join(Dossier_.items) and I end up with a Join set. I want to make the following query:

Select Items from Series,Dossier,Item 
Where Series.Id=Dossier.seriesId 
and Dossier.id=Item.dossierId 
and series.projectId = :param

I can't express this statement with Spring Specifications and criteria api....Please shed some light

Answer 1

It is more a JPA question.

First, I always emphasize, you are not access "tables". You should view them as domain entities. Lot of misuse of JPA/Hibernate/other ORMs actually comes from direct "translate" of SQL or database concepts.

Back to your question, the answer is simple. First make sure you actually have the "relationships" in your domain entities. Storing IDs is not helping to build a concrete 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 query is straight-forward:

select s.dossiers.items from Series s where s.projectId = :param

Or, if it is more reasonable to have only the @ManyToOnes and omit the @OneToManys, the query is still straight-forward:

from Item where i.dossier.series.projectId = :param