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>
弯弯月亮,只为美好的自己。