Mybatis的嵌套查询-column多条件

Mybatis的嵌套查询

一、结构

创建三个表user role user_role

简单展示一下嵌套查询的例子，其实就是Collection放到 ResultMap，下一层把上一层的查询结果作为条件传入。

-- master.`user` definition

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `age` int DEFAULT NULL,
  `name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id_IDX` (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户表';

-- master.`role` definition

CREATE TABLE `role` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `role_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;	

-- master.user_role definition

CREATE TABLE `user_role` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `role_id` bigint NOT NULL COMMENT '角色ID',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

二、动态SQL

1、多层子查询嵌套

<resultMap id="userResult" type="com.yangjiapo.asynch.entity.User">
    <id column="id" property="id" jdbcType="BIGINT"/>
    <result column="age" property="age" jdbcType="INTEGER"/>
    <result column="name" property="name" jdbcType="VARCHAR"/>
    <collection property="roleList" ofType="com.yangjiapo.asynch.entity.Role" column="{userId=id}"
                select="selectRoleForUser"/>
</resultMap>

构造了一个User对象的返回结果，当然，一个用户有多个角色，即返回一个List的角色列表。

这里需要注意的是ofType 属性，即返回嵌套的对象是哪一个。在selectRoleForUser查询中，是嵌套的查询，在上一级的查询结果中有user表的ID。

这里再column属性中可以指定查询条件，即上一层的查询结果user的id。

<select id="selectRoleForUser" resultType="com.yangjiapo.asynch.entity.Role">
        select r.* from `role` r
        left join user_role ur on ur.role_id = r.id
        where ur.user_id = #{userId}
   </select>

主查询如下

 <select id="selectRoleForUserList" resultMap="userResult">
        select * from user where id = #{id,jdbcType=BIGINT}
    </select>

2、关联查询嵌套结果

还有一种即直接关联查询，返回结果。

<resultMap id="BaseResultMap" type="com.yangjiapo.asynch.entity.User">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="age" property="age" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <collection property="roleList" javaType="ArrayList" ofType="com.yangjiapo.asynch.entity.Role">
            <id column="role_id" property="id" jdbcType="BIGINT"/>
            <result column="role_name" property="roleName" jdbcType="VARCHAR"/>
        </collection>
    </resultMap>

这里需要注意的是，嵌套中的查询结果字段不要和主表一样。

只有一个查询。

<select id="selectUser" resultMap="BaseResultMap" parameterType="com.yangjiapo.asynch.entity.User">
        select u.*,r.id as role_id, r.role_name from user u
        left join user_role ur on ur.user_id = u.id
        left join `role` r  on r.id = ur.role_id
        <where>
            <if test="id != null">
                u.id = #{id,jdbcType=BIGINT}
            </if>
            <if test="name != null and name != ''">
                u.name = #{name}
            </if>
        </where>
    </select>

三、总结

嵌套查询，即是上层的查询结果可以作为下层的查询条件。在关联查询中也是一样，left join后面也是有条件关联。

在 column 字段中，它是一个 set 集合。如下

<resultMap id="entityResult" type="xxx">
        <collection property="yyyList" ofType="yyy" column="{yyyId=id,yyyName=xxx_name,yyyCode=xxx_code}"
        select="selectYYY"/>
    </resultMap>