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; }