IDEA奇淫技巧之配置MySQL，DataBase导航栏与**Mapper.xml联动纠错（SQL字段是否匹配），利用IDEA辅助校验Mapper文件中的SQL字段是否拼写正确

IDEA奇淫技巧之database导航栏 会自动校验SQL语句字段与数据库的匹配情况，字段匹配正确自动变成紫色，错误右侧会有红线，正确单击有响应！

IDEA 未连接数据库时，***mapper.xml文件中SQL语句的颜色

我们连接后再看看 

  配置好数据连接信息

 测试是否连接成功

 

 然后apply、OK，此时的**mapper.xml，此时文件颜色尚无变化！还需下面的操作，进行IDEA配置。有的IDEA默认MySQL 作为工程数据库，此时若文件有变化，可以不进行相关的配置。

还需一步配置或者快捷键 CTRL+ALT+S

 配置SQL

 然后点击apply、ok按钮、再来看看**mapper.xxml文件

concat函数，模糊查询时需要注意单个字符为'%'和'_'的情况，否则会全匹配，控制层参数需要进行相关拦截！ 

此时单击高亮(紫色)字段 会跳到右侧相关表结构上

 低版本的IDEA会将这句SQL的DDL以单独的页签打开！

 上述配置完成之后，如果字段错误就很明显了，但是也有不够完善的地方、子标签中的字段无法校验，limit 1会误报错...忽略这些欠缺的，还是能帮助开发人员避免数据字段或者SQL字段错误。值得推荐使用这个功能。

顺便贴下mapper

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.iot.system.mapper.IotCategoryMapper">

    <resultMap type="IotCategory" id="IotCategoryResult">
        <result property="categoryId" column="category_id"/>
        <result property="categoryName" column="category_name"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="remark" column="remark"/>
    </resultMap>

    <sql id="selectIotCategoryVo">
        select
            category_id,
            category_name,
            create_by,
            create_time,
            update_by,
            update_time,
            remark
        from iot_category
    </sql>

    <select id="selectIotCategoryList" parameterType="IotCategory" resultMap="IotCategoryResult">
        <include refid="selectIotCategoryVo"/>
        <where>
            del_flag= '0'
            <if test="categoryName != null  and categoryName != ''">
                and category_name like concat('%', #{categoryName}, '%')
            </if>
        </where>
    </select>

    <select id="selectIotCategoryById" parameterType="Long" resultMap="IotCategoryResult">
        <include refid="selectIotCategoryVo"/>
        where category_id = #{categoryId}
    </select>

    <insert id="insertIotCategory" parameterType="IotCategory" useGeneratedKeys="true" keyProperty="categoryId">
        insert into iot_category
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="categoryName != null and categoryName != ''">category_name,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="remark != null">remark,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="categoryName != null and categoryName != ''">#{categoryName},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="remark != null">#{remark},</if>
        </trim>
    </insert>

    <update id="updateIotCategory" parameterType="IotCategory">
        update iot_category
        <trim prefix="SET" suffixOverrides=",">
            <if test="categoryName != null and categoryName != ''">category_name = #{categoryName},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="remark != null">remark = #{remark},</if>
        </trim>
        where category_id = #{categoryId}
    </update>

    <delete id="deleteIotCategoryById" parameterType="Long">
        update iot_category
        set del_flag = '2'
        where category_id = #{categoryId}
    </delete>

    <delete id="deleteIotCategoryByIds" parameterType="String">
        update iot_category set del_flag= '2' where category_id in
        <foreach item="categoryId" collection="array" open="(" separator="," close=")">
            #{categoryId}
        </foreach>
    </delete>
</mapper>