mybatis的mapper.xml文件编写注意点小结

生成的sql语句

在mapper.xml中编写的sql语句可以在控制台的debug级别日志检索

可见mapper.xml中的sql语句是以PrepareStatement对象以占位符的方式实现对数据库的交互,换而言之就是对JDBC的一种薄封装,也就是只关注sql语句本身

新增操作

<!-- 新增     -->
    <insert id="insertSupplierLabel" parameterType="com.tbi.service.base.supplier.entity.SupplierLabelEntity">
        INSERT INTO
        T_SUPPLIER_LABEL
        (
        LABEL_ID ,
        <if test="supplierId != null and supplierId !='' ">  SUPPLIER_ID , </if>
        LABEL_NAME ,
        CREATE_USER_ID ,
        CREATE_USER_NAME ,
        CREATE_TIME ,
        DEL_FLAG
        )
        VALUES
        (
        #{labelId},
        <if test="supplierId != null and supplierId !='' "> #{supplierId}, </if>
        #{labelName},
        #{createUserId},
        #{createUserName},
        now(4),
        0
        )
    </insert>

<!-- 新增     -->
    <insert id="insertSupplierLabel" parameterType="com.tbi.service.base.supplier.entity.SupplierLabelEntity">
        INSERT INTO
        T_SUPPLIER_LABEL
        (
        LABEL_ID ,
        LABEL_NAME ,
        CREATE_USER_ID ,
        CREATE_USER_NAME ,
        CREATE_TIME ,
        DEL_FLAG ,
        <if test="supplierId != null and supplierId !='' ">  SUPPLIER_ID  </if>
        )
        VALUES
        (
        #{labelId},
        #{labelName},
        #{createUserId},
        #{createUserName},
        now(4),
        0,
        <if test="supplierId != null and supplierId !='' "> #{supplierId} </if>
        )
    </insert>

两张图中SUPPLIER_ID的位置不同,看起来是没有太大区别的,但是SUPPLIER_ID没有在业务逻辑层传入时sql语句就不会显示该字段,导致DEL_FLAG后多了一个逗号,导致报错.

{
  "header": {
    "msg": "error|\r\n### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')\n        VALUES\n        (\n        'SUP-LABEL20191230000001',\n        '星级客' at line 11\r\n### The error may involve com.tbi.service.base.supplier.mapper.SupplierLabelMapper.insertSupplierLabel-Inline\r\n### The error occurred while setting parameters\r\n### SQL: INSERT INTO         T_SUPPLIER_LABEL         (         LABEL_ID ,         LABEL_NAME ,         CREATE_USER_ID ,         CREATE_USER_NAME ,         CREATE_TIME ,         DEL_FLAG ,                   )         VALUES         (         ?,         ?,         ?,         ?,         now(4),         0,                   )\r\n### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')\n        VALUES\n        (\n        'SUP-LABEL20191230000001',\n        '星级客' at line 11\n; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')\n        VALUES\n        (\n        'SUP-LABEL20191230000001',\n        '星级客' at line 11",
    "code": "1"
  },
  "body": {}
}

解决方法:将必填项移动至插入前段及插入后段的最后一位防止sql语句报错

修改

<update id="updateSupplierLabel" parameterType="com.tbi.service.base.supplier.entity.SupplierLabelEntity">
        update
        T_SUPPLIER_LABEL
        set
        <if test="labelId != null and labelId !='' "> LABEL_ID = #{labelId}, </if>
        <if test="supplierId != null and supplierId !='' "> SUPPLIER_ID = #{supplierId}, </if>
        <if test="labelName != null and labelName !='' "> LABEL_NAME = #{labelName}, </if>
        <if test="updateUserId != null and updateUserId !='' "> UPDATE_USER_ID = #{updateUserId}, </if>
        <if test="updateUserName != null and updateUserName !='' "> UPDATE_USER_NAME = #{updateUserName}, </if>
        <if test="delFlag != null and delFlag !='' ">DEL_FLAG = #{delFlag},</if>
        update_time = now(4)
        where
        1=1
        <if test="labelId != null and labelId !='' "> and LABEL_ID = #{labelId} </if>
    </update>

修改时的注意事项类型与插入操作,在where条件可以加入恒等式1=1方式sql语句报错

查询

