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

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

第一种配置方式：

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

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

3.实体

publicclassUserimplementsSerializable{

privateStringid;

privateStringusername;

privateStringpassword;

privateSet<Role>role=newHashSet<Role>();

publicclassRoleimplementsSerializable{

privateStringid;

privateStringname;

privateStringdes;

privateSet<Privilege>privilege=newHashSet<Privilege>();

publicclassPrivilegeimplementsSerializable{privateStringid;privateStringname;privateStringdes;

privateSet<Resource>resource=newHashSet<Resource>();privatePrivilegeparent;privateSet<Privilege>child=newHashSet<Privilege>();

publicclassResourceimplementsSerializable{

privateStringid;

privateStringuri;

privateStringdes;

4.UserMapper.xml的查询方法为

<resultMaptype="user"id="userResult">

<idcolumn="id"property="id"/>

<resultcolumn="username"property="username"/>

<collectionproperty="role"javaType="java.util.Set"resultMap="roleResult"/>

</resultMap>

<resultMaptype="role"id="roleResult">

<idcolumn="rid"property="id"/>

<resultcolumn="rname"property="name"/>

<resultcolumn="rdes"property="des"/>

<collectionproperty="privilege"javaType="java.util.Set"resultMap="privilegeResult"/>

</resultMap>

<resultMaptype="privilege"id="privilegeResult">

<idcolumn="pid"property="id"/>

<resultcolumn="pname"property="name"/>

<collectionproperty="resource"javaType="java.util.Set"resultMap="resourceResult"/>

</resultMap>

<resultMaptype="resource"id="resourceResult">

<idcolumn="resid"property="id"/>

<resultcolumn="resuri"property="uri"/>

</resultMap>

<selectid="findUserByUserName"parameterType="string"resultMap="userResult">

selectu.id,u.username,r.idasrid,r.nameasrname,r.desasrdes,p.idaspid,p.nameaspname,res.idasresid,res.uriasresuri

fromtb_useru

leftjoinuser_roleuronur.user_id=u.id

jointb_roleronr.id=ur.role_id

joinrole_privilegerponrp.role_id=r.id

jointb_privilegeponp.id=rp.privilege_id

jointb_resourceresonres.privilege_id=p.id

whereu.username=#{username}

</select>

5.UserMapper.java定义方法

@Component

publicinterfaceUserMapper{

UserfindUserByUserName(@Param(value="username")Stringusername);

}

第二种配置方式：

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

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

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

<resultMapid="usersResult"type="com.zlwh.member.model.Users">

<resultproperty="id"column="id"/>

<resultproperty="loginName"column="login_name"/>

<resultproperty="userName"column="user_name"/>

<resultproperty="password"column="password"/>

<resultproperty="email"column="email"/>

<resultproperty="status"column="status"/>

<resultproperty="userType"column="user_type"/>

<resultproperty="memberType"column="member_type"/>

<resultproperty="imgPath"column="img_path"/>

<resultproperty="imgStatus"column="img_status"/>

<resultproperty="identification"column="identification"/>

<resultproperty="certificateCode"column="certificate_code"/>

<resultproperty="countyCode"column="county_code"/>

<resultproperty="schoolId"column="school_id"/>

<resultproperty="classId"column="class_id"/>

<resultproperty="grade"column="grade"/>

<resultproperty="sex"column="sex"/>

<resultproperty="birthday"column="birthday"/>

<resultproperty="mobile"column="mobile"/>

<resultproperty="inviteCode"column="invite_code"/>

<resultproperty="subjectId"column="subject_id"/>

<resultproperty="certificationStatus"column="Certification_status"/>

<resultproperty="createTime"column="create_time"/>

<resultproperty="validTime"column="valid_time"/>

<resultproperty="institutionId"column="institution_id"/>

<resultproperty="depName"column="dep_name"/>

<resultproperty="jobName"column="job_name"/>

<associationcolumn="school_id"property="school"

javaType="com.zlwh.member.model.School"select="getSchoolById"/>

</resultMap>

<resultMapid="schoolResult"type="com.zlwh.member.model.School">

<resultproperty="id"column="id"/>

<resultproperty="schoolName"column="school_name"/>

<resultproperty="schoolCode"column="school_code"/>

<resultproperty="county"column="county"/>

<resultproperty="address"column="address"/>

<resultproperty="phase"column="phase"/>

</resultMap>

<selectid="getSchoolById"resultMap="schoolResult">

SELECT*FROMschoolWHEREid=#{id}

</select>

<resultMapid="usersResultForT"type="com.zlwh.member.model.Users">

<resultproperty="id"column="id"/>

<resultproperty="loginName"column="login_name"/>

<resultproperty="userName"column="user_name"/>

<resultproperty="inviteCode"column="invite_code"/>

<collectionproperty="userSubscriptions"column="id"select="getUserSubscriptionsByUserId"></collection>

</resultMap>

<resultMapid="userSubscriptionResult"type="com.zlwh.member.model.UserSubscription">

<resultproperty="id"column="id"/>

<resultproperty="userId"column="user_id"/>

<resultproperty="subscriptionId"column="subscription_id"/>

<resultproperty="beginDate"column="begin_date"/>

<resultproperty="endDate"column="end_date"/>

<resultproperty="status"column="status"/>

<resultproperty="createTime"column="create_time"/>

<resultproperty="orderId"column="order_id"/>

<resultproperty="orderPrice"column="order_price"/>

<resultproperty="payTime"column="pay_time"/>

</resultMap>

<selectid="getStudentsByInviteCode"resultMap="usersResultForT">

<![CDATA[SELECTid,login_name,user_name,invite_codeFROMusersWHEREinvite_code=#{inviteCode}andmember_type=1]]>

</select>

<selectid="getById"resultMap="usersResult">

SELECT<includerefid="commonColumns"/>

<![CDATA[

FROMusers

WHERE

id=#{id}

]]>

</select>

<!--查所有已支付的订阅-->

<selectid="getUserSubscriptionsByUserId"resultMap="userSubscriptionResult">

SELECT*FROMuser_subscriptionWHERE(status=1orstatus=3)anduser_id=#{userId}orderbysubscription_idasc

</select>

<!--用于select查询公用抽取的列-->

<sqlid="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>