mybatis 多条件查询、in查询

当参数有值，添加条件查询，附带一个字符串的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>
     <!-- receiveRoleIds是一个字符串 -->
     <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>