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

桃扇骨 2021-09-24 12:44 406阅读 0赞

个人网站：[http://xiaocaoshare.com/][http_xiaocaoshare.com]

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.相同字段名的处理

[http_xiaocaoshare.com]: http://xiaocaoshare.com/