9. Mybatis Table Association - Many-to-Many

Many-to-many

mybatis3.0 adds associations and collection s tags specifically for cascading queries on multiple related entity class data, but does not support cascading saves and deletes on multiple related entity class data.Therefore, when designing many-to-many mapping tables for entity classes, it is necessary to establish an associated object class to describe the related entity classes.The CRUD operation will be exemplified below with the many-to-many association mapping between the two entity classes "User" and "Group".

Scenario: A user belongs to more than one group and a group has more than one user.

Environment Setup

1. Creating tables and adding data
The structure and data of the user table:

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;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'Zhang San', '13838009988');
INSERT INTO `user` VALUES ('2', 'Li Si', '13838009988');

Structure and data of user group group group table:

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;

-- ----------------------------
-- Records of group
-- ----------------------------
INSERT INTO `group` VALUES ('1', 'Group-1');
INSERT INTO `group` VALUES ('2', 'Group-2');

Structure and data of user group mapping table 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;

-- ----------------------------
-- Records of user_group
-- ----------------------------
INSERT INTO `user_group` VALUES ('1', '1');
INSERT INTO `user_group` VALUES ('2', '1');
INSERT INTO `user_group` VALUES ('1', '2');

2. Create a JavaBean object for the table
User class creation:

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 + "]";
    }
}

Creation of the Group class:

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+ "]";
    }
}

Creation of the UserGroup class (used to describe the correspondence between User and 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;
    }

}

Writing Configuration File

1. Core Profile 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>
    <!-- Use external profile -->
    <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">
            <!-- Use JDBC transaction management -->
            <transactionManager type="JDBC" />
            <!-- Configure database connection pool -->
            <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 configuration file 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">

    <!-- According to a user group ID,View all users under this group -->
    <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>

    <!-- According to a user ID,View the group for this user-->
    <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 configuration file 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 configuration file 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

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

Tags: Mybatis Mobile xml JDBC

Posted on Fri, 15 May 2020 12:38:54 -0400 by pkellum