mybatis分页查询一对多

<!--分页查询诊室列表包括诊室预约的人数信息-->
    <select id="searchClinicRoomList" parameterType="com.bjs.glasses.entity.tocEshop.reservation.TocClinicRoomExp"
            resultMap="ClinicRoomWithDoctors">
        select SQL_CALC_FOUND_ROWS
        t.room_no,t.room_name,#{startTime}as startTime,#{endTime} as endTime,t.store_code,t.company_code
        from toc_clinic_room t
        where t.delete_flag = '0'
        <if test="companyCode != null and companyCode != ''">
            and t.company_code = #{companyCode,jdbcType=VARCHAR}
        </if>
        <if test="storeCode != null and storeCode != ''">
            and t.store_code = #{storeCode,jdbcType=VARCHAR}
        </if>
        <if test="roomName != null and roomName != ''">
            and t.room_name = #{roomName,jdbcType=VARCHAR}
        </if>
        order by t.reg_time desc
        <if test="limit != 0">
            limit ${limit*(page-1)}, ${limit}
        </if>
    </select>
    <resultMap id="ClinicRoomWithDoctors" type="com.bjs.glasses.entity.tocEshop.reservation.TocClinicRoomExp">
        <id property="roomNo" column="room_no"/>
        <result property="storeCode" column="room_name"/>
        <result property="roomName" column="store_code"/>
        <result property="companyCode" column="company_code"/>
        <result property="startTime" column="startTime"/>
        <result property="endTime" column="endTime"/>
        <result property="roomName" column="room_name"/>
        <result property="amMaxAppointments" column="am_max_appointments"/>
        <result property="pmMaxAppointments" column="pm_max_appointments"/>
        <!-- 一对多映射 -->
        <collection property="doctorList" javaType="ArrayList" ofType="com.bjs.glasses.entity.tocEshop.reservation.TocDoctorScheduleExp"
                    select="getDoctorListByRoomIdAndDate"
            传参,参数必须是主表里边查询出来的数据 column="{roomNo=room_no,storeCode=store_code,companyCode=company_code,startTime=startTime,endTime=endTime}"> </collection> </resultMap> <select id="getDoctorListByRoomIdAndDate" resultType="com.bjs.glasses.entity.tocEshop.reservation.TocDoctorScheduleExp"> SELECT tds.*, su.display_name FROM toc_doctor_schedule tds left join system_usr su on tds.emp_code=su.user_code and su.delete_flag='0' WHERE tds.delete_flag='0' and tds.company_code =#{companyCode,jdbcType=VARCHAR} and tds.store_code = #{storeCode,jdbcType=VARCHAR} and tds.room_no = #{roomNo,jdbcType=VARCHAR} <if test="startTime!='' and startTime!=null and endTime!='' and endTime!=null"> AND tds.work_date BETWEEN #{startTime} AND #{endTime} </if> </select>

 实体类

@Getter
@Setter
public class TocClinicRoomExp extends TocClinicRoom {
    // 每页条数
    private int limit;
    // 页号
    private int page;
    private List<TocDoctorScheduleExp> doctorList;
    /**
     * start_time,  开始时间
     */
    private String startTime;

    /**
     * end_time,  结束时间
     */
    private String endTime;

}

 

posted on 2025-07-09 16:27  IT-QI  阅读(5)  评论(0)    收藏  举报