级联关系实现的方式
一对一
一对多
案例实操
一对一关系
实现方式 resultType/resultMap
接口方法
/**
* 一对一关联查询 resultType
* @param userId
* @return
*/
UserDtoqueryUserCardInfoForResultType(@Param("userId")intuserId);
sql 查询
<selectid="queryUserCardInfoForResultType"resultType="userDto">
SELECT
u.id,
u.user_name AS userName,
u.user_pwd AS userPwd,
u.flag,
u.cid,
c.number
FROM
USER u
LEFT JOIN card c ON u.cid = c.id
WHERE
u.id = #{userId}
</select>
resultType 实现
<selectid="queryUserByIdResultType"parameterType="int"resultType="com.xxx.dto.UserDto">
SELECT
u.id,
u.user_name AS userName,
u.create_time,
u.flag,
u.cid AS cid,
c.number
FROM
User u
LEFT JOIN card c u ON u.cid =c.id
WHERE
u.id = #{id}
</select>
resultMap 实现
<!--自定义映射关系实现关联 -->
<resultMapid="baseUser"type="com.xxx.mybatis.pojo.User">
<idcolumn="id"property="id"/><!-- 主键 -->
<!-- 普通例 -->
<resultcolumn="user_name"property="userName"/>
<resultcolumn="true_name"property="trueName"/>
<resultcolumn="role_name"property="roleName"/>
</resultMap>
<selectid="queryUserInfoByIdV2"parameterType="int"resultMap="baseUser">
SELECT u.id,u.user_name,u.true_name,l.role_name
FROM t_user u JOIN t_user_role r
ON (u.id = r.user_id) JOIN t_role l
on (r.role_id = l.id)
WHERE u.id = #{id}
</select>
resultMap + association实现
resultMap 映射定义
<resultMapid="baseUserRole"type="userRole">
<idcolumn="id"property="id"></id>
<resultcolumn="role_id"property="roleId"></result>
<associationproperty="role"column="role_id"select="queryRoleById"/>
</resultMap>
<resultMapid="baseRole"type="role">
<idcolumn="id"property="id"></id>
<resultcolumn="role_name"property="roleName"/>
<resultcolumn="role_remark"property="roleRemark"/>
</resultMap>
查询
<selectid="queryUserRoleInfoById"parameterType="int"resultMap="baseUserRole">
SELECT id, role_id FROM t_user_role WHERE id = #{id}
</select>
<selectid="queryRoleById"parameterType="int"resultMap="baseRole">
SELECT id, role_name , role_remark FROM t_role WHERE id = #{role_id}
</select>
一对多关系
实现方式:resultMap 实现
resultType 有局限，无法去重，需手动处理。
ResultMap 定义
<resultMapid="baseGoods"type="goods">
<idcolumn="id"property="id"/>
<resultcolumn="name"property="goodsName"/>
<resultcolumn="promotePrice"property="price"/>
<collectionproperty="ixxxs"column="id"select="queryGoodsIxxxsById"/>
</resultMap>
<resultMapid="baseGoodsImnage"type="GoodsIxxxs">
<idcolumn="id"property="id"></id>
<resultcolumn="goods_skuid"property="goodsSkuid"></result>
<resultcolumn="goods_img"property="ixxxUrl"></result>
</resultMap>
查询
<selectid="queryGoodsById"parameterType="int"resultMap="baseGoods"useCache="true">
select id, name , promotePrice FROM yg_goods WHERE id = #{id}
</select>
<selectid="queryGoodsIxxxsById"parameterType="int"resultMap="baseGoodsImnage"useCache="true">
SELECT id , goods_skuid , goods_img FROM yg_goods_imgage where goods_skuid = #{id}
</select>
扩展
sql
sql 元素用来定义一个可以复用的 SQL 语句段，供其它语句调用。比如：
<sqlid="User_columns">userId, userName, password</sql>
<!--用include 引用-->
<selectid="findUserById"resultMap="RM_User">
select<includerefid="User_columns"/>
from user where userId = #{userId}
</select>
###
