mybatis mapper 一对多分页查询

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>
  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
对于一对多关系的分页查询,你可以使用MyBatis提供的嵌套查询来实现。下面是一个示例: 1. 首先,定义一个Mapper接口,例如`ParentMapper`,用于查询父对象和相关的子对象列表: ```java public interface ParentMapper { List<Parent> getParentList(); List<Child> getChildListByParentId(Long parentId); } ``` 2. 在`ParentMapper.xml`中,编写查询语句和嵌套查询: ```xml <select id="getParentList" resultType="Parent"> SELECT * FROM parent_table </select> <select id="getChildListByParentId" resultType="Child"> SELECT * FROM child_table WHERE parent_id = #{parentId} </select> ``` 3. 在你的服务类中,注入`ParentMapper`并使用嵌套查询进行分页查询: ```java @Service public class ParentService { @Autowired private ParentMapper parentMapper; public PageInfo<Parent> getParentListWithChildren(int pageNum, int pageSize) { PageHelper.startPage(pageNum, pageSize); List<Parent> parentList = parentMapper.getParentList(); for (Parent parent : parentList) { List<Child> childList = parentMapper.getChildListByParentId(parent.getId()); parent.setChildList(childList); } return new PageInfo<>(parentList); } } ``` 以上示例中,我们使用了PageHelper来进行分页处理,并在查询父对象时,通过循环查询子对象列表并设置到父对象中。最后返回使用PageInfo包装后的分页结果。 需要注意的是,以上示例仅作为参考,并不能直接运行,你需要根据具体的表结构和实体类进行适当的修改。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值