One to many relationship in MyBatis
Solution 1
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.
Solution 2
Found the solution. All I had to do was change the player_id mapping in the collection from "id" to "result".
spacitron
Updated on August 14, 2022Comments
-
spacitron over 1 year
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?
-
jujuzi almost 4 yearsHello @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.
-
Lucky almost 4 years@jujuzi Please refer this example for inner join queries. Although not using annotations, this might give you an idea dongchuan.github.io/mybatis/2016/04/17/MyBatis-Advanced.html