多条件查询写法

布满荆棘的人生 2023-10-05 18:06 2阅读 0赞

学系笔记之Sql  
前端查询条件一个选项在可以多选的情况下后端用List去接收，多个条件多个List  
并封装在一个对象中，后端只需要接收封装的对象即可

@ApiModel("载体列表")
    public class CarriersDTOs implements Serializable {
    
        @ApiModelProperty(value = "产业方向条件", example = "")
        private List<String> industrial;
    
        @ApiModelProperty(value = "学科方向条件", example = "")
        private List<String> subject;
    
        @ApiModelProperty(value = "级别等级条件", example = "")
        private List<String> level;
    
        @ApiModelProperty(value = "项目阶段条件", example = "")
        private List<String> stage;
    
        @ApiModelProperty(value = "建设主体条件", example = "")
        private List<String> build;
    
        @ApiModelProperty(value = "批复时间条件", example = "")
        private List<String> replyTime;
    
        @ApiModelProperty(value = "所在区域条件", example = "")
        private List<String> region;

数据库sql写法：

<select id="selectCountNumber5" resultType="com.pingan.smartcity.gov.drc.industrial.dto.carrier.InnovationCarriersCountDTORsp">
            select count(*) as theTotalCount from "INNOVATION_RESOURCES"."INNOVATION_CARRIER_NAME" where PROJECT_PHASE=#{carriersDTOs.builds}  <include refid="carrierType2"/>
        </select>

<sql id="carrierType2">
            <where>
                REGION = #{region}
                <if test=" carriersDTOs.carrierType != null and carriersDTOs.carrierType!= '' ">
                    and CARRIER_TYPE = #{carriersDTOs.carrierType}
                </if>
                <if test="carriersDTOs.industrial != null and carriersDTOs.industrial.size() > 0 ">
                    AND INDUSTRY_CATEGORY in
                    <foreach close=")" collection="carriersDTOs.industrial" index="index" item="item" open="(" separator=",">
                        #{item}
                    </foreach>
                </if>
                <if test="carriersDTOs.subject != null and carriersDTOs.subject.size()  > 0 ">
                    And FIELD_DIRECTION in
                    <foreach close=")" collection="carriersDTOs.subject" index="index" item="item" open="(" separator=",">
                        #{item}
                    </foreach>
                </if>
                <if test="carriersDTOs.level != null and carriersDTOs.level.size()  > 0 ">
                    And LEVEL in
                    <foreach close=")" collection="carriersDTOs.level" index="index" item="item" open="(" separator=",">
                        #{item}
                    </foreach>
                </if>
                <if test="carriersDTOs.stage != null and carriersDTOs.stage.size()  > 0 ">
                    And PROJECT_PHASE in
                    <foreach close=")" collection="carriersDTOs.stage" index="index" item="item" open="(" separator=",">
                        #{item}
                    </foreach>
                </if>
                <if test="carriersDTOs.build != null and carriersDTOs.build.size()  >0 ">
                    And CONSTRUCTION_SUBJECT in
                    <foreach close=")" collection="carriersDTOs.build" index="index" item="item" open="(" separator=",">
                        #{item}
                    </foreach>
                </if>
                <if test="carriersDTOs.replyTime != null and carriersDTOs.replyTime.size()  > 0 ">
                    And REPLY_TIME in
                    <foreach close=")" collection="carriersDTOs.replyTime" index="index" item="item" open="(" separator=",">
                        #{item}
                    </foreach>
                </if>
                <if test="carriersDTOs.region != null and carriersDTOs.region.size()  > 0 ">
                    And REGION in
                    <foreach close=")" collection="carriersDTOs.region" index="index" item="item" open="(" separator=",">
                        #{item}
                    </foreach>
                </if>
                <if test="carriersDTOs.builds != null and carriersDTOs.builds != '' ">
                    And CONSTRUCTION_SUBJECT = #{carriersDTOs.builds}
                </if>
                <if test="carriersDTOs.slevelb != null and carriersDTOs.slevelb != '' ">
                    AND NAME = #{carriersDTOs.slevelb}
                </if>
            </where>
        </sql>

条件为list在写sql时要对list进行遍历；例：  
条件：

public class CarriersDTOs implements Serializable {
    
        @ApiModelProperty(value = "产业方向条件", example = "")
        private List<String> industrial

条件\`List industrial;封装在CarriersDTOs 中；  
在写sql时进项遍历

<if test="carriersDTOs.industrial != null and carriersDTOs.industrial.size() > 0 ">
                    AND INDUSTRY_CATEGORY in
                    <foreach close=")" collection="carriersDTOs.industrial" index="index" item="item" open="(" separator=",">
                        #{item}
                    </foreach>
                </if>

就可以了。