1

I'm currently messing around with Spring Data JPA Specifications. I came across the following question: How can I define the following SQL Join with just JPA Specifications?

SELECT * FROM competition c
   LEFT JOIN participation p ON c.id = p.competition_id
   LEFT JOIN team t ON p.team_id = t.id
   WHERE t.name = 'WDB'

Note that Competition, Participation and Team are JPA Entities (@Entity)!

Sebastian Ullrich
  • 1,007
  • 11
  • 21
  • Are you trying to do something like https://stackoverflow.com/questions/19977130/joining-two-table-entities-in-spring-data-jpa ? – marojbor Apr 07 '18 at 13:25

1 Answers1

0

A JPQL query could be as follows;

SELECT c FROM competition c 
LEFT JOIN c.participation p
LEFT JOIN p.team t
WHERE t.name = 'WDB'

The above assuming that from the original description of the problem, that the entities are defined like in the following (accessors and other details omitted).

@Entity
public class Competition
@ManyToOne
public List<Participation> participation;

@Entity
public class Participation
@ManyToOne
public List<Team> team;

@Entity
public class Team
private String name;

If your return object is not an Competition or other entity object, then you could define another transfer class and make use of JPQL's NEW operation to return an instance of it.

garfield
  • 571
  • 9
  • 21
  • Thanks a lot for your reply. Unfortunately I'm already using named JPQL queries. That is why I want to implement this with Specification. – Sebastian Ullrich Apr 08 '18 at 17:39
  • There's no such thing as Spring Data Specification, Spring Data is a library/framework/utility programming model on top of various Persistence Technologies one of them being JPA. So In the context of Spring Data you can have JPQL queries as value of the '@Query' annotation on a repository method,or you can use any other JPA feature if you want like NativeQueries etc.Have a look at Spring Data examples in particular to : https://github.com/spring-projects/spring-data-examples/blob/master/jpa/example/src/main/java/example/springdata/jpa/projections/CustomerRepository.java – garfield Apr 09 '18 at 06:58