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 < ?)