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; }
浙公网安备 33010602011771号