sql示例:
SELECT
*,
CASE
WHEN push_rate = 1 THEN DATE_ADD(last_push_time, INTERVAL 1 HOUR)
WHEN push_rate = 2 THEN DATE_ADD(last_push_time, INTERVAL 6 HOUR)
WHEN push_rate = 3 THEN DATE_ADD(last_push_time, INTERVAL 24 HOUR)
ELSE NULL
END AS push_time
FROM table
WHERE
CASE
WHEN push_rate = 1 THEN DATE_ADD(last_push_time, INTERVAL 1 HOUR)
WHEN push_rate = 2 THEN DATE_ADD(last_push_time, INTERVAL 6 HOUR)
WHEN push_rate = 3 THEN DATE_ADD(last_push_time, INTERVAL 24 HOUR)
ELSE NULL
END >= NOW();
可以使用Mybatis Plus的lambda表达式方式将上述SQL语句改写为QueryWrapper的形式,具体代码如下:
QueryWrapper<DatPushProgress> datPushProgressQueryWrapper = new QueryWrapper<>();
datPushProgressQueryWrapper.select("*")
.apply("CASE WHEN push_rate = 1 THEN DATE_ADD(last_push_time, INTERVAL 1 HOUR) " +
"WHEN push_rate = 2 THEN DATE_ADD(last_push_time, INTERVAL 6 HOUR) " +
"WHEN push_rate = 3 THEN DATE_ADD(last_push_time, INTERVAL 24 HOUR) " +
"ELSE NULL END AS push_time")
.apply("CASE WHEN push_rate = 1 THEN DATE_ADD(last_push_time, INTERVAL 1 HOUR) " +
"WHEN push_rate = 2 THEN DATE_ADD(last_push_time, INTERVAL 6 HOUR) " +
"WHEN push_rate = 3 THEN DATE_ADD(last_push_time, INTERVAL 24 HOUR) " +
"ELSE NULL END >= NOW() ");
上述代码中,使用QueryWrapper的select方法选择所有字段,使用apply方法将CASE WHEN语句作为SQL片段添加到QueryWrapper中,并命名为push_time。在第二个apply方法中,同样使用CASE WHEN语句判断计算结果是否大于等于当前时间,并添加到QueryWrapper中。最终得到的datPushProgressQueryWrapper即为需要的QueryWrapper对象。
需要注意的是,apply方法可以将任意SQL片段添加到QueryWrapper中,但是使用时需要注意SQL注入问题。
可以使用Mybatis Plus的LambdaQueryWrapper将上述SQL语句改写为Lambda表达式的形式,具体代码如下:
LambdaQueryWrapper<DatPushProgress> datPushProgressLambdaQueryWrapper = new LambdaQueryWrapper<>();
datPushProgressLambdaQueryWrapper.select(DatPushProgress.class, info -> true)
.apply("CASE WHEN push_rate = 1 THEN DATE_ADD(last_push_time, INTERVAL 1 HOUR) " +
"WHEN push_rate = 2 THEN DATE_ADD(last_push_time, INTERVAL 6 HOUR) " +
"WHEN push_rate = 3 THEN DATE_ADD(last_push_time, INTERVAL 24 HOUR) " +
"ELSE NULL END AS push_time")
.apply("CASE WHEN push_rate = 1 THEN DATE_ADD(last_push_time, INTERVAL 1 HOUR) " +
"WHEN push_rate = 2 THEN DATE_ADD(last_push_time, INTERVAL 6 HOUR) " +
"WHEN push_rate = 3 THEN DATE_ADD(last_push_time, INTERVAL 24 HOUR) " +
"ELSE NULL END >= NOW() ");
上述代码中,使用LambdaQueryWrapper的select方法选择所有字段,并使用info -> true作为条件函数,表示选择所有记录。使用apply方法将CASE WHEN语句作为SQL片段添加到LambdaQueryWrapper中,并命名为push_time。在第二个apply方法中,同样使用CASE WHEN语句判断计算结果是否大于等于当前时间,并添加到LambdaQueryWrapper中。最终得到的datPushProgressLambdaQueryWrapper即为需要的LambdaQueryWrapper对象。
需要注意的是,apply方法可以将任意SQL片段添加到LambdaQueryWrapper中,但是使用时需要注意SQL注入问题。