之前的公司对于数据库访问使用的框架是Mybatis，现在公司的系统比较老，用的是ibatis。复习一下Mybatis的动态标签，同时学习一下ibatis框架的动态标签方式。

<if test="条件">：根据条件动态动态拼装sql

<where>: 可以自动补足多个判断条件之间的and，并删除开头位置多余的and

示例

select id,clbh,cph,gpszd,cllx,ssdw,clzt,zrr,zrrdh,bz from l_clgl_clxx
<where>
    is_deleted = 0
    <if test="entity.fireBrigade != null and entity.fireBrigade != '' ">
        and ssdw like concat('%',#{entity.fireBrigade},'%')
    </if>
    <if test="entity.plateNumber != null and entity.plateNumber != '' ">
        and cph like concat('%',#{entity.plateNumber},'%')
    </if>
    <if test="entity.state != null and entity.state != '' ">
        and clzt like concat('%',#{entity.state},'%')
    </if>
    <if test="entity.type != null and entity.type != '' ">
        and cllx = #{entity.type}
    </if>
</where>

<set>： 会自动去掉为空选项的添加记录

示例

update customer
<set>
    <if test="customerName!=null"> customer_name = #{customerName}, </if>
    <if test="age!=null">age = #{age}</if>
</set>
where id = #{id}

<trim>：

prefix="":前缀，prefix给拼串后的整个字符串加一个前缀
prefixOverrides=""：前缀覆盖，去掉整个字符串前面多余的字符
suffix=""：后缀，suffix给拼串后的整个字符串加一个后缀
suffixOverrides=""：后缀覆盖，去掉整个字符串后面多余的字符

示例

<select id="getCustomerTRIM" resultType="bean.Customer">
    select * from customer
    <trim prefix="where" suffixOverrides="and">
        <if test="id != null"> id = #{id} and
        <it test="customerName != null and customerName != &quot;&quot;">
            customer_name = #{customerName} and
        </if>
        <it test="age != 0 and age != null">
            age = #{age}
        </if>
    </trim>
</select>

<choose>： 分支选择

示例

select * from customer
<where>
    <choose>
        <when test="id != null"> id = #{id} </when>
        <when test="customerName != null"> customer_name = #{customerName} </when>
        <otherwise> age = #{age} </otherwise>
    </choose>
</where>

<foreach>

• collection：指定要遍历的集合(list类型的参数会特殊处理封装在map，map的key就叫list)
• item：将当前遍历出的元素赋值给指定的变量
• separator：每个元素之间的分隔符
  -- open：遍历出所有结果拼接一个开始的字符
• close：遍历出所有结果拼接一个结束的字符
• index：索引，遍历list时，index为索引，item为当前值(遍历map时，index为key值，item为value)
• '#{变量名}'：当前遍历的元素

示例

1.
<insert>
    insert into patrol_push_msg (id, task_id, user_id, duty, create_user, create_time, is_deleted) values
    <foreach collection="list" item="item" separator=",">
        (uuid(), #{item.taskId}, #{item.userId}, #{item.duty}, #{item.createUser}, now(), 0)
    </foreach>
</insert>
2.
<delete id="deleteRepeatRecord">
    delete from l_rwgl_dbsj where rwid in
    <foreach collection="list" item="record" open="(" close=")" separator=",">
        #{record.rwid}
    </foreach>
</delete>

<resultMap>的语法举例：

<resultMap id="patrolMap" type="com.jxdinfo.hussar.cloud.dto.patrol.PatrolRegisterDto">
    <id column="patrolNo"/>
    <result column="patrolNo" property="patrolNo"/>
    <result column="compliedUser" property="compliedUser"/>
    <result column="startTime" property="patrolTime"/>
    <collection property="patrolItemList" ofType="com.jxdinfo.hussar.cloud.dto.patrol.PatrolItemDto">
        <id column="patrolNo"/>
        <id column="patrolItemId"/>
        <result column="patrolItemId" property="patrolItemId"/>
        <result column="name" property="name"/>
        <result column="state" property="state"/>
    </collection>
</resultMap>

<select id="getPatrolDetail" resultMap="patrolMap">
    select patrolNo, compliedUser, startTime, patrol_item.id patrolItemId, name, state
    from patrolregist left join patrol_item
        on patrolregist.patrolNo = patrol_item.patrolId and patrol_item.is_deleted = 0
    where patrolregist.id = #{id}
</select>

<resultMap id="patrolItemMap" type="com.jxdinfo.hussar.cloud.dto.patrol.PatrolItemDto">
    <id column="itemId"/>
    <result column="itemId" property="patrolItemId"/>
    <result column="name" property="name"/>
    <result column="state" property="state"/>
    <result column="itemIfReport" property="ifReport"/>
    <collection property="patrolLocationList" resultMap="patrolLocationMap"/>
</resultMap>
<resultMap id="patrolLocationMap" type="com.jxdinfo.hussar.cloud.dto.patrol.PatrolLocationDto">
    <id column="itemId"/>
    <id column="locId"/>
    <result column="locId" property="id"/>
    <result column="taskNo" property="taskNo"/>
    <result column="issue_description" property="issueDescription"/>
    <result column="locPatrolTime" property="patrolTime"/>
    <result column="location" property="location"/>
    <result column="locIfReport" property="ifReport"/>
    <collection property="imageIdList" ofType="com.jxdinfo.hussar.cloud.dto.patrol.ImageItemDto">
        <id column="itemId"/>
        <id column="locId"/>
        <id column="FILE_ID"/>
        <result column="FILE_ID" property="file_id"/>
    </collection>
</resultMap>

<select id="getPatrolItemDetail" resultMap="patrolItemMap">
    select patrol_item.id itemId, patrol_item.name, patrol_item.state, patrol_item.ifReport itemIfReport,
           patrol_location.id locId, patrol_location.task_no taskNo, patrol_location.issue_description,
           patrol_location.patrol_time locPatrolTime, patrol_location.location, patrol_location.if_report locIfReport, FILE_ID
    from patrol_item
        left join patrol_location on patrol_item.id = patrol_location.patrol_item_id and patrol_item.is_deleted = 0
        left join sys_file_info on patrol_location.id = sys_file_info.ID
    where patrol_item.id = #{patrolItemId}
</select>

ibatis相关语法

ibatis使用like模糊查询

批量插入

<insert id="waiter.insertBatch" parameterClass="java.util.List">
    <![CDATA[
        insert into rc_waiter (account, emp_id, data_time) values
    ]]>
    <iterate conjunction ="," >
        <![CDATA[(now(), now(),
            #hello[].account#,
            #hello[].empId#,
            #hello[].dataTime#)
        ]]>
    </iterate>
</insert>