Mybatis多表查询

• Mybatis多表查询 一对一关系
• Mybatis多表查询 一对多关系
• Mybatis多表查询 多对多关系
• 前述
  • 基础知识是mysql的多表查询，要熟练。
  • 多表查询就是一次查询涉及多个表；inner join left join right
  • 结果有一对一关系，一对多关系，多对多关系
  • 我们在封装数据时，定义好的类无法直接封装（类字段，集合字段等），需要通过配置封装

实操

• 一对一关系，1个order 对应 1个user , 封装user 类中order
  • select * from orders o,user u where o.uid=u.id
  • ```java <?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">

<resultMap id="orderMap" type="order">
    <!--手动指定字段和实体属性的关系
    column:数据表的字段名称
    property:Java 类（实体）中的字段名称-->
    <id column="id" property="id"></id><!--id 主健标识-->
    <result column="ordertime" property="ordertime"></result>
    <result column="total" property="total"/>
    <result column="uid" property="user.id"/>
    <result column="username" property="user.username"/>
    <result column="password" property="user.password"/>
    <result column="birthday" property="user.birthday"/>
</resultMap>
<select id="findAll" resultMap="orderMap">
select * from orders o,user u where o.uid=u.id
</select>

- 一对多关系，1个user 对应 多个 order, 封装 user 类中的 List<order>
   - select * ,o.id oid from user u left join orders o on u.id = o.uid
   - ![image.png](https://cdn.nlark.com/yuque/0/2021/png/22442214/1635261146543-583e5293-845b-49cd-bcfe-b7cdf146960d.png#clientId=u70650cf7-4f69-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=52&id=u407318c6&margin=%5Bobject%20Object%5D&name=image.png&originHeight=103&originWidth=584&originalType=binary&ratio=1&rotation=0&showTitle=false&size=9354&status=done&style=none&taskId=u508d4845-8855-4fe2-bb20-cb2af822771&title=&width=292)

- 多对多关系，1个user 可能有 多个 role, 1 个role可能有多个 user  封装 user 类中的 List<role>
   - select u.*,r.*,r.id rid from user u  left join sys_user_role ur  on u.id = ur.userid  left join sys_role r on ur.roleid = r.id
   - ![image.png](https://cdn.nlark.com/yuque/0/2021/png/22442214/1635261228261-0ee9d40a-18c4-4be9-923a-a9a27085098b.png#clientId=u70650cf7-4f69-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=87&id=uec1938c2&margin=%5Bobject%20Object%5D&name=image.png&originHeight=174&originWidth=667&originalType=binary&ratio=1&rotation=0&showTitle=false&size=14983&status=done&style=none&taskId=u9f65d11f-c4ab-487d-a8ec-8d15cbb9d10&title=&width=333.5)
```java
<?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.jquan.mapper.UserMapper">

    <resultMap id="userMap" type="user"> <!--首先是user  u表left join o表-->
       <id column="uid" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="birthday" property="birthday"/>

        <!-- 1对多的关系： 配置集合信息
            property:集合名称,和User类中的orderList字段名称对应
            ofType: 当前集合中的数据类型
        -->
        <collection property="orderList" ofType="order">
            <!--封装order数据-->
            <id column="oid" property="id"/>
            <result column="ordertime" property="ordertime"/>
            <result column="total" property="total"/>
        </collection>

    </resultMap>

    <select id="findAll" resultMap="userMap">
        select * ,o.id oid from user u left join orders o on u.id = o.uid
    </select>


    <resultMap id="userRoleMap" type="user">
        <id column="id" property="id"></id>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="birthday" property="birthday"/>
        <collection property="roleList" ofType="role">
            <id column="rid" property="id"/>
            <result column="rolename" property="roleName"/>
            <result column="roleDesc" property="roleDesc"/>
        </collection>

    </resultMap>
    <select id="findUserAndRoleAll" resultMap="userRoleMap">
        select u.*,r.*,r.id rid from user u  left join sys_user_role ur  on u.id = ur.userid  left join sys_role r on ur.roleid = r.id
    </select>

</mapper>