关于Mybatis多表查询之多对多查询

概念

首先明确一个概念
多对多查询 其实就是 多个 一对多查询

比如 一个人可以拥有多个角色，而一个角色也可以赋给多个人

那么 从单一个体(比如一个人) 来说，其实就是一次一对多查询罢了

因为有多个个体 相当于循环多次我们的一对多查询的过程罢了

前期准备

我们准备三张表

一张用户表 一张角色表 一张中间表

角色表:

用户表:

中间表:

建立对应的类

public class User implements Serializable {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    //这个是用于 一对多查询的
    private List<Account> accountList;
    private List<Role> roleList;

public class Role implements Serializable {
    //注意 这里的属性名与数据库的列名不一致
    private Integer roleID;
    private String roleName;
    private String roleDesc;
    //需要加上多对多的关系映射 一个角色可以赋予多个用户
    private List<User> userList;

实现多对多查询

方式一、使用xml配置文件

关于用户 我们首先在UserDao下添加一个方法

 /**
     * 查询一个用户对应的多个角色
     * @return
     */
    List<User> selectMulTable();

并在其xml文件中加上

<resultMap id="userRoleMapper" type="com.tubai.domain.User">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>
        <result property="birthday" column="birthday"></result>
        <collection property="roleList" ofType="com.tubai.domain.Role">
            <id property="roleID" column="rid"></id>
            <result property="roleName" column="role_name"></result>
            <result property="roleDesc" column="role_desc"></result>
        </collection>
    </resultMap>
    <select id="selectMulTable" resultMap="userRoleMapper">
        select	u.*,r.id rid,r.ROLE_DESC,r.ROLE_NAME from user u
        LEFT JOIN user_role ur ON u.ID=ur.uID LEFT JOIN
        role r ON r.id=ur.rID;
    </select>

同理 关于角色 我们也同样加上这两个

/**
     * 多对多查询
     * @return
     */
    List<Role> mulTableQuery();

<resultMap id="roleUserMapper" type="com.tubai.domain.Role">
        <id property="roleID" column="rid"></id>
        <result property="roleName" column="role_name"></result>
        <result property="roleDesc" column="role_desc"></result>
        <collection property="userList" ofType="com.tubai.domain.User">
            <id property="id" column="id"></id>
            <result property="username" column="username"></result>
            <result property="sex" column="sex"></result>
            <result property="address" column="address"></result>
            <result property="birthday" column="birthday"></result>
        </collection>
    </resultMap>
    <select id="mulTableQuery" resultMap="roleUserMapper">
        <!--在写长sql语句的时候 注意要多填补空格(在行前行末) 不然拼接的时候可能会报错-->
        select	u.*,r.id rid,r.ROLE_DESC,r.ROLE_NAME from role r
        LEFT JOIN user_role ur ON r.ID=ur.RID LEFT JOIN
        `user` u ON u.id=ur.uID;
    </select>

然后分别执行测试类即可

@Test
    public void mulTableQuery() throws Exception{
        List<Role> roles = roleDao.mulTableQuery();
        for (Role role : roles) {
            System.out.println(role);
            System.out.println("该角色被赋予的用户");
            System.out.println(role.getUserList());
            System.out.println();
        }
    }

@Test
    public void testSelectMulTable(){
        List<User> userList = userDao.selectMulTable();
        for (User user : userList) {
            System.out.println(user);
            System.out.println("以下为该用户对应的角色");
            System.out.println(user.getRoleList());
            System.out.println();
        }
    }

当然 也可以像我们延迟加载那篇博客中使用 标签中的select属性去调用方法来辅助我们完成查询

方式二、使用注解

数据库与相应类都和上面一致
因为我们使用注解开发 因此只需要dao类和测试类

package com.tubai.dao;

import com.tubai.domain.Role;
import com.tubai.domain.User1;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;

import java.util.List;

/**
 * 多对多查询 一个用户可以拥有多个角色 一个角色亦可以赋给多个用户
 */
public interface MultiTableQueryDao {
    @Select("select * from role")
    @Results(id="userRoleMapper",value = {
            @Result(property = "roleID",column = "id",id = true),
            @Result(property = "roleName",column = "role_name"),
            @Result(property = "roleDesc",column = "role_desc"),
            //根据角色ID去查找用户ID 再通过用户ID查询用户信息
            @Result(property = "userList",column = "id",
                    many = @Many(fetchType = FetchType.LAZY,
                            select = "com.tubai.dao.MultiTableQueryDao.selectUidByRid"))
    })
    List<Role> selectRoleAll();

    @Select("select * from user")
    @Results(id="userMapper",value = {
            @Result(id=true,column = "id",property = "id"),
            @Result(column = "username",property = "username"),
            @Result(column = "birthday",property = "birthday"),
            @Result(column = "sex",property = "sex"),
            @Result(column = "address",property = "address"),
            @Result(column = "id",property = "accountList",many = @Many(fetchType = FetchType.LAZY,
                    select = "com.tubai.dao.AccountDao.selectAccountByUid")),
            @Result(column = "id",property = "roleList",many = @Many(
                    fetchType = FetchType.LAZY,select = "com.tubai.dao.MultiTableQueryDao.selectRidByUid"
            ))

    })
    List<User1> selectAllUser1();

    @Select("select * from user where id in(select uid from user_role where rid = #{Rid})")
    List<User1> selectUidByRid(Integer Rid);

    @Select("select * from role where id in(select rid from user_role where uid = #{Uid})")
    @ResultMap("userRoleMapper")
    List<Role> selectRidByUid(Integer Uid);

}