One to many relationship in MyBatis

Question

I'm trying to use MyBatis to map a one-to-many relationship in my data model. This is based on the following classes:

class Team{
    String mId;
    String mName;
    List<Player> mPlayers;
}

class Player{
    String mId;
    String mName;
}

I would like to write a query that returns a list of matches, each populated with the tags that correspond to that match.

<select id="getTeams" resultType="Team" resultMap="TeamMap">
    SELECT id, name, players.id as player_id, players.name as player_name
    FROM teams
    JOIN players ON teams.id = players.team_id
</select>


<resultMap type="Team" id="TeamMap">
    <id property="mId" column="id"/>
    <result property="mName" column="name"/>

    <collection property="mTags" javaType="List" ofType="Player">
        <id property="player_id" column="mId"/>
        <result property="player_name" column="mName"/>
    </collection>

</resultMap>

But the problem I'm having with this is that each Team object is only populated with a single Player. How can I change this to ensure that each team contains all players that belong to it?

Possible duplicate of [MyBatis ResultMap Collection - Returning only one row](http://stackoverflow.com/questions/21064454/mybatis-resultmap-collection-returning-only-one-row) — indivisible, Oct 16 '15 at 07:28

score 11 · Answer 1 · answered Oct 31 '15 at 10:12

I've tried to follow this question and I've created a One-to-Many relationship in Mybatis using Annotations. Following is my code,

UserMapper.java

@Select("SELECT teamId, name FROM TEAM")
    @Results(value = {
        @Result(property="teamId", column = "teamId"),
        @Result(property="name", column = "name"),
        @Result(property="players", column="teamId", javaType= List.class, many=@Many(select="selectPlayers"))
    })   
public List<Team> getAllTeams();

@Select("SELECT * FROM PLAYER WHERE teamId = #{teamId}")
    @Results(value={
        @Result(property="playerId", column ="playerId" ),
        @Result(property="name", column = "name")
    })
List<Player> selectPlayers(String teamId);

My Team.java:

public class Team {

    private Long teamId;
    private String name;
    private List<Player> players;

    //...getters and setters

}

Player.java:

public class Player {

    private Long playerId;
    private String name;
    private Team team;

    //...getter and setters

}

team.sql

CREATE TABLE `team` (
  `teamId` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`teamId`)
)

player.sql

CREATE TABLE `player` (
  `playerId` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `teamId` bigint(10) DEFAULT NULL,
  PRIMARY KEY (`playerId`),
  KEY `FK_TEAM_ID` (`teamId`),
  CONSTRAINT `FK_TEAM_ID` FOREIGN KEY (`teamId`) REFERENCES `team` (`teamId`)
)

UserServiceImpl.java

@Autowired
private UserMapper userMapper;

...
/* Get the list of teams with players data */
List<Team> teams = userMapper.getAllTeams();
...

Hope this serves useful for future readers.

Hello @Lucky, I have a question. Is it possible that **getAllTeams(...)** method use inner join instead of creating another method **selectPlayers(...)**? I have tried to use inner join instead but the team object is return null player. I believe I did it wrongly. Please help. Thanks. — jujuzi, May 23 '20 at 10:20
@jujuzi Please refer this example for inner join queries. Although not using annotations, this might give you an idea http://dongchuan.github.io/mybatis/2016/04/17/MyBatis-Advanced.html — Lucky, May 28 '20 at 17:43

score 5 · Answer 2 · edited Jan 12 '23 at 15:25

5

Found the solution. All I had to do was change the player_id mapping in the collection from id to result.

edited Jan 12 '23 at 15:25

wittich

2,079
2
27
50

answered Oct 15 '15 at 15:14

spacitron

2,095
7
30
39

One to many relationship in MyBatis

2 Answers2

Linked