5-Mybatis复杂映射开发

5.1 一对一查询

5.1.1 一对一查询的模型

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户

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

5.1.2一对一查询的语句

对应的sql语句：select * from orders o,user u where o.uid=u.id;

查询的结果如下：

5.1.3 创建Order和User实体

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

5.1.4 创建OrderMapper接口

public interface IOrderMapper {  
  
 //查询订单以及订单所属的用户信息  
 public List<Order> findOrderAndUser();  
  
}

5.1.5 配置OrderMapper.xml

<resultMap id="orderMap" type="com.lemon.pojo.Order">
       <result property="id" column="id"></result>
       <result property="orderTime" column="orderTime"></result>
       <result property="total" column="total"></result>

       <association property="user" javaType="com.lemon.pojo.User">
           <result property="id" column="uid"></result>
           <result property="username" column="username"></result>
           <result property="password" column="password"></result>
           <result property="birthday" column="birthday"></result>
       </association>
   </resultMap>


   <!--resultMap：手动来配置实体属性与表字段的映射关系-->
   <select id="findOrderAndUser" resultMap="orderMap">
       select * from orders o,user u where o.uid = u.id
   </select>

5.1.6 测试结果

5.2 一对多查询

5.2.1 一对多查询的模型

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户

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

5.2.2 一对多查询的语句

对应的sql语句：select *, o.id oid from user u left join orders o on u.id=o.uid;

查询的结果如下：

5.2.3 修改User实体

public class User {

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

5.2.4 创建UserMapper接口

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

5.2.5 配置UserMapper.xml

<mapper namespace="com.lemon.mapper.IUserMapper">

    <resultMap id="userMap" type="com.lemon.pojo.User">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <collection property="orderList" ofType="com.lemon.pojo.Order">
            <id property="id" column="oid"></id>
            <result property="ordertime" column="ordertime"></result>
            <result property="total" column="total"></result>
        </collection>
    </resultMap>

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

</mapper>

5.2.6 测试结果

5.3 多对多查询

5.3.1 多对多查询的模型

用户表和⻆色表的关系为，一个用户有多个⻆色，一个⻆色被多个用户使用

多对多查询的需求：查询用户同时查询出该用户的所有⻆色

5.3.2 多对多查询的语句

对应的sql语句：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;

查询的结果如下：

5.3.3 创建Role实体，修改User实体

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


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

5.3.4 添加UserMapper接口方法

List<User> findAllUserAndRole();

5.3.5 配置UserMapper.xml

<resultMap id="userRoleMap" type="com.lemon.pojo.User">
       <result property="id" column="userid"></result>
       <result property="username" column="username"></result>
       <collection property="roleList" ofType="com.lemon.pojo.Role">
           <result property="id" column="roleid"></result>
           <result property="roleName" column="roleName"></result>
           <result property="roleDesc" column="roleDesc"></result>
       </collection>
   </resultMap>

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