mybatis 多条件查询、in查询

落日映苍穹つ 2023-08-17 16:19 126阅读 0赞

> 当参数有值，添加条件查询，附带一个字符串的in查询

*  resultMap：
    
        <resultMap id="eventMap" type="com.szkingdom.entityserver.vo.event.EventBean">
          <id column="id" jdbcType="BIGINT" property="id" />
          <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
          <result column="modify_time" jdbcType="TIMESTAMP" property="modifyTime" />
          <result column="create_user_id" jdbcType="BIGINT" property="createUserId" />
          <result column="modify_user_id" jdbcType="BIGINT" property="modifyUserId" />
          <result column="db_status" jdbcType="TINYINT" property="dbStatus" />
          <result column="remark" jdbcType="VARCHAR" property="remark" />
          <result column="event_name" jdbcType="VARCHAR" property="eventName" />
          <result column="event_code" jdbcType="VARCHAR" property="eventCode" />
          <result column="small_class_code" jdbcType="VARCHAR" property="smallClassCode" />
          <result column="big_class_code" jdbcType="VARCHAR" property="bigClassCode" />
          <result column="event_status" jdbcType="TINYINT" property="eventStatus" />
          <result column="village_code" jdbcType="VARCHAR" property="villageCode" />
          <result column="event_level" jdbcType="TINYINT" property="eventLevel" />
          <result column="event_resource" jdbcType="INTEGER" property="eventResource" />
          <result column="location" jdbcType="VARCHAR" property="location" />
          <result column="content" jdbcType="VARCHAR" property="content" />
          <result column="longitude" jdbcType="VARCHAR" property="longitude" />
          <result column="latitude" jdbcType="VARCHAR" property="latitude" />
          <result column="work_tache" jdbcType="VARCHAR" property="workTache" />
          <result column="work_flow_id" jdbcType="VARCHAR" property="workFlowId" />
          <result column="receive_time" jdbcType="TIMESTAMP" property="receiveTime"/>
          <result column="reporter_name" jdbcType="VARCHAR" property="reporterName"/>
          <result column="reporter_tel" jdbcType="VARCHAR" property="reporterTel"/>
          <result column="area_id" jdbcType="BIGINT" property="areaId"/>
          <result column="register_time" jdbcType="TIMESTAMP" property="registerTime"/>
          <result column="overTimePoint" jdbcType="TIMESTAMP" property="overTimePoint"/>
          <result column="curTacheName" jdbcType="VARCHAR" property="curTacheName"/>
          <association property="bigClass" javaType="com.szkingdom.entityserver.entity.pmi.BigClass">
            <result column="bigId" property="id"/>
            <result column="bigName" property="name"/>
            <result column="bigCode" property="code"/>
            <result column="bigType" property="bigType"/>
          </association>
          <association property="smallClass" javaType="com.szkingdom.entityserver.entity.pmi.SmallClass">
            <result column="smallId" property="id"/>
            <result column="smallName" property="name"/>
            <result column="smallCode" property="code"/>
          </association>
              <association property="createUser" javaType="com.szkingdom.entityserver.entity.pmi.User">
                <result column="userId" property="id"/>
                <result column="userName" property="userName"/>
              </association>
              <collection property="beforeAnnexeList" ofType="com.szkingdom.entityserver.entity.sys.Annexe" column="id" select="com.szkingdom.webserver.dao.file.AnnexeDAO.findEventBeforeAnnexe">
              </collection>
              <collection property="afterAnnexeList" ofType="com.szkingdom.entityserver.entity.sys.Annexe" column="id" select="com.szkingdom.webserver.dao.file.AnnexeDAO.findEventAfterAnnexe">
              </collection>
              <collection property="area" ofType="com.szkingdom.entityserver.entity.pmi.Area" column="area_id" select="com.szkingdom.webserver.dao.pmi.AreaMapper.findAreaVTreeById">
              </collection>
            </resultMap>
 *  Base\_Column\_List:
    
        <sql id="Base_Column_List">
          e.id, e.create_time, e.modify_time, e.create_user_id, e.modify_user_id, e.db_status,e.remark,
          e.event_name, e.event_code, e.small_class_code, e.big_class_code, e.event_status, e.village_code,
          e.event_level, e.event_resource, e.`location`, e.content, e.longitude, e.latitude, e.work_tache,
          e.work_flow_id,e.receive_time,e.reporter_name,e.reporter_tel,e.area_id,e.register_time,e.area_id
        </sql>
 *  查询的条件

