当前位置：首页 > news >正文

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

news 2024/10/22 9:55:38

前言

对于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_HISTwhere 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.usernameFROM orders oLEFT 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>