mybatis常用语法

mybatis常用语法

数组foreach   IN

<if test="metadataCategorys != null and metadataCategorys.length>0">

    AND d.metadata_category IN

    <foreach collection="metadataCategorys" item="mcs" open="(" separator="," close=")">

        #{mcs}

    </foreach>

</if>

list foreach   IN

<if test="metadataCategorys != null and metadataCategorys.size>0">

    AND d.metadata_category IN

    <foreach collection="metadataCategorys" item="mcs" open="(" separator="," close=")">

        #{mcs}

    </foreach>

</if>

判断日期

<if test="startDate != null and startDate != '' ">

    ANDDATE_FORMAT(j.last_sync_time,'%Y-%m-%d')>= #{startDate}

</if>

<if test="endDate != null and endDate != '' ">

    ANDDATE_FORMAT(j.last_sync_time,'%Y-%m-%d')<= #{endDate}

</if>

模糊匹配

<if test="metadataName != null and metadataName != '' ">

    and cr.entity_name like CONCAT('%',#{metadataName},'%' ) escape '/'

</if>

批量update

<foreach collection="assessDetailUpdates" item="assessDetailUpdate" separator=";" >

    UPDATE

        assess_detail

    SET

        avg = #{assessDetailUpdate.avg},

        attendance = #{assessDetailUpdate.attendance},

        score = #{assessDetailUpdate.score},

        updated_account = #{assessDetailUpdate.updatedAccount},

        updated_time = #{assessDetailUpdate.updatedTime}

    WHERE id = #{assessDetailUpdate.id}

</foreach>

中文首字母

如果数据库表字段的字符编码是utf8_general_ci（常用）

select * from tablename WHERE 1=1 order byCONVERT( name USING gbk )COLLATE gbk_chinese_ci ASC