问题
pageHelper的原理是默认执行分页中对应数量条数的sql,所以在mybatis的1对多查询中,如果直接书写,会查询出少于你主表条数的结果,产生问题,并且有些查询条件需要集成在多表这一方,,之前在博文上找了很久,没有啥具体答案,参考了mybatis的官方文档,整理了如下示例,直接上代码
主sql语句
<select id="findAllByCondition" resultMap="findAllTrafficEventMap">
select
event.*,
maxHistory.id as trafficEventHistoryId,
case when ('${judgeStartTime}' != null) then '${judgeStartTime}' else '' end as judgeStartTime,
case when ('${judgeEndTime}' != null) then '${judgeEndTime}' else '' end as judgeEndTime
from
traffic_event event
inner join
(select max(id) as id, traffic_event_id from traffic_event_history group by traffic_event_id) maxHistory
on event.traffic_event_id = maxHistory.traffic_event_id
<where>
<if test="trafficEventType != null">
event.traffic_event_type = #{trafficEventType}
</if>
<if test="isRelieved != null">
and event.is_relieved = #{isRelieved}
</if>
<if test="checkStatus != null">
and event.check_status = #{checkStatus}
</if>
</where>
</select>
多方sql语句
<select id="selectJudgeTimes" resultMap="JudgeTimesMap" parameterType="java.util.Map">
SELECT
judgeTime.*
FROM
traffic_event_judge_time judgeTime
<where>
judgeTime.traffic_event_history_id = #{trafficEventHistoryId}
<if test="judgeStartTime != null and judgeStartTime != ''">
and judgeTime.judge_time :: timestamp >= #{judgeStartTime} :: timestamp
</if>
<if test="judgeEndTime != null and judgeEndTime != ''">
and judgeTime.judge_time :: timestamp <= #{judgeEndTime} :: timestamp
</if>
</where>
</select>
两者的映射关系
<resultMap id="findAllTrafficEventMap" type="com.bxt.event.po.TrafficEventPO">
<result column="traffic_event_id" property="trafficEventId"/>
<result column="drc_event_report_time" property="drcEventReportTime"/>
<collection property="judgeTimes" ofType="com.bxt.event.po.JudgeTimePO"
select="selectJudgeTimes" column="{trafficEventHistoryId=trafficEventHistoryId, judgeStartTime=judgeStartTime, judgeEndTime=judgeEndTime}">
</collection>
</resultMap>
其中trafficEventHistoryId,judgeStartTime,judgeEndTime三者属于多方的查询条件
参数含义讲解:
<!--property:对应实体类中的参数名称-->
<!--column:需要往子表传递的字段-->
<!--javaType:该参数类型-->
<!--ofType:该参数泛型-->
<!--select:子查询id名称-->
<collection property="" column="" ofType="" javaType="" select="" />