多对多
mybatis3.0 添加了association和collection标签专门用于对多个相关实体类数据进行级联查询，但仍不支持多个相关实体类数据的级联保存和级联删除操作。因此在进行实体类多对多映射表设计时，需要专门建立一个关联对象类对相关实体类的关联关系进行描述。下文将以“User”和“Group”两个实体类之间的多对多关联映射为例进行CRUD操作。
应用场景：一个用户属于多个组，一个组有多个用户。
环境搭建
1、创建表与添加数据 user表的结构和数据：
CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(64) NOT NULL DEFAULT '',
`mobile` varchar(16) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `user` VALUES ('1', '张三', '13838009988');
INSERT INTO `user` VALUES ('2', '李四', '13838009988');
用户组 group 表的结构和数据：
CREATE TABLE `group` (
`group_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`group_name` varchar(254) NOT NULL DEFAULT '',
PRIMARY KEY (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `group` VALUES ('1', 'Group-1');
INSERT INTO `group` VALUES ('2', 'Group-2');
用户组映射表 user_group 的结构和数据：
CREATE TABLE `user_group` (
`user_id` int(10) unsigned NOT NULL DEFAULT '0',
`group_id` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `user_group` VALUES ('1', '1');
INSERT INTO `user_group` VALUES ('2', '1');
INSERT INTO `user_group` VALUES ('1', '2');
2、创建表对应的 JavaBean 对象 User类的创建：
public class User {
private int id;
private String username;
private String mobile;
private List<Group> groups;
public List<Group>
getGroups() {
return groups;
}
public void setGroups(List<Group> groups) {
this.groups = groups;
}
public int getId() {
return id;
}
public void setId(
int id) {
this.id = id;
}
public String
getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String
getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
@Override
public String
toString() {
return "User [id=" + id +
", username=" + username+
", mobile="+ mobile +
"]";
}
}
Group类的创建：
public class Group {
private int groupId;
private String groupName;
private List<User> users;
public List<User>
getUsers() {
return users;
}
public void setUsers(List<User> users) {
this.users = users;
}
public int getGroupId() {
return groupId;
}
public void setGroupId(
int groupId) {
this.groupId = groupId;
}
public String
getGroupName() {
return groupName;
}
public void setGroupName(String groupName) {
this.groupName = groupName;
}
@Override
public String
toString() {
return "Group [groupId=" + groupId +
",groupName=" + groupName+
"]";
}
}
UserGroup类的创建（用于描述User和Group之间的对应关系）：
public class UserGroup {
private int userId;
private int groupId;
public int getUserId() {
return userId;
}
public void setUserId(
int userId) {
this.userId = userId;
}
public int getGroupId() {
return groupId;
}
public void setGroupId(
int groupId) {
this.groupId = groupId;
}
}
配置文件的编写
1、核心配置文件SqlMapperConfig.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="jdbc.properties"/>
<typeAliases>
<typeAlias alias="User" type="cn.xpu.hcp.bean.User" />
<typeAlias alias="UserGroup" type="cn.xpu.hcp.bean.UserGroup" />
<typeAlias alias="Group" type="cn.xpu.hcp.bean.Group" />
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</dataSource>
</environment>
</environments>
<mappers>
<package name="cn/xpu/hcp/mapper"/>
</mappers>
</configuration>
2、UserGroupMapper配置文件UserGroupMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.xpu.hcp.mapper.UserGroupMapper">
<select id="getUsersByGroupId" resultMap="cn.xpu.hcp.mapper.UserMapper.UserMap" parameterType="int">
SELECT u.*, ug.group_id
FROM user u, user_group ug
WHERE u.id=ug.user_id AND ug.group_id=#{group_id}
</select>
<select id="getGroupsByUserId" resultMap="cn.xpu.hcp.mapper.GroupMapper.GroupMap" parameterType="int">
SELECT g.*, u.user_id
FROM `group` g, user_group u
WHERE g.group_id=u.group_id AND u.user_id=#{user_id}
</select>
</mapper>
3、UserMapper配置文件UserMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.xpu.hcp.mapper.UserMapper">
<resultMap type="User" id="UserMap">
<result property="id" column="id"/>
<result property="name" column="name"/>
<result property="mobile" column="mobile"/>
</resultMap>
<resultMap type="User" id="resultUser" extends="UserMap">
<collection property="groups" column="id" select="cn.xpu.hcp.mapper.UserGroupMapper.getGroupsByUserId"/>
</resultMap>
<select id="getUser" resultMap="resultUser" parameterType="int">
SELECT *
FROM user
WHERE id=#{id}
</select>
</mapper>
4、GroupMapper配置文件GroupMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.xpu.hcp.mapper.GroupMapper">
<resultMap type="Group" id="GroupMap">
<result property="groupId" column="group_id"/>
<result property="groupName" column="group_name"/>
</resultMap>
<resultMap type="Group" id="ResultGroupMap" extends="GroupMap">
<collection property="users" column="group_id"
select="cn.xpu.hcp.mapper.UserGroupMapper.getUsersByGroupId" />
</resultMap>
<select id="getGroup" resultMap="ResultGroupMap" parameterType="int">
SELECT *
FROM `group`
WHERE group_id=#{id}
</select>
</mapper>
测试
@Test
public void Test(){
SqlSession session = sqlSessionFactory.openSession();
GroupMapper mapper = session.getMapper(GroupMapper.class);
Group group = mapper.getGroup(
1);
System.out.println(group+
":");
List<User> users = group.getUsers();
for (User user : users) {
System.out.println(
"\t"+user);
}
UserMapper mapper2 = session.getMapper(UserMapper.class);
User user = mapper2.getUser(
1);
List<Group> groups = user.getGroups();
System.out.println(user+
":");
for (Group g : groups) {
System.out.println(
"\t"+g);
}
session.commit();
session.close();
}