<!-- 查询 -->
    <select id="queryCommodityValuation"
            parameterType="com.tbi.service.sale.commodityValuation.entity.CommodityValuationEntity"
            resultType="com.tbi.service.sale.commodityValuation.entity.CommodityValuationEntity">
        SELECT
        <include refid="columnName"/>,
        DATE_FORMAT(a.START_TIME,'%Y-%m-%d ') AS startDate,
        DATE_FORMAT(a.END_TIME,'%Y-%m-%d ') AS endDate,
        s.`STORE_NAME` as "storeName"
        FROM
        T_COMMODITY_VALUATION a
        LEFT JOIN t_store s
        ON a.`STORE_ID` = s.`STORE_ID`
        where
        1=1
        <choose>
            <when test="delFlag != null and delFlag !='' ">
                and a.del_flag=#{delFlag}
            </when>
            <otherwise>
                and a.del_flag=0
            </otherwise>
        </choose>
        <if test="valuationId != null and valuationId !='' ">and a.VALUATION_ID = #{valuationId}</if>
        <if test="sobId != null and sobId !='' ">and a.SOB_ID = #{sobId}</if>
        <if test="projectId != null and projectId !='' ">and a.PROJECT_ID = #{projectId}</if>
        <if test="taskId != null and taskId !='' ">and a.TASK_ID = #{taskId}</if>
        <if test="eventId != null and eventId !='' ">and a.EVENT_ID = #{eventId}</if>
        <if test="approvalId != null and approvalId !='' ">and a.APPROVAL_ID = #{approvalId}</if>
        <if test="sourceType != null and sourceType !='' ">and a.SOURCE_TYPE = #{sourceType}</if>
        <if test="sourceId != null and sourceId !='' ">and a.SOURCE_ID = #{sourceId}</if>
        <if test="valuationName != null and valuationName !='' ">and a.VALUATION_NAME = #{valuationName}</if>
        <if test="storeId != null and storeId !='' ">and a.STORE_ID = #{storeId}</if>
        <if test="enableFlag != null and enableFlag !='' ">and a.ENABLE_FLAG = #{enableFlag}</if>
        <if test="description != null and description !='' ">and a.DESCRIPTION = #{description}</if>
        <if test="allCustomer != null and allCustomer !='' ">and a.ALL_CUSTOMER = #{allCustomer}</if>
        <if test="approvalType != null and approvalType !='' ">and a.APPROVAL_TYPE = #{approvalType}</if>
        <if test="currentState != null and currentState !='' ">and a.CURRENT_STATE = #{currentState}</if>
        <if test="isSuccess != null and isSuccess !='' ">and a.IS_SUCCESS = #{isSuccess}</if>
        <if test="successCode != null and successCode !='' ">and a.SUCCESS_CODE = #{successCode}</if>

        <if test="screenStartTimeHead !=null and screenStartTimeHead !=''">
            AND a.START_TIME <![CDATA[>=]]> #{screenStartTimeHead,jdbcType=VARCHAR}
        </if>
        <if test="screenStartTimeTail !=null and screenStartTimeTail !=''">
            AND a.START_TIME <![CDATA[<=]]> #{screenStartTimeTail,jdbcType=VARCHAR}
        </if>

        <if test="screenEndTimeHead !=null and screenEndTimeHead !=''">
            AND a.END_TIME <![CDATA[>=]]> #{screenEndTimeHead,jdbcType=VARCHAR}
        </if>
        <if test="screenEndTimeTail !=null and screenEndTimeTail !=''">
            AND a.END_TIME <![CDATA[<=]]> #{screenEndTimeTail,jdbcType=VARCHAR}
        </if>

        <if test="startTimeSort != null and startTimeSort !='' ">order by a.START_TIME ${startTimeSort}</if>
        <if test="endTimeSort != null and endTimeSort !='' ">order by a.END_TIME ${endTimeSort}</if>
    </select>

首先select字段as关键字后的字段对于实体类的属性名
若没有对应可能导致实体类接收不到数据库查询结果集的相关数据
其次数据库表别名对应的字段需分别表示,如

DATE_FORMAT(a.START_TIME,’%Y-%m-%d ‘) AS startDate,
DATE_FORMAT(a.END_TIME,’%Y-%m-%d ') AS endDate,
s.STORE_NAME as “storeName”

然后多表查询where条件下的语句如果在select没有明确表示的话需要指定参数所属数据库表否则报错

SELECT 
  pps.id,
  pps.category,
  pps.type,
  pps.params,
  pps.unit,
  pp.`name` 
FROM
  prj_purchase_standard pps 
  INNER JOIN prj_project pp 
    ON pps.project_id = pp.`id` 
WHERE 1 = 1 
  AND TYPE = 1 

最后一行的type没有指定数据库表报且未在select中指明报如下错误

错误代码： 1052
Column 'type' in where clause is ambiguous