mybatis 连表查询，子查询 collection，返回 vo

原创已于 2022-08-18 22:46:50 修改 · 2.3k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #数据库 #java

于 2022-08-08 15:32:34 首次发布

java程序实现同时被 2 个专栏收录

20 篇文章

订阅专栏

SQL数据库

7 篇文章

订阅专栏

该博客详细介绍了如何使用MyBatis进行连表查询和子查询，以获取复杂的数据视图对象AppsVo。AppsService.xml中定义了主查询与子查询的ResultMap，包括Apps实体类与AppsVo视图类的映射。AppsVo包含了分类名称、分类层级名称以及应用分组集合和文章名称列表。同时，展示了AppsGroupMapper的子查询方法，用于获取应用的分组对象列表。主查询实现了分页功能，并支持多种查询条件，如应用名、类型、平台等。

mybatis 查询

- - 1. 连表查询，子查询 collection，返回 vo

1. 连表查询，子查询 collection，返回 vo

AppsService.xml 主查询

	<resultMap id="appsMap" type="com.boyia.magic.lxt.api.entity.Apps">
        <id property="id" column="id"/>
        <result property="appName" column="app_name"/>
        <result property="appType" column="app_type"/>
        <result property="appIcon" column="app_icon"/>
        <result property="appPlatform" column="app_platform"/>
        <result property="appCategoryId" column="app_category_id"/>
        <result property="appLanguage" column="app_language"/>
        <result property="appBrief" column="app_brief"/>
        <result property="appKey" column="app_key"/>
        <result property="appSecret" column="app_secret"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="status" column="status"/>
        <result property="delFlag" column="del_flag"/>
  </resultMap>
  
	<resultMap id="appVoMap" type="com.boyia.magic.lxt.api.vo.AppsVo">
		<id property="id" column="id"/>
		<result property="appName" column="app_name"/>
		<result property="appType" column="app_type"/>
		<result property="appIcon" column="app_icon"/>
		<result property="appPlatform" column="app_platform"/>
		<result property="appCategoryId" column="app_category_id"/>
		<result property="appLanguage" column="app_language"/>
		<result property="appBrief" column="app_brief"/>
		<result property="appKey" column="app_key"/>
		<result property="appSecret" column="app_secret"/>
		<result property="createBy" column="create_by"/>
		<result property="createTime" column="create_time"/>
		<result property="updateBy" column="update_by"/>
		<result property="updateTime" column="update_time"/>
		<result property="status" column="status"/>
		<result property="delFlag" column="del_flag"/>
		<!--分类名称-->
		<result property="categoryName" column="category_name"/>
		<!--分类层级名称-->
		<result property="categoryNames" column="category_names"/>
		<!--应用分组集合-->
		<collection property="groupList" ofType="com.boyia.magic.lxt.api.dto.AppGroupForListNameDto"
					select="getByAppsId" column="categoryId = id">
					<result property="articleId" column="id"/>
					<result property="articleName" column="name"/>
		</collection>

		<collection property="articleNameList" ofType="javax.long.String"
					select="getByAppsId" column="categoryId = id">
					<result property="articleName" column="name"/>
		</collection>
	</resultMap>

<select id="getByAppsId">
	SELECT
		name,
		sex
	WHERE
		article.category_id = #{categoryId}
</select>

	<!--分页查询-->
	<select id="pageByQuery" resultMap="appVoMap" resultType="com.boyia.magic.lxt.api.vo.AppsVo">
		SELECT
		  t1.id,
			t1.app_name,
			t1.app_type,
			t1.app_icon,
			t1.app_platform,
			t1.app_category_id,
			t1.app_language,
			t1.app_brief,
			t1.app_key,
			t1.app_secret,
			t1.create_by,
			t1.create_time,
			t1.update_by,
			t1.update_time,
			t1.status,
			t2.category_name,
			t2.category_names
		FROM
			lxt_platform_apps AS t1
			LEFT JOIN lxt_platform_apps_category AS t2 ON t1.app_category_id = t2.id
		WHERE
			t1.del_flag = '0'
			AND t2.del_flag = '0'
		<if test="query.appName != null and query.appName != ''">
			AND t1.app_name LIKE CONCAT(CONCAT('%', #{query.appName}), '%')
		</if>
		<if test="query.appType != null and query.appType != ''">
			AND t1.app_type = #{query.appType}
		</if>
		<if test="query.appPlatform != null and query.appPlatform != ''">
			AND t1.app_platform = #{query.appPlatform}
		</if>
		<if test="query.appCategoryId != null">
			AND t1.app_category_id = #{query.appCategoryId}
		  	OR t1.app_category_id IN ( SELECT id FROM lxt_platform_apps_category WHERE parent_ids LIKE CONCAT(CONCAT('%,',#{query.appCategoryId}),',%') )
		</if>
		<if test="query.groupId != null">
			AND t1.id IN ( SELECT apps_id FROM lxt_platform_apps_group_relation WHERE group_id = #{query.groupId} )
		</if>
		ORDER BY
			t1.create_time
		DESC
	</select>

AppsGroupMapper.java 子查询

@Mapper
public interface AppsGroupMapper extends MagicBaseMapper<AppsGroup> {

	/**
	 * 根据应用id查询分组对象列表
	 *
	 * @param id 应用id
	 * @return 应用分组对象列表
	 */
	List<AppGroupForListNameDto> getByAppsId(Long id);
}

AppsGroupMapper.xml 子查询

<mapper namespace="com.boyia.magic.lxt.mapper.AppsGroupMapper">

  <resultMap id="appsGroupMap" type="com.boyia.magic.lxt.api.entity.AppsGroup">
        <id property="id" column="id"/>
        <result property="groupName" column="group_name"/>
        <result property="rankWeight" column="rank_weight"/>
        <result property="remark" column="remark"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="status" column="status"/>
        <result property="delFlag" column="del_flag"/>
  </resultMap>

	<!--根据应用id查询分组对象列表-->
	<select id="getByAppsId" resultType="com.boyia.magic.lxt.api.dto.AppGroupForListNameDto">
		SELECT
			id,
			group_name
		FROM
			lxt_platform_apps_group
		WHERE
			id IN ( SELECT group_id FROM lxt_platform_apps_group_relation WHERE apps_id = #{id})
	</select>
</mapper>