mybatis级联查询的两种配置方式（很多人问我，这里给出答案）

孤独雪鹰

关键字：mybatis级联查询的两种方式


第一种配置方式：

1.定义四个实体。User   Role    Privilege   Resource,他们之间的对于关系为

2.需求：我通过用户名username查找出该用户对应的角色以及角色对应的权限和资源

3.实体

public class User implements Serializable {
private String id;
private String username;
private String password;

private Set<Role> role = new HashSet<Role>();

public class Role implements Serializable {
private String id;
private String name;
private String des;

private Set<Privilege> privilege = new HashSet<Privilege>();

public class Privilege implements Serializable {  private String id;  private String name;  private String des;

private Set<Resource> resource = new HashSet<Resource>();  private Privilege parent;  private Set<Privilege> child = new HashSet<Privilege>();

public class Resource implements Serializable {
private String id;
private String uri;
private String des;


4.UserMapper.xml的查询方法为

        <resultMap type="user" id="userResult">
<id column="id" property="id"/>
<result column="username" property="username"/>
<collection property="role" javaType="java.util.Set" resultMap="roleResult"/>
</resultMap>

<resultMap type="role" id="roleResult">
<id column="rid" property="id"/>
<result column="rname" property="name"/>
<result column="rdes" property="des"/>
<collection property="privilege" javaType="java.util.Set" resultMap="privilegeResult"/>
</resultMap>

<resultMap type="privilege" id="privilegeResult">
<id column="pid" property="id"/>
<result column="pname" property="name"/>
<collection property="resource" javaType="java.util.Set" resultMap="resourceResult"/>
</resultMap>

<resultMap type="resource" id="resourceResult">
<id column="resid" property="id"/>
<result column="resuri" property="uri"/>
</resultMap>

<select id="findUserByUserName" parameterType="string" resultMap="userResult">
  select u.id,u.username,r.id as rid,r.name as rname,r.des as rdes,p.id as pid,p.name as pname,res.id as resid,res.uri as resuri
  from tb_user u
  left join user_role ur on ur.user_id=u.id
  join tb_role r on r.id=ur.role_id
  join role_privilege rp on rp.role_id=r.id
  join tb_privilege p on p.id=rp.privilege_id
  join tb_resource res on res.privilege_id=p.id
  where u.username=#{username}
</select>

5.UserMapper.java定义方法

@Component
public interface UserMapper {

User findUserByUserName(@Param(value="username")String username);

}



第二种配置方式：

级联（关联）查询，mybatis已经有了很好的支持，配置也相当简单，示例：

一种是一对一的，一种是一结多的，  association用于前者，collection用于后者。下面都有相应配置。



当然一对一的，可以直接配置在一起，就不用两次查询了。


    <resultMap id="usersResult" type="com.zlwh.member.model.Users">
        <result property="id" column="id"/>
        <result property="loginName" column="login_name"/>
        <result property="userName" column="user_name"/>
        <result property="password" column="password"/>
        <result property="email" column="email"/>
        <result property="status" column="status"/>
        <result property="userType" column="user_type"/>
        <result property="memberType" column="member_type"/>
        <result property="imgPath" column="img_path"/>
        <result property="imgStatus" column="img_status"/>
        <result property="identification" column="identification"/>
        <result property="certificateCode" column="certificate_code"/>
        <result property="countyCode" column="county_code"/>
        <result property="schoolId" column="school_id"/>
        <result property="classId" column="class_id"/>
        <result property="grade" column="grade"/>
        <result property="sex" column="sex"/>
        <result property="birthday" column="birthday"/>
        <result property="mobile" column="mobile"/>
        <result property="inviteCode" column="invite_code"/>
        <result property="subjectId" column="subject_id"/>
        <result property="certificationStatus" column="Certification_status"/>
        <result property="createTime" column="create_time"/>
        <result property="validTime" column="valid_time"/>
        <result property="institutionId" column="institution_id"/>
        <result property="depName" column="dep_name"/>
        <result property="jobName" column="job_name"/>
        <association column="school_id" property="school"
        javaType="com.zlwh.member.model.School" select="getSchoolById" />
    </resultMap>
   
         <resultMap id="schoolResult" type="com.zlwh.member.model.School">
        <result property="id" column="id"/>
        <result property="schoolName" column="school_name"/>
        <result property="schoolCode" column="school_code"/>
        <result property="county" column="county"/>
        <result property="address" column="address"/>
        <result property="phase" column="phase"/>
    </resultMap>
      
    <select id="getSchoolById" resultMap="schoolResult">
        SELECT * FROM school WHERE id = #{id}
    </select>
   
    <resultMap id="usersResultForT" type="com.zlwh.member.model.Users">
        <result property="id" column="id"/>
        <result property="loginName" column="login_name"/>
        <result property="userName" column="user_name"/>
        <result property="inviteCode" column="invite_code"/>
        <collection property="userSubscriptions" column="id" select="getUserSubscriptionsByUserId" ></collection>
    </resultMap>
   

   
    <resultMap id="userSubscriptionResult" type="com.zlwh.member.model.UserSubscription">
        <result property="id" column="id"/>
        <result property="userId" column="user_id"/>
        <result property="subscriptionId" column="subscription_id"/>
        <result property="beginDate" column="begin_date"/>
        <result property="endDate" column="end_date"/>
        <result property="status" column="status"/>
        <result property="createTime" column="create_time"/>
        <result property="orderId" column="order_id"/>
        <result property="orderPrice" column="order_price"/>
        <result property="payTime" column="pay_time"/>   
    </resultMap>
   
    <select id="getStudentsByInviteCode" resultMap="usersResultForT">
        <![CDATA[SELECT id,login_name,user_name,invite_code FROM users    WHERE invite_code = #{inviteCode} and member_type=1]]>
    </select>



<select id="getById" resultMap="usersResult">
        SELECT <include refid="commonColumns" />
        <![CDATA[
            FROM users
            WHERE
                id = #{id}
        ]]>
    </select>

   
    <!-- 查所有已支付的订阅 -->
     <select id="getUserSubscriptionsByUserId" resultMap="userSubscriptionResult">
        SELECT * FROM user_subscription WHERE (status=1 or status=3) and user_id = #{userId} order by subscription_id asc
    </select>
   




    <!-- 用于select查询公用抽取的列 -->
    <sql id="commonColumns">
        <![CDATA[
            id ,
            login_name ,
            user_name ,
            password ,
            email ,
            status ,
            user_type ,
            member_type ,
            img_path ,
            img_status ,
            identification ,
            certificate_code ,
            county_code ,
            school_id ,
            class_id ,
            grade ,
            sex ,
            birthday ,
            mobile ,
            invite_code ,
            subject_id ,
            Certification_status ,
            create_time ,
            valid_time ,
            institution_id ,
            dep_name ,
            job_name
        ]]>
    </sql>