SpringBoot: @Query doesn't detect a Many-To-Many table-CodePudding

I've created two @Entities (Player, Tournament) that are linked with a @Many-To-Many relationship.

Player.java

@Entity
@Table
public class Player {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(updatable = false)
    private String email;
    @Column
    private String name;

    // ...
    
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "PlayerTournament",
               joinColumns = @JoinColumn(name = "player_id"),
               inverseJoinColumns = @JoinColumn(name = "tournament_id"))
    private Set<Tournament> tournaments;

    // ...

}

Tournament.java

@Entity
@Table
public class Tournament{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(updatable = false)
    private String name;

    // ...

    @ManyToMany(mappedBy = "tournaments")
    private Set<Player> players= new HashSet<>();

    public Set<Player> getPlayers() { return players; }

    public void setPlayers(Set<Player> players) { this.players = players; }


    // ...

}

Now, I want to get all Tournaments where a specific Player has played in:

TournamentService.java

@Service
public class TournamentService {

    @Autowired
    private TournamentRepository repo;

    public List<Tournament> findTournamentsByPlayerId(Long id) {
        return repo.findTournamentsByPlayerId(id);
    }
}

TournamentRepository.java

@Repository
public interface TournamentRepository extends CrudRepository<Tournament, Long> {

    @Query("select t from Tournament t join PlayerTournament pt on t.id = pt.tournament_id where pt.player_id = :id")
    List<Tournament> findTournamentsByPlayerId(@Param("id") Long id);
}

But this code doesn't work.
When I try to run the Application i got this Error:
Caused by: java.lang.IllegalArgumentException: Could not resolve entity reference: PlayerTournament

The IDEE says that it Cannot resolve symbol 'PlayerTournament' when I hover on the table's name.

What am I doing wrong?
Thank you!

CodePudding user response：

You don't need @Query anatotion if you want to bring tournaments according to playerId. Spring Boot will handle it for you.

@Repository
public interface TournamentRepository extends CrudRepository<Tournament, Long> {

    List<Tournament> findTournamentsByPlayersId(Long playerId);
}

CodePudding user response：

The problem is that you are confusing JPA/JPQL and SQL.

Since you did not specify nativeQuery=true in the @Query annotation the query is considered a JPQL query. But JPQL operates on the level of entities and does not know about (mapping) tables.

The correct JPQL query should look like this:

select t from Tournament t where t.players.id = :id

Of course you can stay with your current query and use nativeQuery=true.

And finally as fathi correctly notes, you don't need to specify a query at all and can use query derivation with a method like.

List<Tournament> findTournamentsByPlayersId(Long playerId);

CodePudding user response：

try using the below code in your tournament.java

enter code here:

   @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "PlayerTournament",
               joinColumns = @JoinColumn(name = "tournament_id"),
               inverseJoinColumns = @JoinColumn(name = "player_id"))
    private Set<Player> players= new HashSet<>();