MyBatis的关联查询

关联映射的一对多

  //查询经理角色   以及  该角色下对应的员工集合
    public SmbmsRole getRoleAndUser(Integer id);



 <resultMap id="roleAndUserMapper" type="SmbmsRole">
        <id column="rid" property="rid"></id>
        <result column="roleName" property="roleName"/>
        <!--映射多的一方 property代表实体当中多的一方的属性名      ofType代表集合当中泛型类型-->
        <!-- select 代表执行查询的ID   column所引用的条件列   -->
        <collection property="userList" ofType="SmbmsUser" select="getRoleAndUserMutilSQL" column="rid">

        </collection>
    </resultMap>




    <!--<select id="getRoleAndUser" resultMap="roleAndUserMapper">
        select u.id,u.userName,u.userRole,r.rid,r.roleName from smbms_user as u,smbms_role as r where u.userRole=r.rid and r.rid=#{id}
    </select>-->

    <select id="getRoleAndUser" resultMap="roleAndUserMapper">
        select * from  smbms_role where rid=#{id}
    </select>
    <select id="getRoleAndUserMutilSQL" resultType="SmbmsUser">
        select * from  smbms_user where userRole=#{rid}
    </select>

关联查询的多对一

//查询所有用户信息  包含角色信息
public List<SmbmsUser> getUserList();


<resultMap id="userListAndRole" type="SmbmsUser">
        <id column="id" property="id"></id>
        <result column="userName" property="userName"/>
        <association property="role" javaType="SmbmsRole" select="getRole" column="userRole">
            <id column="rid" property="rid"></id>
            <result column="roleName" property="roleName"/>
        </association>
    </resultMap>
    <!--<select id="getUserList" resultMap="userListAndRole">
        select u.id,u.userName,u.userRole,r.rid,r.roleName from smbms_user as u,smbms_role as r where u.userRole=r.rid
    </select>-->


    <select id="getUserList" resultMap="userListAndRole">
        select * from smbms_user
    </select>
    <select id="getRole" resultType="SmbmsRole">
        select * from smbms_role where rid=#{userRole}
    </select>

关联查询的多对多

//查询所有学生信息  以及授课教员
public List<Student> getStudentInfo();



<resultMap id="studentAndTeacherMapper" type="Student">
        <id column="stuid" property="stuid"/>
        <result column="stuname" property="stuname"/>
        <collection property="teachers" ofType="Teacher">
            <id column="tid" property="tid"></id>
            <result property="tname" column="tname"/>
        </collection>
    </resultMap>
    <select id="getStudentInfo" resultMap="studentAndTeacherMapper">
        select * from student,teacher,stu_t where student.stuid=stu_t.stuid and teacher.tid=stu_t.tid
    </select>

关联映射的自查询

 //查询河南省  下的所有子集
public City getCityAndChildCitys(Integer cid);


 <resultMap id="CityAndChildCitysMapper" type="City">
        <id column="cid" property="cid"></id>
        <result column="cname" property="cname"/>
        <result column="pid" property="pid"/>
        <collection property="childCitys" ofType="City" select="getCityAndChildCitysMutilSQL" column="cid">
            <id column="cid" property="cid"></id>
            <result column="cname" property="cname"/>
            <result column="pid" property="pid"/>
        </collection>
    </resultMap>

   <select id="getCityAndChildCitys" resultMap="CityAndChildCitysMapper">
       select * from city where cid=#{cid}
   </select>
    <select id="getCityAndChildCitysMutilSQL" resultMap="CityAndChildCitysMapper">
       select * from city where pid=#{cid}
   </select>