Mybatis 3.5 多对多实现

用户（Users）和角色（Roles）是多对多的关系，数据库利用中间表user_role转换为两个多对一，实体类：

@Data
@Accessors(chain = true)
public class User {

    private Integer uid;
    private String username;
    private Integer age;
    //用户有一个角色的集合
    private List<Role> roles = new ArrayList<Role>();;
}

@Data
@Accessors(chain = true)
public class Role {

    private Integer rid;
    private String rname;
    //角色有一个用户的集合
    private List<User> users = new ArrayList<>();

}

接口：

package com.song.mtm.mapper;

import com.song.mtm.pojo.Role;
import com.song.mtm.pojo.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface UserMapper {

    //查询所有用户
    @Select("select * from users")
    @ResultMap("userMap")
    List<User> findUsers();

    //根据用户id查角色集合
//    @Select("SELECT * from roles where rid in(select rid from user_role where uid=#{uid})")
    List<Role> findRolesByUserId(int uid);

    //查询所有角色
    @Select("select * from roles")
    @ResultMap("roleMap")
    List<Role> findRoles();

    //根据角色id获取该角色的用户集合
    List<User> findUsersByRid(int rid);

    @Select("select * from roles where rid=#{rid}")
    Role findRoleById(int rid);

    //插入数据到用户表(并获取主键)
    @Insert("insert into users(username,age) values ( #{username},#{age} )")
    @Options(useGeneratedKeys = true,keyProperty ="uid")
    int addUser(User user);

    //插入数据到中间表
    @Insert("insert into user_role(uid,rid) values ( #{uid},#{rid} )")
    int addMiddle(@Param("uid")int uid,@Param("rid") int rid);

    //删除中间表根据uid
    @Delete("delete from user_role where uid=#{uid}")
    int delMiddle(int uid);

    //根据uid删除用户表
    @Delete("delete from users where uid=#{uid}")
    int delUser(int uid);

    //根据uid查询用户
    @Select("select * from  users where uid=#{uid}")
    User findUserById(int uid);

    @Update("update users set username=#{username}, age=#{age} where uid=#{uid}")
    int update(User user);

}

sql映射文件：

<?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="com.song.mtm.mapper.UserMapper">

      <!--根据用户查询角色-->
      <resultMap id="userMap" type="com.song.mtm.pojo.User">
           <id property="uid" column="uid"/>
          
           <collection property="roles" ofType="com.song.mtm.pojo.Role" column="uid" select="findRolesByUserId">
                <id property="rid" column="rid"></id>
           </collection>
      </resultMap>
    
     <!--根据角色查询用户-->
     <resultMap id="roleMap" type="com.song.mtm.pojo.Role">
           <id property="rid" column="rid"/>
         
           <collection property="users" ofType="com.song.mtm.pojo.User" column="rid" select="findUsersByRid">
                 <id property="uid" column="uid"/>
           </collection>
     </resultMap>


    <select id="findRolesByUserId" resultType="com.song.mtm.pojo.Role">
        SELECT * from roles where rid in(select rid from user_role where uid=#{uid})
    </select>

    <select id="findUsersByRid" resultType="com.song.mtm.pojo.User">
        SELECT * FROM users where uid in(select uid from user_role where rid=#{rid})
    </select>
    
</mapper>

测试：

package com.song.mtm;

import static org.junit.Assert.assertTrue;

import com.song.mtm.mapper.UserMapper;
import com.song.mtm.pojo.Role;
import com.song.mtm.pojo.User;
import com.song.mtm.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Before;
import org.junit.Test;

import java.util.List;

public class AppTest {

     private SqlSession session;
     private UserMapper userMapper;

     @Before
    public void init(){
          session= MybatisUtil.getSession();
          this.userMapper=this.session.getMapper(UserMapper.class);
     }
     @Test
     public void testlistUsers(){
          //根据用户查询角色
         // 获取用户集合
         List<User> users = this.userMapper.findUsers();
         for(User user: users){
             System.out.println("用户名："+user.getUsername());
             System.out.println("角色：");
              for(Role role : user.getRoles()){
                  System.out.println(""+role.getRname());
              }
         }
     }

    @Test
    public void testRoles(){
        //根据角色查询用户
          //获取所有角色
        List<Role> roles = this.userMapper.findRoles();
        for(Role role : roles){
            System.out.println("角色："+role.getRname());
            //获取各个角色的用户集合
            List<User> users = role.getUsers();
            for(User user: users){
                System.out.println("用户名："+user.getUsername());
            }
        }

    }
    @Test
    public void teseAdduser(){
         User user=new User();
         user.setAge(33).setUsername("孙悟空");
         //保存数据到session中
        int m= this.userMapper.addUser(user);
         //获取刚插入的用户的主键，做为中间表的uid
         int uid=user.getUid();
         //获取角色
        Role r1= this.userMapper.findRoleById(1);
        Role r2= this.userMapper.findRoleById(2);
        Role r3= this.userMapper.findRoleById(3);
        Role r4= this.userMapper.findRoleById(4);
         //为用户授予角色
//        user.getRoles().add(r1);
//        user.getRoles().add(r2);
        user.getRoles().add(r3);
        user.getRoles().add(r4);
        //保存数据到session
        for(int i=0;i<user.getRoles().size();i++){
              Role role= user.getRoles().get(i);
             m+=this.userMapper.addMiddle(uid, role.getRid()); // 4,1  4,2  4,3  4,4
        }
        if(m>=2){
            this.session.commit();
        }
    }


    @Test
    public void testDel(){
         int uid=3;
         //删除中间表（保存用户和角色关系）
         int m=this.userMapper.delMiddle(uid);
         //删除用户表
        m+= this.userMapper.delUser(uid);
        System.out.println("共删除了"+m+"条数据。");
        if(m>=2){
            this.session.commit();
        }
    }

    @Test
    public void testUpdate(){
        int uid=5;
        //根据id查询用户
        User user= this.userMapper.findUserById(uid);
        System.out.println("修改前：姓名："+user.getUsername()+"年龄："+user.getAge());
        //进行修改个人信息
        user.setUsername("修改了姓名").setAge(24);
        int m= this.userMapper.update(user);
        //删除原有的角色信息
        m += this.userMapper.delMiddle(uid);
        Role r1= this.userMapper.findRoleById(1);
        Role r2= this.userMapper.findRoleById(2);
        Role r3= this.userMapper.findRoleById(3);
        Role r4= this.userMapper.findRoleById(4);
        //再次获取新角色：授予角色
        user.getRoles().add(r1);
        user.getRoles().add(r3);
        for(int i=0;i<user.getRoles().size();i++){
               Role role = user.getRoles().get(i);
               m+=this.userMapper.addMiddle(uid, role.getRid());
        }
        if(m>=3){
            this.session.commit();
        }

    }
}