mybatis多表查询

最近在写spring+springmvc+mybatis相关的demo；分享下多表查询的心得：

A表结构：

C表结构

A实体类：

package com.azj.entity;


import java.util.List;


public class A {
private int aid;
private String aname;


private List<C> clist;

public List<C> getClist() {
return clist;
}
public void setClist(List<C> clist) {
this.clist = clist;
}
public int getAid() {
return aid;
}
public void setAid(int aid) {
this.aid = aid;
}
public String getAname() {
return aname;
}
public void setAname(String aname) {
this.aname = aname;
}

@Override
public String toString() {
return "A [aid=" + aid + ", aname=" + aname + ",   clist=" + clist + "]";
}


}

C实体类

package com.azj.entity;


public class C {
private int id;
private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "C [id=" + id + ", name=" + name + "]";
}
public C(int id, String name) {
super();
this.id = id;
this.name = name;
}
public C() {
super();
}

}

sql语句映射：

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
        <!--多对多查询  -->

        <!--第一种方法  -->
  <select id="getAll" resultMap="getA" parameterType="Map">
    <!-- select * from a aa left join b bb  on aa.bid=bb.bid right JOIN c cc on aa.aid=cc.cid where 1=1 -->
     select * from a aa left join c cc  on aa.aid=cc.aid where 1=1
      <choose>
        <when test="aid!=null">
           and aa.aid=${aid}
        </when>
        <when test="aname!=null">
           and aa.aname=${aname}
        </when>
      </choose> 
  </select>
  <resultMap type="A" id="getA">
     <id column="aid" property="aid"/>
       <result column="aname" property="aname"/>
     <collection property="clist"  javaType="ArrayList" ofType="C">
       <id column="cid" property="id"/>
       <result column="cname" property="name"/>
     </collection>
  </resultMap>

   <!--第二种方法  -->

<select id="getAll" resultMap="getA" parameterType="Map">
     select * from a aa  where 1=1
      <choose>
        <when test="aid!=null">
           and aa.aid=${aid}
        </when>
        <when test="aname!=null">
           and aa.aname=${aname}
        </when>
      </choose> 
  </select>
  <resultMap type="A" id="getA">
     <id column="aid" property="aid"/>
       <result column="aname" property="aname"/>
     <collection property="clist" column="aid"select="getC"  javaType="java.util.List" ofType="C"/>
  </resultMap>
<select id="getC" resultMap="StudentResultMap" parameterType="java.lang.Integer" >
    select *
    from C
    where aid = #{aid}
</select>
<resultMap id="StudentResultMap" type="C">  
       <id column="cid" property="id"/>
       <result column="cname" property="name"/>
  </resultMap> -->

</mapper>