How to implement ManyToMany relationship in MyBatis?

Question

I'm recently learning to develop Web Applications using Java frameworks.

The application is just a basic register/login/logout system. Guests can register an account and login as a common user. Besides common user, there're two other roles super admin and admin, with their respective permissions/previleges.

An Account could own several Roles simultaneously, and of course a Role can be applied to many Accounts. And that forms a many-to-many(or n-to-to) relationship between Account and Role.

I've maintained three tables in my database:

• First, the ACCOUNT table, which stores information of an account except the account's roles;
• Second, the ROLE table, which stores information about how many Roles there are. Currently, there are only three, "Superuser", "Admin" and "Common User";
• Third one, the ACCOUNT_ROLE table, which stores the mapping between ACCOUNT and ROLE.

I can implement this using Hibernate's @ManyToMany and @JoinTable annotations, if I do so, the third table can be automatically managed by Hibernate. For example, when I create an Account of Common User, hibernate will help me add an entry which contains the AccountId and RoleId into the third table; and when I delete this Account, hibernate will help me drop the corresponding entry in the third table.

And, the question is How can I implement this many-to-many relationship in MyBatis?

I've read some materials introducing how to set up one-to-one or one-to-many relationship in MyBatis. But it seems that MyBatis doesn't have the many-to-many relationship like Hibernate. So what am I gonna do?

My raw idea is Manage The Third Table Manually, which means:

• I have to add entries to the third table manually, when I create a new account
• I have to remove/update corresponding entries manually, when I delete/update an account

Since I'm not really that familiar with MyBatis, this way is the only idea I can come up with now.

I hope someone could give me some tips or ideas or concrete samples about rounding off this problem.

PLUS: I've the demo application's code on github, in case you need to see how this demo goes. Many thanks!

Answer 1

User story

First, MyBatis generate model, mapper, and mapper.xml for you.

Now, if you have two models -- User & Role
What's the relationship between User and Role?

A user have many roles, and a role can be authorized to many users. So they are many-to-many relationship.

How to implement?

• add List roles to User model

    private List<Role> roles = new ArrayList<>();
  

• add List users to Role model

    private List<User> users = new ArrayList<>();
  

• make a UserRoleMapper interface to add some methods

    package com.jim.mapper;
  
    import com.jim.model.Role;
    import com.jim.model.User;
  
    import java.util.List;
  
    public interface UserRoleMapper {
        int deleteRole(long userId, long roleId);
        int addRole(long userId, long roleId);
        List<Role> getRoles(long userId);
        List<User> getUsers(long roleId);
    }
  

• add a xml to mapping SQL & result.

    <resultMap id="UserResultMap" type="com.jim.model.Role">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <collection javaType="ArrayList" property="users" resultMap="RoleResultMap" />
    </resultMap>
  
    <resultMap id="RoleResultMap" type="com.jim.model.User">
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="username" jdbcType="VARCHAR" property="username"/>
        <collection javaType="ArrayList" property="roles" resultMap="UserResultMap" />
    </resultMap>
  
    <insert id="addRole">
        INSERT INTO user_role (userId, roleId)
        VALUES (#{userId, jdbcType=BIGINT}, #{roleId, jdbcType=BIGINT});
    </insert>
    <delete id="deleteRole" >
        DELETE FROM user_role
        WHERE 1=1
        AND userId=#{userId}
        AND roleId=#{roleId}
    </delete>
  
    <select id="getRoles" resultMap="UserResultMap">
        SELECT
        r.id AS id, r.name AS name
        FROM users AS  u
        LEFT JOIN user_role AS ur ON u.id = ur.userId
        LEFT JOIN roles AS r ON r.id = ur.roleId
        WHERE 1=1
          AND u.id = #{id,jdbcType=BIGINT}
          AND r.isActive = 1
          AND u.isActive = 1
    </select>
  
    <select id="getUsers" resultMap="RoleResultMap">
        SELECT
        u.id AS id, u.username AS username
        FROM roles AS  r
        LEFT JOIN user_role AS ur ON r.id = ur.roleId
        LEFT JOIN users AS u ON u.id = ur.userId
        WHERE 1=1
        AND r.id = #{id,jdbcType=BIGINT}
        AND r.isActive = 1
        AND u.isActive = 1
    </select>