项目场景:
项目场景:车型和ecu一对多的两张表,希望能根据条件查出车型并查出关联的所有ecu(查询时对车型和ecu都有条件要求)
问题描述
在进行查询时分页插件失效了,查出来的是车型关联ecu的总条数,但是希望总条数为车型的条数
<select id="selectVehicleModelInfo" resultMap="vehicleDto">
select ivm.vehicle_model_id,
ivm.vehicle_model_name,
ivm.company_id,
ivm.status,
ivm.update_time,
ive.vehicle_ecu_id,
ive.vehicle_ecu_name,
ive.status
from iot_vehicle_model ivm left join iot_vehicle_ecu ive
on ivm.vehicle_model_id = ive.vehicle_id
where 1 = 1
<if test="param.companyId != null and param.companyId != ''">
AND (
ivm.company_id = #{param.companyId}
)
</if>
<if test="param.fieldOne != null and param.fieldOne != ''">
AND (
ivm.vehicle_model_id = #{param.fieldOne}
)
</if>
<if test="param.fieldTwo != null and param.fieldTwo != ''">
AND (
ive.vehicle_ecu_id= #{param.fieldTwo}
)
</if>
<if test="param.dateTime != null and param.dateTime != ''">
AND (
DATE_FORMAT(ivm.update_time, '%Y-%m-%d') = #{param.dateTime}
)
</if>
</select>
原因分析:
是因为pageHelper 在进行分页时,会将总条数进行分页。这个时候如果想得到正确结果,需要用resultMap+collection进行复合查询。
解决方案:
先查iot_vehicle_model 这个表,再查根据查出来的内容查iot_vehicle_ecu表。
这里有一个需要注意的点是:参数的传递问题,第一个查询接收到了所有的条件参数,需要把条件参数传递给第二个查询。
//先查第一张表
<select id="selectVehicleModelInfo" resultMap="vehicleDto">
select vehicle_model_id,
vehicle_model_name,
company_id,
status,
update_time,
#{param.fieldTwo} fieldTwo //这里将参数作为resultMap结果
from iot_vehicle_model
where 1 = 1
<if test="param.companyId != null and param.companyId != ''">
AND (
company_id = #{param.companyId}
)
</if>
<if test="param.fieldOne != null and param.fieldOne != ''">
AND (
vehicle_model_id = #{param.fieldOne}
)
</if>
<if test="param.dateTime != null and param.dateTime != ''">
AND (
DATE_FORMAT(update_time, '%Y-%m-%d') = #{param.dateTime}
)
</if>
</select>
//将第一步查询的结果设为resultMap
<resultMap id="vehicleDto" type="com.lenovoconnect.ota.core.model.dto.VehicleDto">
<result property="vehicleModelId" column="vehicle_model_id"></result>
<result property="vehicleModelName" column="vehicle_model_name"></result>
<result property="status" column="status"></result>
<result property="updateTime" column="update_time"></result>
<result property="companyId" column="company_id"></result>
//这里是重点,将参数接收,并执行第二个查询
<collection property="vehicleEcuDtos" ofType="com.lenovoconnect.ota.core.model.dto.VehicleEcuDto"
select="selectVehicleEcuInfo" column="{vehicleModelId=vehicle_model_id, fieldTwo=fieldTwo}"/>
</resultMap>
//执行第二步查询
<select id="selectVehicleEcuInfo" resultType="com.lenovoconnect.ota.core.model.dto.VehicleEcuDto">
select ive.vehicle_ecu_id,
ive.vehicle_ecu_name,
ive.status
from iot_vehicle_ecu ive
where 1 = 1
and ive.vehicle_id = #{vehicleModelId}
<if test="fieldTwo != null and fieldTwo != ''">
AND (
ive.vehicle_ecu_id = #{fieldTwo}
)
</if>
</select>