mybatis中的lt、le、eq、ne、ge、gt

lt、le、eq、ne、ge、gt

• lt：less than 小于
• le：less than or equal to 小于等于
• eq：equal to 等于
• ne：not equal to 不等于
• ge：greater than or equal to 大于等于
• gt：greater than 大于

查看mybatis的SQL语句

System.out.println(sysBeforeUserQueryWrapper.getSqlSegment());
System.out.println(sysBeforeUserQueryWrapper.getTargetSql()); 

(id LIKE #{ew.paramNameValuePairs.MPGENVAL1} AND end_time IS NULL OR end_time < #{ew.paramNameValuePairs.MPGENVAL2} AND account LIKE #{ew.paramNameValuePairs.MPGENVAL3}) ORDER BY create_time DESC 
(id LIKE ? AND end_time IS NULL OR end_time < ? AND account LIKE ?) ORDER BY create_time DESC  

使用or()导致查询结果错误

当使用如下语句时查询的结果有偏差甚至查询失败：

Date now = new Date(); 
if (pageParam.getIsVip().equals("YES")) {
    sysBeforeUserQueryWrapper.lambda().ge(SysBeforeUser::getEndTime, now);
} else {
    sysBeforeUserQueryWrapper.lambda().isNull(SysBeforeUser::getEndTime).or().lt(SysBeforeUser::getEndTime, now);
} 

或者：

SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-DD HH:mm:ss");

sysBeforeUserQueryWrapper.lambda().ge(SysBeforeUser::getEndTime, simpleDateFormat.format(now));

sysBeforeUserQueryWrapper.lambda().isNull(SysBeforeUser::getEndTime).or().lt(SysBeforeUser::getEndTime, simpleDateFormat.format(now));
}

分情况分析：
① 查询结果正确的SQL语句（or条件后面还有and条件）：

 ==>  Preparing: SELECT COUNT(*) AS total FROM sys_before_user WHERE (id LIKE ? AND end_time IS NULL OR end_time < ? AND account LIKE ?)
 ==> Parameters: %744990042990178305%(String), 2024-01-10 16:03:07.003(Timestamp), %7307453%(String) 

或者：

 ==>  Preparing: SELECT COUNT(*) AS total FROM sys_before_user WHERE (id LIKE ? AND end_time IS NULL OR end_time < ? AND account LIKE ?)
 ==> Parameters: %744990042990178305%(String), 2024-01-10 16:01:21(String), %7307453%(String)

② 查询失败的SQL语句（or条件后没有约束条件了）：

 ==>  Preparing: SELECT COUNT(*) AS total FROM sys_before_user WHERE (id LIKE ? AND end_time IS NULL OR end_time < ?)
 ==> Parameters: %744990042990178305%(String), 2024-01-10 16:04:50.018(Timestamp) 

或者：

 ==>  Preparing: SELECT COUNT(*) AS total FROM sys_before_user WHERE (id LIKE ? AND end_time IS NULL OR end_time < ?)
 ==> Parameters: %744990042990178305%(String), 2024-01-10 16:06:35(String) 

分析以上成功或失败的原因：
查看以下语句可知查询条件先后顺序会影响结果：
1、对于情况① （id like ?、account like ?、end_time < ? 或 end_time IS NULL）应当是：

SELECT COUNT(*) AS total FROM sys_before_user WHERE (id LIKE ? AND (end_time IS NULL OR end_time < ?)) AND account LIKE ? 

不过，SELECT COUNT(*) AS total FROM sys_before_user WHERE (id LIKE ? AND end_time IS NULL OR end_time < ? AND account LIKE ?)也是正确的。
2、对于情况② 则完全错误（id like ?、end_time < ? 或 end_time IS NULL），应当是：

SELECT COUNT(*) AS total  FROM sys_before_user  WHERE (id LIKE ? AND (end_time IS NULL OR end_time < ?))

问题主要是出在 end_time 参数中，该参数得条件应该用括号包裹，即 (end_time IS NULL OR end_time < ?) 才对！

sysBeforeUserQueryWrapper.lambda().and(wrapper -> wrapper.isNull(SysBeforeUser::getEndTime).or().lt(SysBeforeUser::getEndTime, now)); 

对应的SQL语句条件如下：

SELECT * FROM your_table WHERE some_other_conditions AND (end_time IS NULL OR end_time < ?)