sql语句-实体属性有集合怎么批量查询

1、背景

前端返回一个实体类，实体类里还有集合。要对集合外的属性查询，还要对集合批量查询，并且属性可能为空。返回给前端的结果是个实体类，实体类里有集合。

2、前端实体类

public class AppletSyncDiseaseInfoBO {// 病害信息同步接口/*** 组织id*/private Integer organizationId;/*** 最后一次同步时间，病害更新时间小于此时间的不同步，可为空*/private String lastSyncTime;/*** 返回结果中的坐标系WGS84等，待约定*/private String coordinate;/*** 路面类型:沥青A,水泥D*/private char roadSurface;/*** 病害集合*/private List<AppletDiseaseInfoBO> disease;
}

public class AppletDiseaseInfoBO {/*** 病害类型*/private String proType;/*** 筛选上限，为空则不做筛选*/private Double upperLimit;/*** 筛选下限，为空则不做筛选*/private Double lowerLimit;}

3、返回给前端的实体类

public class AppletDiseaseInfoVO {/*** 组织id*/private Integer organizationId;/*** 接口调用结果返回时间*/private String nowTime;/*** 返回结果中的坐标系WGS84等，待约定*/private String coordinate;List<AppletDiseaseVO> problem;
}

public class AppletDiseaseVO extends AppletDiseaseBO {/*** 病害id*/private Integer proId;/*** 识别任务id*/private Integer subId;/*** 图片id*/private Integer picId;/*** 病害类型*/private String proType;/*** 病害名称*/private String proName;/*** 经度*/private Double lon;/*** 纬度*/private Double lat;/*** 道路id*/private Integer roadId;/*** 道路区间id*/private Integer roadIntervalId;/*** 道路方向，上行U；下行D*/private char roadDirection;/*** 桩号*/private Integer chainage;/*** 桩号偏移量*/private Double chainageOffset;/*** 影响面积或长度*/private Double proDestroy;/*** 原始图片地址*/private String picUrl;/*** 病害发现时间*/private String findTime;
}

4、sql语句

    <resultMap id="syncDiseaseInfoMap" type="com.jzsk.presentation.pojo.vo.AppletSyncDiseaseInfoVO"><result column="organization_id" property="organizationId"/>-- 以下就是映射到集合里<collection property="problem"  javaType="java.util.List" resultMap="problemsMap"/></resultMap><resultMap id="problemsMap" type="com.jzsk.presentation.pojo.bo.AppletDiseaseBO"><result column="proId" property="proId"/><result column="rec_sub_id" property="subId"/><result column="picId" property="picId"/><result column="pro_type" property="proType"/><result column="pro_name" property="proName"/><result column="gps_lon" property="lon"/><result column="gps_lat" property="lat"/><result column="road_id" property="roadId"/><result column="interval_id" property="roadIntervalId"/><result column="road_direction" property="roadDirection"/><result column="chainage" property="chainage"/><result column="chainage_offset" property="chainageOffset"/><result column="pro_destroy" property="proDestroy"/><result column="photo_name" property="photoName"/><result column="output_path" property="outputPath"/><result column="reported_time" property="findTime"/><result column="is_review" property="isReview"/></resultMap><select id="syncDiseaseInfo" parameterType="com.jzsk.presentation.pojo.bo.AppletSyncDiseaseInfoBO"resultMap="syncDiseaseInfoMap">SELECTrrp.organization_id,rrp.id as proId,rrp.rec_sub_id,cp.id AS picId,rrp.pro_type,rrp.pro_name,rrp.gps_lon,rrp.gps_lat,rl.road_id,rs.interval_id,rl.road_direction,rrp.chainage,rrp.chainage_offset,rrp.pro_destroy,rr.photo_name,rs.output_path,rs.reported_time,rs.lane,rr.is_reviewFROMrec_result_problem rrp-- 连表的时候 先去查出子表 加快检索效率 LEFT JOIN (SELECT rresult.id,rresult.photo_name,rresult.is_review FROM rec_result rresult WHERE rresult.organization_id = #{organizationId}) AS rr ON rrp.rec_result_id = rr.idLEFT JOIN (SELECT rsubmit.id,rsubmit.interval_id,rsubmit.output_path,rsubmit.reported_time,rsubmit.lane,rsubmit.pavement_type,rsubmit.locus_id FROM rec_submit rsubmit WHERE rsubmit.organization_id = #{organizationId}) AS rs ON rrp.rec_sub_id = rs.idLEFT JOIN (SELECT rlocus.road_id,rlocus.id,rlocus.road_direction FROM road_locus rlocus WHERE rlocus.organization_id = #{organizationId}) AS rl ON rl.id = rs.locus_idLEFT JOIN (SELECT ctask.interval_id,ctask.id FROM capture_task ctask WHERE ctask.organization_id = #{organizationId}) AS ct ON ct.interval_id = rs.interval_idLEFT JOIN (SELECT cphoto.task_id,cphoto.id FROM capture_photo cphoto WHERE cphoto.organization_id = #{organizationId}) AS cp ON cp.task_id = ct.idWHERErrp.organization_id = #{organizationId}<if test="lastSyncTime != null and lastSyncTime != ''">AND rrp.create_time &gt;= #{lastSyncTime}</if><if test="roadSurface != null and roadSurface != ''">AND rs.pavement_type = #{roadSurface}</if>-- 判断传入的集合是否为空-- 如果传入的是数组 就用 <if test="disease != null and disease.length > 0"><if test="disease != null and disease.size() > 0">AND<foreach collection="disease" item="diseaseInfo" open="(" close=")" separator=" OR ">-- separator=" OR " 和 这个括号是精髓-- 比如你传入(A) OR (B) 这里就会取结果的并集-- 如果不加括号 只是 A OR B ,那么满足一个条件就会返回数据(<if test="diseaseInfo.proType != null and diseaseInfo.proType != ''">rrp.pro_type = #{diseaseInfo.proType}</if><if test="diseaseInfo.upperLimit != null">AND rrp.pro_destroy &lt;= #{diseaseInfo.upperLimit}</if><if test="diseaseInfo.lowerLimit != null">AND rrp.pro_destroy &gt;= #{diseaseInfo.lowerLimit}</if>)</foreach></if></select>

5、其他

如果你只是传入一个集合进行查询

mapper接口要加 @parm("")注解

5.1、maapper层接口

ResultSectionBO querySectionChainageBySubId(@Param("idList") List<Integer> idList);

5.2、sql语句

    <select id="querySectionChainageBySubId"  resultType="com.jzsk.presentation.pojo.bo.ResultSectionBO">SELECT MIN(chainage*1000+chainage_offset) AS minChainage,MAX(chainage*1000+chainage_offset) AS maxChainageFROM REC_RESULT WHERE is_del='N'<if test="idList != null and idList.size >0">AND rec_sub_id IN<foreach collection="idList" index="index" item="item" open="(" separator="," close=")">#{item}</foreach></if></select>