11.MyBatis的嵌套查询

11.MyBatis的嵌套查询

1.什么事嵌套查询

嵌套查询就是将原来多表查询中的联合查询语句拆成单个表的查询，再使用mybatis的语法嵌套在一
起。

• 例子：

  * 需求：查询一个订单，与此同时查询出该订单所属的用户
  1. 联合查询
  SELECT * FROM orders o LEFT JOIN USER u ON o.`uid`=u.`id`;
  2. 嵌套查询
  2.1 先查询订单
  SELECT * FROM orders
  2.2 再根据订单uid外键，查询用户
  SELECT * FROM `user` WHERE id = #{根据订单查询的uid}
  2.3 最后使用mybatis，将以上二步嵌套起来
  

2.一对一嵌套查询

• 需求：查询一个订单，与此同时查询出该订单所属的用户

• sql语句

  -- 先查询订单
  SELECT * FROM orders;
  -- 再根据订单uid外键，查询用户
  SELECT * FROM `user` WHERE id = #{订单的uid}
  

2.1代码实现

（1）OrderMapper接口

    //  查询用户订单
    List<Orders> findUserOrders();

（2）OrderMapper.xml映射

• 一对一查询

  <resultMap id="userOrdersMapper" type="orders">
        <id property="id" column="id"></id>
        <result property="orderTime" column="orderTime"></result>
        <result property="total" column="total"></result>
        <result property="uid" column="uid"></result>
      <!--根据订单中uid外键，查询用户表
      select：参数为userMapper的namespace.id 
      column:为传递的参数 
      --> 
      <association property="user" javaType="com.lagou.domain.User" select="com.lagou.mapper.UserMapper.findUserOrderByNested" column="uid">
        </association>
    </resultMap>
    <select id="findUserOrders" resultMap="userOrdersMapper">
            select * from orders
    </select>

（3）userMapper接口

//    嵌套查询：查询订单拥有者
    List<User> findUserOrderByNested(@Param("uid") Integer uid);

（4）UserMapper.xml映射

<resultMap id="getUserInfo" type="user">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="birthday" column="birthday"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>
    </resultMap>
    <select id="findUserOrderByNested" resultMap="getUserInfo" parameterType="Integer">
        select * from `user` where id = #{uid}
    </select>

(5)测试方法

 @Test
    public void test5() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
        List<Orders> userOrders = ordersMapper.findUserOrders();
        for (Orders orders : userOrders){
            System.out.println(orders);
        }
        sqlSession.close();
    }

（6）测试结果

2.2流程解析

3.一对多嵌套查询

• 需求：查询所有用户，与此同时查询出该用户具有的订单

• 一对多查询语句

  -- 先查询用户
  SELECT * FROM `user`;
  -- 再根据用户id主键，查询订单列表
  SELECT * FROM orders where uid = #{用户id};
  

3.1代码实现

• 思想：
  1. 先找到所有用户信息（即先在用户操作类xml查询出用户信息）
  2. 得到用户id后去查询订单表信息（关联查询）
  3. 根据uid=id(用户id)查询出结果

（1）userMapper接口

//嵌套查询： 查询用户的订单信息（一对多）
    List<User> findUserOrder2();

（2）UserMapper.xml映射

<!--    嵌套查询-->
    <resultMap id="getUserInfoWithNested" type="user">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="birthday" column="birthday"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>
        <collection property="ordersList" ofType="com.lagou.domain.Orders" select="com.lagou.mapper.OrdersMapper.findUserAllOrder" column="id"></collection>
    </resultMap>
    <select id="findUserOrder2" resultMap="getUserInfoWithNested">
        select * from user
    </select>

（3）OrderMapper接口

    //嵌套查询：查询用户的订单
    List<Orders> findUserAllOrder(@Param("id") Integer id);

（4）OrderMapper.xml映射

<!-- 嵌套查询   -->
    <resultMap id="OrdersBaseInfo" type="orders">
        <id property="id" column="id"></id>
        <result property="orderTime" column="orderTime"></result>
        <result property="total" column="total"></result>
        <result property="uid" column="uid"></result>
    </resultMap>
    <select id="findUserAllOrder" parameterType="int" resultMap="OrdersBaseInfo">
        select * from orders where uid = #{id}
    </select>

(5)测试方法

   /**
     * 嵌套查询：一对多
     * 查询用户对应的订单信息
     * @throws IOException
     */
    @Test
    public void test6() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userOrders = userMapper.findUserOrder2();
        for (User userOrder : userOrders){
            System.out.println(userOrder);
        }
        sqlSession.close();
    }

（6）测试结果

4.多对多嵌套查询

需求：查询用户，同时查询出该用户的所有角色

• 查询语句

  -- 先查询用户
  SELECT * FROM `user`;
  -- 再根据用户id主键，查询角色列表
  SELECT * FROM role r INNER JOIN user_role ur ON r.`id` = ur.`rid`
  WHERE ur.`uid` = #{用户id};
  

4.1代码实现

（1）userMapper接口

/**
 * 嵌套查询：查询用户对应的角色信息
 */
    List<User> findAllWithRole();

（2）UserMapper.xml映射

 <!--    嵌套查询:查询用户对应的角色信息-->
    <resultMap id="getUserRoleWithNested" type="user">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="birthday" column="birthday"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>
        <collection property="roleList" ofType="com.lagou.domain.Role" select="com.lagou.mapper.RoleMapper.getRolelist" column="id"></collection>
    </resultMap>
    <select id="findAllWithRole" resultMap="getUserRoleWithNested">
        select * from `user`
    </select>

（3）RoleMapper接口

public interface RoleMapper {
    List<Role> getRolelist(Integer id);
}

（4）RoleMapper.xml映射文件

<mapper namespace="com.lagou.mapper.RoleMapper">

    <select id="getRolelist" resultType="role" parameterType="Integer">
        SELECT * FROM sys_role r INNER JOIN sys_user_role ur ON r.id = ur.roleid WHERE ur.userid= #{id}
    </select>
</mapper>

（5）测试类

 /**
     * 嵌套查询：多对多
     * 查询用户对应的角色
     * @throws IOException
     */
    @Test
    public void test7() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userOrders = userMapper.findAllWithRole();
        for (User userOrder : userOrders){
            System.out.println(userOrder);
        }
        sqlSession.close();
    }

（6）测试结果

5.小结

• 一对一配置：使用+做配置，通过column条件，执行select查询
• 一对多配置：使用+做配置，通过column条件，执行select查询
• 多对多配置：使用+做配置，通过column条件，执行select查询
  优点：简化多表查询操作
  缺点：执行多次sql语句，浪费数据库性能