<sql id="todoEventWhere">
       <trim prefix="where" suffixOverrides="and | or">
         
         (e.db_status = 1 ) and
    
         
         task.TASK_DEF_KEY_ != 'StopState' AND
    
         <if test="currentTacheCode != null and currentTacheCode != ''">
           task.TASK_DEF_KEY_ = #{currentTacheCode} AND
         </if>
         <if test="eventCode != null and eventCode != ''">
           e.event_code = #{eventCode} AND
         </if>
         <if test="bigClassCode != null and bigClassCode != ''">
           e.big_class_code = #{bigClassCode} AND
         </if>
         <if test="smallClassCode != null and smallClassCode != ''">
           e.small_class_code = #{smallClassCode} AND
         </if>
         <if test="eventResource != null and eventResource != ''">
           e.event_resource = #{eventResource} AND
         </if>
         <if test="eventLevel != null ">
           e.event_level = #{eventLevel} AND
         </if>
         <if test="receiveTimeStart != null">
           e.receive_time <![CDATA[>=]]> #{receiveTimeStart} AND
         </if>
         <if test="receiveTimeEnd != null">
           e.receive_time <![CDATA[<=]]> #{receiveTimeEnd} AND
         </if>
         <if test="registerTimeStart != null">
           e.register_time <![CDATA[>=]]> #{registerTimeStart} AND
         </if>
         <if test="registerTimeEnd != null">
           e.register_time <![CDATA[<=]]> #{registerTimeEnd} AND
         </if>
         <if test="reporterName != null and reporterName != ''">
           e.reporter_name LIKE "%"#{reporterName}"%" AND
         </if>
         <if test="reporterTel != null and reporterTel != ''">
           e.reporter_tel LIKE "%"#{reporterTel}"%" AND
         </if>
         <if test="location != null and location != ''">
           e.location LIKE "%"#{location}"%" AND
         </if>
         <if test="content != null and content != ''">
           e.content LIKE "%"#{content}"%" AND
         </if>
         <if test="remark != null and remark != ''">
           e.remark LIKE "%"#{remark}"%" AND
         </if>
         
         <if test="receiveRoleIds != null and receiveRoleIds != ''">
           ridentity.GROUP_ID_ in
           <foreach item="roleId" collection="receiveRoleIds.split(',')" open="(" separator="," close=")">
             #{roleId}
           </foreach>
         </if>
       </trim>
     </sql>

*  查询语句

<select id="findEventPage" resultMap="eventMap" parameterType="com.szkingdom.entityserver.dto.event.EventParam">
       SELECT
       <include refid="Base_Column_List" />,
       bc.id bigId, bc.name bigName, bc.code bigCode,bc.big_type bigType,
       sc.id smallId, sc.name smallName, sc.code smallCode,
      a.id areaId, a.area_name areaName, a.area_code areaCode,
       task.DUE_DATE_ overTimePoint,task.NAME_ curTacheName
       FROM
       evt_event e
       LEFT JOIN pmi_big_class bc ON e.big_class_code = bc.code
       LEFT JOIN pmi_small_class sc ON e.small_class_code = sc.code
       LEFT JOIN pmi_area a ON e.area_id = a.id
       LEFT JOIN act_ru_execution ruexe ON ruexe.BUSINESS_KEY_ = e.id
       LEFT JOIN act_ru_task task ON task.EXECUTION_ID_ = ruexe.ID_
       left join act_ru_identitylink ridentity on ridentity.TASK_ID_= task.ID_
       <include refid="todoEventWhere"/>
       ORDER BY e.event_level DESC,e.receive_time DESC
     </select>