前言

我们日常使用MyBatis的开发过程中，对于一对多查询或者多对多查询或多或少都使用过。其中琐碎的知识点和注意事项还是不少的。本博文整理了MyBatis对于复杂映射的开发方式，将分别介绍XML和注解两种方式。详情见正文。

注意：我们下面的示例为了演示方便均使用select * 的方式做的查询，我们在项目开发过程中尽量避免此种方式。要遵循需要哪个字段查询哪个字段的原则。

一对一查询

一对一的查询比较常见，比如说一个订单就对应一个用户，我们在查询订单表的时候，把关联的用户一起查询出来就是最常见的一对一查询。表结构如下：

再来创建我们表对应的实体：

public class Order {
    private int id;
    private Date ordertime;
    private double total;
    //代表当前订单从属于哪一个客户
    private User user;
}

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
}

XML查询

创建Mapper接口和配置文件：

public interface OrderMapper {
    List<Order> findAll();
}

<resultMap id="orderMap" type="com.rubin.domain.Order">
    <result property="id" column="id"></result>
    <result property="ordertime" column="ordertime"></result>
    <result property="total" column="total"></result>
    <association property="user" javaType="com.rubin.domain.User">
        <result column="uid" property="id"></result>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
    </association>
</resultMap>
<select id="findAll" resultMap="orderMap">
    select * from orders o,user u where o.uid=u.id
</select>

注解查询

创建Mapper接口:

public interface OrderMapper {
    @Select("select * from orders")
    @Results({
      @Result(id=true,property = "id", column = "id"),
      @Result(property = "ordertime", column = "ordertime"),
      @Result(property = "total", column = "total"),
      @Result(property = "user", column = "uid", javaType = User.class, one = @One(select = "com.rubin.mapper.UserMapper.findById"))
    })
    List<Order> findAll();
}

public interface UserMapper {
    @Select("select * from user where id=#{id}")
    User findById(int id);
}

一对多查询

一对多的场景也很常见，上述示例反过来就是一个典型的一对多的查询。也就是说，我们在查询用户详情信息的时候，把该用户的所有订单一起查询出来。表关系如下：

创建映射实体类：

public class Order {
    private int id;
    private Date ordertime;
    private double total;
}

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    //代表当前用户具备哪些订单
    private List<Order> orderList;
}

XML查询

创建Mapper接口和配置文件如下：

public interface UserMapper {
    List<User> findAll();
}

<mapper namespace="com.rubin.mapper.UserMapper">
    <resultMap id="userMap" type="com.rubin.domain.User">
        <result column="id" property="id"></result>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
        <collection property="orderList" ofType="com.rubin.domain.Order">
            <result column="oid" property="id"></result>
            <result column="ordertime" property="ordertime"></result>
            <result column="total" property="total"></result>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="userMap">
       select *,o.id oid from user u left join orders o on u.id=o.uid
    </select>
</mapper>

注解查询

创建Mapper接口如下：

public interface UserMapper {
    @Select("select * from user")
    @Results({
        @Result(id = true, property = "id", column = "id"),
        @Result(property = "username", column = "username"),
        @Result(property = "password", column = "password"),
        @Result(property = "birthday", column = "birthday"),
        @Result(property = "orderList", column = "id", javaType = List.class, many = @Many(select = "com.rubin.mapper.OrderMapper.findByUid"))
    })
    List<User> findAllUserAndOrder();
}

public interface OrderMapper {
    @Select("select * from orders where uid=#{uid}")
    List<Order> findByUid(int uid);
}

多对多查询

多对多的场景我们最常见的就是权限管理。一个用户对应多个角色，而每个角色又对应多个用户。用户权限的表结构如下：

根据上表，我们设计的实体如下：

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    //代表当前用户具备哪些角色
    private List<Role> roleList;
}

public class Role {
    private int id;
    private String rolename;
}

Mapper查询

我们定义Mapper接口以及配置文件如下：

List<User> findAllUserAndRole();

<resultMap id="userRoleMap" type="com.lagou.domain.User">
    <result column="id" property="id"></result>
    <result column="username" property="username"></result>
    <result column="password" property="password"></result>
    <result column="birthday" property="birthday"></result>
    <collection property="roleList" ofType="com.lagou.domain.Role">
        <result column="rid" property="id"></result>
        <result column="rolename" property="rolename"></result>
    </collection>
</resultMap>
<select id="findAllUserAndRole" resultMap="userRoleMap">
   select 
      u.*,r.*,
      r.id rid
   from 
      user u 
   left join user_role ur on u.id=ur.user_id 
   inner join role r on ur.role_id=r.id
</select>

注解查询

我们定义Mapper接口如下：

public interface UserMapper {
    @Select("select * from user")
    @Results({
        @Result(id = true,property = "id", column = "id"),
        @Result(property = "username", column = "username"),
        @Result(property = "password", column = "password"),
        @Result(property = "birthday", column = "birthday"),
        @Result(property = "roleList", column = "id", javaType = List.class, many = @Many(select = "com.rubin.mapper.RoleMapper.findByUid"))
    })
    List<User> findAllUserAndRole();
}

public interface RoleMapper {
    @Select("select * from role r,user_role ur where r.id=ur.role_id and ur.user_id=#{uid}")
    List<Role> findByUid(int uid);
}