Mybatis 中＜where＞的用法注意事项（附Demo）

前言

对于Java的基本知识推荐阅读：

java框架零基础从入门到精通的学习路线附开源项目面经等（超全）
【Java项目】实战CRUD的功能整理（持续更新）

先从实战问题中进行分析，在分析相关的基本知识

1. 问题所示

执行后端代码的时候，网页出现如下提示：

axios.js:72  Uncaught (in promise) Error: 
### Error querying database.  Cause: java.sql.SQLException: sql injection violation, dbType mysql, druid-version 1.2.22, select alway true condition not allow : SELECT COUNT(*) FROM (SELECT s.spreader_no, s.spreader_model, d.check_date, DATEDIFF(CURDATE(), d.check_date) AS check_days_between_last, d.end_lock_cumulative_box_quantity, d.end_lock_cumulative_box_quantity - d.last_endLock_cumulative_box_quantity AS end_lock_used_quantity, d.end_lock_replace_date, DATEDIFF(CURDATE(), d.end_lock_replace_date) AS end_lock_replace_days, d.last_endLock_cumulative_box_quantity, d.middle_lock_cumulative_box_quantity, d.middle_lock_cumulative_box_quantity - d.last_middle_lock_cumulative_box_quantity AS middle_lock_used_quantity, d.middle_lock_replace_date, DATEDIFF(CURDATE(), d.middle_lock_replace_date) AS middle_lock_replace_days, d.last_middle_lock_cumulative_box_quantity FROM equipment_spreader AS s JOIN equipment_spreader_running_data AS d ON s.id = d.spreader_id AND '1' = '1' WHERE s.is_deleted = 0 AND d.is_deleted = 0) AS t WHERE 1 = 1
### The error may exist in file [F:\java_project\BladeX-Biz\blade-service\blade-equipment\target\classes\org\springblade\equipment\mapper\SpreaderRunningDataMapper.xml]
### The error may involve org.springblade.equipment.mapper.SpreaderRunningDataMapper.queryCount
### The error occurred while executing a query

截图如下：

在这里插入图片描述

给一个Demo示例：

这个原本是正确的，适应低版本

<select id="listView" resultType="org.springblade.equipment.vo.DeviceScheduleHistVO">
    SELECT * FROM E_DEVICE_SCHEDULE_HIST
    where 1=1
        <if test="deviceScheduleHistVO.lowCapacityDeviceName != null and deviceScheduleHistVO.lowCapacityDeviceName != ''">
             AND LOW_CAPACITY_DEVICE_NAME = #{deviceScheduleHistVO.lowCapacityDeviceName}
        </if>
        <if test="deviceScheduleHistVO.startTime != null">
            and CREATE_TIME &gt; #{deviceScheduleHistVO.startTime}
        </if>
        <if test="deviceScheduleHistVO.endTime != null" >
            and CREATE_TIME &lt;= #{deviceScheduleHistVO.endTime}
        </if>
    order by SEND_TO_DRIVER ASC, CREATE_TIME DESC
</select>

最终应该修改为：（与版本差异有关，后续版本使用<where>保险一点）

<select id="listView" resultType="org.springblade.equipment.vo.DeviceScheduleHistVO">
    SELECT * FROM E_DEVICE_SCHEDULE_HIST
    <where>
        <if test="deviceScheduleHistVO.lowCapacityDeviceName != null and deviceScheduleHistVO.lowCapacityDeviceName != ''">
             LOW_CAPACITY_DEVICE_NAME = #{deviceScheduleHistVO.lowCapacityDeviceName}
        </if>
        <if test="deviceScheduleHistVO.startTime != null">
            and CREATE_TIME &gt; #{deviceScheduleHistVO.startTime}
        </if>
        <if test="deviceScheduleHistVO.endTime != null" >
            and CREATE_TIME &lt;= #{deviceScheduleHistVO.endTime}
        </if>
    </where>
    order by SEND_TO_DRIVER ASC, CREATE_TIME DESC
</select>

2. 基本知识

MyBatis 的 XML 文件中，<where> 标签是用来动态构建 WHERE 子句的，它可以根据传入的条件在运行时自动拼接 SQL 语句，并且避免不必要的语法问题

基本的用法如下：在一个 SELECT 语句中，包裹所有动态条件，它通过内部的 <if> 标签来动态添加查询条件

<select id="selectUsers" resultType="User">
    SELECT *
    FROM users
    <where>
        <if test="username != null and username != ''">
            username = #{username}
        </if>
        <if test="age != null">
            AND age = #{age}
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
    </where>
</select>

常见的错误如下：<where> 标签内的 ORDER BY 错误
<where> 标签仅用于条件过滤，不能用于排序
ORDER BY 语句必须位于 SELECT 语句的最后，不应放在 <where> 中

错误的写法如下：

<where>
    <if test="username != null">
        username = #{username}
    </if>
    ORDER BY username DESC
</where>

正确的写法应将 ORDER BY 放在外部：

<where>
    <if test="username != null">
        username = #{username}
    </if>
</where>
ORDER BY username DESC

在更复杂的查询中，比如多表查询或分页查询，<where> 依然是构建条件的重要部分

多表查询

<select id="selectUserOrders" resultType="Order">
    SELECT o.id, o.total, u.username
    FROM orders o
    LEFT JOIN users u ON o.user_id = u.id
    <where>
        <if test="u.username != null">
            u.username = #{username}
        </if>
        <if test="o.total != null">
            AND o.total >= #{total}
        </if>
    </where>
</select>

分页查询

<select id="selectUsersByPage" resultType="User">
    SELECT *
    FROM users
    <where>
        <if test="username != null">
            username = #{username}
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
    </where>
    LIMIT #{pageNum}, #{pageSize};
</select>

使用多个排序条件

<select id="getOrderedEquipment" resultType="EquipmentVO">
    SELECT *
    FROM equipment
    <where>
        <if test="status != null">
            status = #{status}
        </if>
    </where>
    <if test="sortBy == 'name'">
        ORDER BY name ASC
    </if>
    <if test="sortBy == 'createdDate'">
        ORDER BY created_date DESC
    </if>
</select>