MyBatis多表查询（一对一，一对多，多对多）

1、一对一
关键字：association
作用：针对pojo对象属性的映射
property：pojo的属性名
javaType：pojo类名
(1) 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集

<resultMap type="com.gec.domain.Person" id="basePersonResultMap">
    <id column="id" property="personId"/>
    <result column="name" property="name"/>
    <result column="sex" property="sex"/>
    <result column="age" property="age"/>
    <association property="card" javaType="com.gec.domain.Card">
        <!-- 1、构造器注入
      <constructor>
            <idArg column="id" javaType="int"/>
            <arg column="code" javaType="string"/>
        </constructor> 
      -->
        <!-- 2、setter注入 -->
        <result column="id" property="cardId"/>
        <result column="code" property="code"/>
    </association>
</resultMap>
<select id="queryUserList" resultMap="basePersonResultMap">
    select p.id as personId,p.name,p.sex,p.age,c.*
    from tbl_person p,tbl_card c where p.card_id=c.id;
</select>

(2) 嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型
column=“引入执行另外定制sql方法的参数值（外键）”
select=“执行定制sql方法名”
PersonMapper2.xml

<resultMap type="com.gec.domain.Person" id="basePersonResultMap">
       <id column="id" property="personId"/>
       <result column="name" property="name"/>
       <result column="sex" property="sex"/>
       <result column="age" property="age"/>
       <association property="card"
                    javaType="com.gec.domain.Card"
                    column="card_id"
                    select="com.gec.mapper.CardMapper.queryCardById">
       </association>
   </resultMap>
   <select id="queryUserList" resultMap="basePersonResultMap">
select * from tbl_person;
   </select>

    <resultMap type="com.gec.domain.Card" id="baseCardResultMap">
        <id column="card_id" property="cardId"/>
        <result column="code" property="code"/>
    </resultMap>
    <select id="queryCardById" resultMap="baseCardResultMap">
   select c.id as card_id,c.code from tbl_card c 
   where c.id=#{id};
</select>
    <resultMap type="com.gec.domain.Card" id="queryCardResultMap" extends="baseCardResultMap">
        <association property="person" javaType="com.gec.domain.Person">
            <id column="card_id" property="personId"/>
            <result column="name" property="name"/>
            <result column="sex" property="sex"/>
            <result column="age" property="age"/>
        </association>
    </resultMap>
    <select id="queryCardList" resultMap="queryCardResultMap">
   SELECT c.id AS card_id, c.code, p.* FROM tbl_card c,tbl_person p WHERE c.id=p.card_id;
    </select>

多对一

<!--
按结果查询
实现了select student.id,student.name,teacher.name from student,teacher where student.tid=teacher.id
-->
<select id="getStudentList2" resultMap="StudentTeacher2">
  select student.id,student.name,teacher.name as tname from student,teacher where student.tid=teacher.id
</select>
<resultMap id="StudentTeacher2" type="com.tjrac.pojo.Student">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <association property="teacher" javaType="com.tjrac.pojo.Teacher">
        <result property="name" column="tname"/>
    </association>
</resultMap>

<!--==========================================================-->
<!--
按嵌套查询
实现了select student.id,student.name,teacher.name from student,teacher where student.tid=teacher.id
-->
<select id="getStudentList" resultMap="StudentTeacher">
  select * from student
</select>
<select id="getTeacherList" resultType="com.tjrac.pojo.Teacher">
  select * from teacher where id=#{id}
</select>
<resultMap id="StudentTeacher" type="com.tjrac.pojo.Student" >
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <association property="teacher" column="tid" javaType="com.tjrac.pojo.Teacher" select="getTeacherList"/>
</resultMap>

多对多

商品表、订单表之间就是以多对多关联
商品与订单的关系表
描述多对多的数据表实现
(1)商品pojo:

public class Article implements Serializable {
    private Integer articleId;
    private String name;
    private Double price;
    private String remark;
    private List<Order> orders;
    省略setter/gettera方法
}

(2)商品表映射：
ArticleMapper.xml

 <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" >
<mapper namespace="com.gec.mapper.ArticleMapper">  
    <resultMap type="article" id="baseArticleResultMap">      
        <id column="id" property="articleId"/>    
        <result column="NAME" property="name"/>       
        <result column="price" property="price"/>     
        <result column="remark" property="remark"/>    
    </resultMap>   
    <resultMap type="article" id="findArtcleByIdResultMap" extends="baseArticleResultMap">    
        <collection property="orders" javaType="ArrayList" ofType="com.gec.domain.Article" column="id" select="com.gec.mapper.OrderMapper.findOrderByArticleId">      
        </collection>  
    </resultMap>   
        <!-- 根据订单id查询商品 -->    
        <select id="findArtcleByOrderId" resultMap="baseArticleResultMap">    
      select * from tb_article  where id        in (select article_id from tb_item where order_id=#{id})   
      </select>  
        <select id="findArtcleById" resultMap="findArtcleByIdResultMap">      
      select * from tb_article  where id=#{id}   
      </select>
</mapper>