15.使用注解实现复杂映射查询数据

1.使用注解实现复杂映射开发

之前我们在映射文件中通过配置、、来实现复杂关系映射。
使用注解开发后，我们可以使用 @Results、@Result，@One、@Many 注解组合完成复杂关系的配置。
下图为注解重点，请记住：

1.1一对一查询—两表查询

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

一对一查询语句：

//嵌套查询
SELECT * FROM orders;
SELECT * FROM `user` WHERE id = #{订单的uid};

1.1.1代码实现

（1）OrderMapper接口

@One代替了标签，使用时需要Result(one = @One(select = “namespace.id”) 表示。
javaType:表示返回的对象类型。

 /**
     * 一对一查询：
     * 查询订单所对应的用户
     */
    @Select(" select * from orders ")
    @Results({
            //id = true 表示该字段为主键 类似于 <id property="id" column="id"></id>
            @Result( id = true, property ="id", column = "id" ),
            @Result(  property ="id", column = "id" ),
            @Result(  property ="orderTime", column = "orderTime" ),
            @Result(  property ="total", column = "total" ),
            @Result(  property ="uid", column = "uid" ),
                //Result(one = @One(select = "namespace.id") 代替了<assocation>标签
                @Result(one = @One(select = "com.weihong.mapper.UserMapper.findUserById") ,property = "user" , javaType = com.weihong.domain.User.class , column = "uid")
    })
    List<Orders> findAllWithUser();

上面语句等价于使用xml映射文件的sql，如下代码所示：

   <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>
        <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>

（2）UserMapper接口

   /**
     * 根据id找用户
     * @param id
     * @return
     */
    @Select(" select * from `user` where id = #{userId} ")
   User findUserById(@Param("userId") Integer id);

（3）测试代码

    @Test
    public void test2(){
        OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
        List<Orders> orders = ordersMapper.findAllWithUser();
        for (Orders order : orders) {
            System.out.println(order);
        }
    }

（4）测试结果

1.2一对多查询—》两表查询

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

SELECT * FROM `user`;
SELECT * FROM orders where uid = #{用户id};

1.2.1代码实现

（1）UserMapper接口

特别注意：

此时查询为一对多查询，使用collection来关联查询，对应注解为@Many属性。
一个用户可能有多个订单，所以返回类型为List类型。javaType = List.class（这是与一对一查询不同的）

 /**
     * 查找用户所对应的订单：一对多
     * 一个用户对应多个订单
     * @return
     */
    @Select("select * from `user` ")
    @Results({
            @Result(id = true, property = "id" , column="id"),
            @Result(property = "birthday" , column = "birthday"),
            @Result(property = "sex" , column = "sex"),
            @Result(property = "address" , column = "address"),
                @Result(column = "id" ,many = @Many(select = "com.weihong.mapper.OrdersMapper.findByUid"), property = "ordersList" ,  javaType = List.class )
    })
    List<User> findAllWithOrder();

(2)OrderMapper接口

 /**
     * 根据用户id查找订单信息
     * @param uid
     * @return
     */
    @Select("select * from orders where uid = #{uid} ")
    List<Orders> findByUid(@Param("uid") Integer uid);

（3）测试类

 @Test
    public void test3(){
        UserMapper usermapper = sqlSession.getMapper(UserMapper.class);
        List<User> allUser = usermapper.findAllWithOrder();
        for (User user : allUser) {
            System.out.println(user);
        }
    }

（4）测试结果

1.3多对多查询

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

多对多查询语句

select * from sys_role r left join sys_user_role ur on r.id = ur.roleid where ur.userid in (
select id from `user`
)

1.3代码实现

（1）UserMapper接口

    /**
     * 查询用户所对应的角色信息：
     * 多对多查询：
     * 借用到关联表
     * 先查询用户表的用户id，后和角色信息表及中间表做关联查询
     */

    @Select("select * from `user` ")
    @Results({
            @Result(id = true, property = "id", column = "id"),
            @Result(property = "birthday", column = "birthday"),
            @Result(property = "sex", column = "sex"),
            @Result(property = "address", column = "address"),
              @Result(column = "id" , many = @Many ( select = "com.weihong.mapper.RoleMapper.getUserRoleInfo" , fetchType = FetchType.EAGER ) , property = "roleList" ,javaType = List.class)
    })
    List<User> findUserofRole();

（2）RoleMapper接口

    @Select(" select * from sys_role r left join sys_user_role ur on r.id = ur.roleid where ur.userid = #{id}")
    List<Role> getUserRoleInfo(Integer id);

（3）测试类

    @Test
    public void test4(){
        UserMapper usermapper = sqlSession.getMapper(UserMapper.class);
        List<User> allUser = usermapper.findUserofRole();
        for (User user : allUser) {
            System.out.println(user);
        }
    }