Mybatis实现多对多关联组合查询

原创

wg_iddaahBB 2021-06-04 15:42:27 ©著作权

©著作权归作者所有：来自51CTO博客作者wg_iddaahBB的原创作品，请联系作者获取转载授权，否则将追究法律责任

1.需求

用户信息表、标签表、用户标签关联信息表

在做用户列表查询的时候，需要查询出该用户对应的用户标签

<resultMap id="BaseUserInfoResponseResultMap" type="com.miniapps.micro.model.response.UserInfoResponse">
  <id column="id" jdbcType="INTEGER" property="id" />
  <result column="program_user_token" jdbcType="VARCHAR" property="programUserToken" />
  <result column="user_type" jdbcType="VARCHAR" property="userType" />
  <result column="name" jdbcType="VARCHAR" property="name" />
  <result column="sex" jdbcType="VARCHAR" property="sex" />
  <result column="address" jdbcType="VARCHAR" property="address" />
  <result column="birthday" jdbcType="DATE" property="birthday" />
  <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
  <result column="last_serviced_time" jdbcType="TIMESTAMP" property="lastServicedTime" />
  <result column="last_login_time" jdbcType="TIMESTAMP" property="lastLoginTime" />
  <result column="score" jdbcType="INTEGER" property="scoreTotal" />
  <collection property="tagList" ofType="com.miniapps.micro.model.base.UserTags">
    <id column="t_id" jdbcType="INTEGER" property="id" />
    <result column="tag_name" jdbcType="VARCHAR" property="tagName" />
  </collection>
</resultMap>

<select id="queryUserInfoByToken" resultMap="BaseUserInfoResponseResultMap">
  SELECT  t1.id,t1.program_user_token, t1.user_type,  t1.name,
  t1.sex,t1.address, t1.birthday, t1.create_time, t1.last_serviced_time, t1.last_login_time,
  t1.score,t3.id as t_id,t3.tag_name
  FROM `user_info` t1
  LEFT JOIN user_tag_relationship t2 on t1.id=t2.user_info_id
  LEFT JOIN user_tags t3 on t2.user_tag_id=t3.id
  where  t1.program_user_token = #{programToken}
  ORDER BY t1.create_time DESC
  LIMIT #{start}, #{pageSize}
</select>

考察知识：

1.Mybatis的关联组合查询

2、collection的用法

3.相同字段名的处理