mybatis 查询
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>