mybatis + PageHelper 一对多分页查询

最新推荐文章于 2024-08-06 22:43:51 发布

非法占用

最新推荐文章于 2024-08-06 22:43:51 发布

阅读量505

点赞数 3

文章标签： mybatis

本文链接：https://blog.csdn.net/m0_51982748/article/details/138494696

版权

1 .直接使用collection标签

对象
@Data 
public class TaskAndResponseVO extends ProdTaskPo {
 private List<ProdAgvResponsePO> responsePOS;
 }

<resultMap id="selectResultMap" type="com.uspring.sabs.agv.entity.vo.TaskAndResponseVO">
        <id column="task_id" jdbcType="INTEGER" property="taskId" />
        <result column="task_code" jdbcType="VARCHAR" property="taskCode"/>
        <collection property="responsePOS" ofType="com.uspring.sabs.agv.entity.po.ProdAgvResponsePO">
            <id column="ar_id" jdbcType="INTEGER" property="arId" />
            <result column="ar_point" jdbcType="VARCHAR" property="arPoint" />
            <result column="create_time1" jdbcType="TIMESTAMP" property="createTime" />
            <result column="ar_method" jdbcType="VARCHAR" property="arMethod" />

        </collection>
    </resultMap>
    
    <select id="taskAndResponses" resultMap="selectResultMap">
        select pt.* ,par.ar_id,par.ar_method,par.create_time create_time1 from prod_task pt left join prod_agv_response par
            on pt.task_code=par.task_code
            <where>
                pt.task_status='COMPLETE'
                <if test="taskCode != null and taskCode != ''">
                    and pt.task_code like concat('%',#{taskCode},'%')
                </if>
                <if test="taskType != null and taskType != ''">
                    and pt.task_type like concat('%',#{taskType},'%')
                </if>
                <if test="agvCode != null and agvCode != ''">
                    and pt.agv_code like concat('%',#{agvCode},'%')
                </if>
            </where>


    </select>


当使用mybatis + PageHelper 一对多分页查询时直接使用collection标签时 数据的pageSize的数量为一对多的关联查询数量也就是responsePOS数量，此时需要重写一个分页方法 如下

2.重写分页方法
<resultMap id="selectResultMap" type="com.uspring.sabs.agv.entity.vo.TaskAndResponseVO">
    <id column="task_id" jdbcType="INTEGER" property="taskId"/>
    <result column="task_code" jdbcType="VARCHAR" property="taskCode"/>
    <result column="task_type" jdbcType="VARCHAR" property="taskType"/>
    <result column="agv_code" jdbcType="VARCHAR" property="agvCode"/>
    <result column="tc_code" jdbcType="VARCHAR" property="tcCode"/>
    <collection property="responsePOS" ofType="com.uspring.sabs.agv.entity.po.ProdAgvResponsePO"
                select="selectResponse" column="task_code">
    </collection>
</resultMap>
<resultMap id="responseMap" type="com.uspring.sabs.agv.entity.po.ProdAgvResponsePO">
    <id column="ar_id" jdbcType="INTEGER" property="arId"/>
    <result column="ar_point" jdbcType="VARCHAR" property="arPoint"/>
    <result column="create_time1" jdbcType="TIMESTAMP" property="createTime"/>
    <result column="ar_method" jdbcType="VARCHAR" property="arMethod"/>
</resultMap>

<select id="taskAndResponses" resultMap="selectResultMap">
    select pt.* from prod_task pt
    <where>
        pt.task_status='COMPLETE' and create_time between #{startCreateTime} and #{endCreateTime}
        <if test="taskCode != null and taskCode != ''">
            and pt.task_code like concat('%',#{taskCode},'%')
        </if>
        <if test="taskType != null and taskType != ''">
            and pt.task_type like concat('%',#{taskType},'%')
        </if>
        <if test="agvCode != null and agvCode != ''">
            and pt.agv_code like concat('%',#{agvCode},'%')
        </if>
    </where>
</select>

<select id="selectResponse" resultMap="responseMap">
    select par.ar_id, par.ar_method, par.create_time create_time1
    from prod_agv_response par
    where par.task_code = #{task_code}
</select>