CODEWITHSUNDEEP
mysqlsqlspringspring-bootjpa
editsons
editsons

Reputation: 97

Spring Data JPA Query: MYSQL TO @QUERY

Can someone please translate me the following mysql query to @Query?

Mysql-query:

SELECT * 
FROM game JOIN game_teams ON game.id = game_teams.game_id
    JOIN team_members ON game_teams.teams_id = team_members.team_id
WHERE start_date IS NOT NULL AND end_date IS NOT NULL AND members_username = 'user2'

This query retrieves more columns than columns in game table. Maybe I have to select just the columns that are in the game table?

I need something like this:

@Query("???")
Collection<Game> findUserPlayedGames(User user);

Upvotes: 0

Views: 1049

Answers (2)

S. Anushan
S. Anushan

Reputation: 788

If you are passing username as variable then follow below way and you are doing some joining in the query then there many columns so use return type as List<Object[]>

@Query(value ="SELECT * 
FROM game JOIN game_teams ON game.id = 
game_teams.game_id
JOIN team_members ON game_teams.teams_id = 
team_members.team_id
WHERE start_date IS NOT NULL AND end_date IS NOT NULL 
ANDD members_username = :username",   nativeQuery = true)

List<Object[]> 
findUserPlayedGamess(@Param("username)String userName);

Upvotes: 1

editsons
editsons

Reputation: 97

@Query(value = "SELECT * "
        + " FROM game g JOIN game_teams gt ON g.id = gt.game_id "
        + "     JOIN team_members tm ON gt.teams_id = tm.team_id "
        + " WHERE g.start_date IS NOT NULL AND g.end_date IS NOT NULL AND tm.members_username = 'user2'",
        nativeQuery = true)
Collection<Game> findUserPlayedGames(User user);

Upvotes: 0

Related Questions