mybatis mapper 一对多分页查询

GoodStudyAndDayDayUp

已于 2023-12-07 10:41:22 修改

阅读量1.7k

点赞数

分类专栏： mybatis 文章标签： mybatis级联查询

于 2022-06-10 22:25:06 首次发布

本文链接：https://blog.csdn.net/leinminna/article/details/125229065

版权

mybatis 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1. 使用级联查询解决一对多分页问题

下面的 mapper 语句为第一个版本，其中还存在一个bug，级联查询没有办法解决带条件的查询问题。

    <resultMap id="ProductCheckListPoolVoMap" type="com.lenovo.npi.portal.module.masterdata.vo.ProductCheckListPoolVo">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="phase_dict_code" jdbcType="VARCHAR" property="phaseDictCode"/>
        <result column="phase" jdbcType="VARCHAR" property="phase"/>
        <result column="sort" jdbcType="INTEGER" property="sort"/>
        <result column="level1" jdbcType="VARCHAR" property="level1"/>
        <result column="level2" jdbcType="VARCHAR" property="level2"/>
        <result column="owner" jdbcType="VARCHAR" property="owner"/>
        <result column="active_dict_code" jdbcType="VARCHAR" property="activeDictCode"/>
        <result column="active" jdbcType="VARCHAR" property="active"/>
        <result column="update_by" jdbcType="VARCHAR" property="updateBy"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
        <collection property="designTypeMappingList" ofType="com.lenovo.npi.portal.module.masterdata.model.ProductCheckListPoolMapping"
                    select="selectDesignTypeMappings" column="id"/>
    </resultMap>
    <select id="getByKey"  resultMap="ProductCheckListPoolVoMap">
        SELECT dpclp.*
        ,sd2.dict_name as phase
        ,sd3.dict_name as active
        ,dpclp.*
        from dim_product_check_list_pool as dpclp
        left join sys_dict as sd2 on sd2.dict_code=dpclp.phase_dict_code
        left join sys_dict as sd3 on sd3.dict_code=dpclp.active_dict_code
        <include refid="pageWhere"></include>
        <if test="param.sortName==null or param.sortName==''">
            order by update_time desc
        </if>
        <if test="param.sortName!=null and param.sortBy!=null and param.sortName!='' and param.sortBy!=''">
            order by
            ${@org.apache.commons.lang.StringEscapeUtils@escapeSql(param.sortName)}
            ${@org.apache.commons.lang.StringEscapeUtils@escapeSql(param.sortBy)}
        </if>
        <if test="param.pageSize > 0">
            limit #{param.pageSize} offset #{param.offset}
        </if>
    </select>
        <sql id="pageWhere">
        <where>
            <if test="param.key != null and param.key != ''">
                LOWER(concat(dio.name,sd2.dict_name,dpclp.sort,dpclp.level1,dpclp.level2,dpclp.owner,sd3.dict_name,dpclp.update_by))
                like LOWER(concat('%',#{param.key},'%'))
            </if>
        </where>
    </sql>

2. 使用分组解决带条件的分页查询

思路
– 1. 查出所有 left join 数据；
– 2. 分组，id，name分别组装为字符串
– 3. 带入查询条件、分页
– 4. 查询返回后 java 组装（新增字符串字段）

    <resultMap id="ProductCheckListPoolVoMap" type="com.lenovo.npi.portal.module.masterdata.vo.ProductCheckListPoolVo">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="phase_dict_code" jdbcType="VARCHAR" property="phaseDictCode"/>
        <result column="phase" jdbcType="VARCHAR" property="phase"/>
        <result column="sort" jdbcType="INTEGER" property="sort"/>
        <result column="level1" jdbcType="VARCHAR" property="level1"/>
        <result column="level2" jdbcType="VARCHAR" property="level2"/>
        <result column="owner" jdbcType="VARCHAR" property="owner"/>
        <result column="active_dict_code" jdbcType="VARCHAR" property="activeDictCode"/>
        <result column="active" jdbcType="VARCHAR" property="active"/>
        <result column="update_by" jdbcType="VARCHAR" property="updateBy"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
        <result column="designTypeIds" jdbcType="VARCHAR" property="designTypeIds"/>
        <result column="designTypes" jdbcType="VARCHAR" property="designTypes"/>
    </resultMap>
    <sql id="poolCommonSql">
        with t_all as(
        SELECT dpclp.*
        ,sd2.dict_name as phase
        ,sd3.dict_name as active
        ,dio.name as designType
        ,dio.id as designTypeId
        from dim_product_check_list_pool as dpclp
        left join dim_inhouse_odm as dio on dio.id = dpclp.design_type_id
        left join sys_dict as sd2 on sd2.dict_code=dpclp.phase_dict_code
        left join sys_dict as sd3 on sd3.dict_code=dpclp.active_dict_code
        )
        ,t_concat as(
        SELECT
        ta.id,ta.phase,ta.sort,ta.level1,ta.level2,ta.owner,ta.active,ta.update_by,ta.update_time
        ,array_to_string(ARRAY_AGG(ta.designTypeId), ',') as designTypeIds
        ,array_to_string(ARRAY_AGG(ta.designType),',') as designTypes
        from t_all as ta
        group by ta.id,ta.phase,ta.sort,ta.level1,ta.level2,ta.owner,ta.active,ta.update_by,ta.update_time
        )
    </sql>
    <select id="getByKey"  resultMap="ProductCheckListPoolVoMap">
        <include refid="poolCommonSql"></include>
        SELECT * from t_concat as tc
        <include refid="pageWhere"></include>
        <if test="param.sortName==null or param.sortName==''">
            order by update_time desc
        </if>
        <if test="param.sortName!=null and param.sortBy!=null and param.sortName!='' and param.sortBy!=''">
            order by
            ${@org.apache.commons.lang.StringEscapeUtils@escapeSql(param.sortName)}
            ${@org.apache.commons.lang.StringEscapeUtils@escapeSql(param.sortBy)}
        </if>
        <if test="param.pageSize > 0">
            limit #{param.pageSize} offset #{param.offset}
        </if>
    </select>
    <sql id="pageWhere">
        <where>
            <if test="param.key != null and param.key != ''">
                LOWER(concat(tc.designTypes,tc.phase,tc.sort,tc.level1,tc.level2,tc.owner,tc.active,tc.update_by))
                like LOWER(concat('%',#{param.key},'%'))
            </if>
        </where>
    </sql>