Mybatis的多表操作之多对多查询与练习

目录

 多对多查询

多对多查询模型

 练习册多对多表查询

将orders、role、user三者联系起来

---

 多对多查询

多对多查询模型

用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用
多对多查询的需求：查询用户同时查询出该用户的所有角色

 数据库中两张新表

 UserMapper接口下

 
 
public interface UserMapper {
    public List findUserAndRoleAll();
 
}

 pojo下的role类

package com_mybatis_table.pojo;
 
public class Role {
 
    private int id;
    private String roleName;
    private String roleDesc;
 //外加get和set方法和toString方法
}

pojo下的user类

package com_mybatis_table.pojo;
 
import java.util.Date;
import java.util.List;
 
public class User {
 
    private int id;
    private String username;
    private String password;
    private Date birthday;
 
    //描述的是当前用户存在哪些订单
    private List orderList;
 
    //描述的是当前用户具备哪些角色
    private List roleList;
 
 //外加getset方法和toString方法
}

UserMapper.xml文件下（配置sql的文件）

"1.0" encoding="UTF-8" ?>
mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com_mybatis_table.mapper.UserMapper">
 
    <resultMap id="userRoleMap" type="user">
        <id column="userId" property="id">id>
        <result column="username" property="username">result>
        <result column="password" property="password">result>
        <result column="birthday" property="birthday">result>
 
        <collection property="roleList"  ofType="role">
            <id column="roleId" property="id">id>
            <result column="roleName" property="roleName">result>
            <result column="roleDesc" property="roleDesc">result>
        collection>
    resultMap>
 
    
    <select id="findUserAndRoleAll" resultMap="userRoleMap">
        select * from user u,sys_user_role sr,sys_role r
                where u.id=sr.roleId and sr.roleId=r.id
    select>
 
mapper>

sqlMapConfig.xml中加入别名和加载映射文件

    
    <typeAliases>
        <typeAlias type="com_mybatis_table.pojo.Role" alias="role">typeAlias>
   typeAliases>
   
  
<mappers>
         <mapper resource="com_mybatis/mapper/UserMapper.xml">mapper>
    mappers>

运行结果

 练习册多对多表查询

将orders、role、user三者联系起来

即一个用户，显示他们角色信息，订单信息，
一个用户有多个角色，一个订单属于一个用户，一个用户有多个订单

mapper包下的OrderUserRoleMapper接口下

 
 
import java.util.List;
 
public interface OrderUserRoleMapper {
    public List findUserAndRoleAndOderAll();
}

pojo包下的三个实体类基本不变，user类中需要加入订信息和角色信息

package com_mybatis_table.pojo;
 
import java.util.Date;
import java.util.List;
 
public class User {
 
    private int id;
    private String username;
    private String password;
    private Date birthday;
 
    //描述的是当前用户存在哪些订单
    private List orderList;
 
    //描述的是当前用户具备哪些角色
    private List roleList;
 
}

Order_User_RoleMapper.xml下

"1.0" encoding="UTF-8" ?>
mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com_mybatis_table.mapper.OrderUserRoleMapper">
 
    
    
    <resultMap id="userRoleMap" type="user">
        
        <id column="userId" property="id">id>
        <result column="username" property="username">result>
        <result column="password" property="password">result>
        <result column="birthday" property="birthday">result>
 
 
        
 
        <collection property="roleList"  ofType="role">
            <id column="roleId" property="id">id>
            <result column="roleName" property="roleName">result>
            <result column="roleDesc" property="roleDesc">result>
        collection>
 
 
        <collection property="orderList"  ofType="order">
 
            <id column="oid" property="id">id>
            <result column="ordertime" property="ordertime">result>
            <result column="total" property="total">result>
        collection>
    resultMap>
 
 
    <select id="findUserAndRoleAndOderAll" resultMap="userRoleMap">
         select *,o.id  oid from user u,sys_user_role sr,sys_role r,orders o
                where u.id=sr.roleId and sr.roleId=r.id and o.uid=u.id
    select>
 
 
 
 
 
mapper>

sqlMapConfig.xml下加入此mapper的Order_User_RoleMapper.xml文件

测试类下

    @Test
    public void findUserAndRoleTest() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
 
        OrderUserRoleMapper mapper = sqlSession.getMapper(OrderUserRoleMapper.class);
        List userAndRoleAndOderAllList = mapper.findUserAndRoleAndOderAll();
        for (User user : userAndRoleAndOderAllList) {
            System.out.println(user);
        }
 
 
        sqlSession.close();
    }

运行结果