MyBatis多表查询(3)

两表联查（多对多）

1、创建一个功能菜单（menu）表、角色（role）表和中间（middle）表

CREATE TABLE menu(
  menuid INT PRIMARY KEY,
  menuname VARCHAR(30)
);
INSERT INTO menu VALUES (1,"商品查询");
INSERT INTO menu VALUES (2,"商品管理");
INSERT INTO menu VALUES (3,"供应商管理");
INSERT INTO menu VALUES (4,"用户管理");

CREATE TABLE role(
    roleid INT,
    rolename VARCHAR(30)
);
INSERT INTO role VALUES (1,"普通用户");
INSERT INTO role VALUES (2,"管理员");
INSERT INTO role VALUES (3,"超级管理员");

CREATE TABLE middle(
   MID INT PRIMARY KEY,
   menuid INT ,
   roleid INT
);
INSERT INTO middle VALUES (1,1,1);
INSERT INTO middle VALUES (2,1,2);
INSERT INTO middle VALUES (3,1,3);
INSERT INTO middle VALUES (4,2,2);
INSERT INTO middle VALUES (5,2,3);
INSERT INTO middle VALUES (6,3,3);
INSERT INTO middle VALUES (7,4,2);
INSERT INTO middle VALUES (8,4,3);

2、创建Menu和Role的实体类

package com.fan.entity;

import java.util.List;

public class Menu {
    private Integer menuId;
    private String menuName;

    private List<Role> roleList;

    public Integer getMenuId() {
        return menuId;
    }

    public void setMenuId(Integer menuId) {
        this.menuId = menuId;
    }

    public String getMenuName() {
        return menuName;
    }

    public void setMenuName(String menuName) {
        this.menuName = menuName;
    }

    public List<Role> getRoleList() {
        return roleList;
    }

    public void setRoleList(List<Role> roleList) {
        this.roleList = roleList;
    }
}

package com.fan.entity;

import java.util.List;

public class Role {
    private Integer roleId;
    private String roleName;

    private List<Menu> menu;
    public Integer getRoleId() {
        return roleId;
    }

    public void setRoleId(Integer roleId) {
        this.roleId = roleId;
    }

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public List<Menu> getMenu() {
        return menu;
    }

    public void setMenu(List<Menu> menu) {
        this.menu = menu;
    }
}

3、创建RoleDao接口

package com.fan.dao;

import com.fan.entity.Menu;

public interface RoleDao {
    //根据menu中的id查询menuname和对应的角色信息
    public Menu findByMenuId(int menuId);
}

4、配置RoleMapper.xml映射文件并将其配置到mybatis-config.xml

<mapper namespace="com.fan.dao.RoleDao">
    <resultMap id="a1" type="Menu">
        <id property="menuId" column="menuid"></id>
        <result property="menuName" column="menuname"></result>
        <collection property="roleList" ofType="Role">
            <id property="roleId" column="roleid"></id>
            <result property="roleName" column="rolename"></result>
        </collection>
    </resultMap>

    <select id="findByMenuId" resultMap="a1">
        select * from menu,role,middle where menu.menuid=middle.menuid
                                         and role.roleid=middle.roleid
                                         and menu.menuid=#{menuId}
    </select>
</mapper>

mybatis-config.xml获取对应的映射文件

 <mapper resource="Mapper/RoleMapper.xml"></mapper>

5、添加测试类

import com.fan.dao.RoleDao;
import com.fan.entity.Menu;
import com.fan.entity.Role;
import com.fan.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class Test4 {
    public static void main(String[] args) {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        RoleDao roleDaoMapper = sqlSession.getMapper(RoleDao.class);
        Menu byMenuId = roleDaoMapper.findByMenuId(1);
        System.out.println(byMenuId.getMenuName());
        System.out.println("可以调用"+byMenuId.getMenuName()+"的角色如下：");
        List<Role> roleList = byMenuId.getRoleList();
        for (Role role:roleList) {
            System.out.println(role.getRoleName());
        }
    }
}

测试结果如下：

测试结果