Spring Data JPA Query: MYSQL TO @QUERY

Question

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);

S. Anushan · Accepted Answer · 2021-06-11 01:49:41Z

1

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);

answered Jun 11, 2021 at 1:49

S. Anushan

7861 gold badge6 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

editsons · Accepted Answer · 2021-06-11 00:02:56Z

0

@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);

answered Jun 11, 2021 at 0:02

editsons

971 silver badge15 bronze badges

Collectives™ on Stack Overflow

Spring Data JPA Query: MYSQL TO @QUERY

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related