SpringCloud微服务踩坑系列：QueryWrapper中的AND和OR

本来是要查询乘客有没有正在进行中的订单，结果变成了数据库里有没有正在进行中的订单

代码如下：

private int getPassengerOrderGoingOn(Integer passengerId) {
    QueryWrapper<OrderInfo> queryWrapper = new QueryWrapper();
    queryWrapper.eq("passenger_id", passengerId);
    queryWrapper.eq("order_status", OrderConstants.START)
        .or().eq("order_status", OrderConstants.DRIVER_RECEIVE_ORDER)
        .or().eq("order_status", OrderConstants.DRIVER_RECEIVE_ORDER)
        .or().eq("order_status", OrderConstants.DRIVER_TO_PICK_UP_PASSENGER)
        .or().eq("order_status", OrderConstants.DRIVER_ARRIVED_DEPARTURE)
        .or().eq("order_status", OrderConstants.PICK_UP_PASSENGER)
        .or().eq("order_status", OrderConstants.PASSENGER_GETOFF)
        .or().eq("order_status", OrderConstants.TO_START_PAY);
    Integer count = orderInfoMapper.selectCount(queryWrapper);
    return count;

}

打印出来的SQL语句

==>  Preparing: SELECT COUNT( * ) FROM order_info WHERE (passenger_id = ? AND order_status = ? OR order_status = ? OR order_status = ? OR order_status = ? OR order_status = ? OR order_status = ? OR order_status = ? OR order_status = ?)
==> Parameters: 3(Integer), 1(Integer), 2(Integer), 2(Integer), 3(Integer), 4(Integer), 5(Integer), 6(Integer), 7(Integer)
<==    Columns: COUNT( * )
<==        Row: 1
<==      Total: 1

显然不符合预期，后面只要有一个OR成立，则整个整理，前面的passenger_id 就变成了被忽略的条件，不会真正用来过滤

根据SQL语句，我们应该改为类似这种：

SELECT COUNT( * ) FROM order_info WHERE (passenger_id = ? AND （order_status = ? OR order_status = ? OR order_status = ? OR order_status = ? OR order_status = ? OR order_status = ? OR order_status = ? OR order_status = ?)）

那代码里如何实现呢？

private int getPassengerOrderGoingOn(Integer passengerId) {
        QueryWrapper<OrderInfo> queryWrapper = new QueryWrapper();
        queryWrapper.eq("passenger_id", passengerId);
        queryWrapper.and(wrapper-> wrapper.eq("order_status", OrderConstants.START)
            .or().eq("order_status", OrderConstants.DRIVER_RECEIVE_ORDER)
            .or().eq("order_status", OrderConstants.DRIVER_RECEIVE_ORDER)
            .or().eq("order_status", OrderConstants.DRIVER_TO_PICK_UP_PASSENGER)
            .or().eq("order_status", OrderConstants.DRIVER_ARRIVED_DEPARTURE)
            .or().eq("order_status", OrderConstants.PICK_UP_PASSENGER)
            .or().eq("order_status", OrderConstants.PASSENGER_GETOFF)
            .or().eq("order_status", OrderConstants.TO_START_PAY));
        Integer count = orderInfoMapper.selectCount(queryWrapper);
        return count;

    }

现在的SQL语句是这样的：

JDBC Connection [HikariProxyConnection@1924856446 wrapping com.mysql.jdbc.JDBC4Connection@1114e0d5] will not be managed by Spring
==>  Preparing: SELECT COUNT( * ) FROM order_info WHERE (passenger_id = ? AND (order_status = ? OR order_status = ? OR order_status = ? OR order_status = ? OR order_status = ? OR order_status = ? OR order_status = ? OR order_status = ?))
==> Parameters: 3(Integer), 1(Integer), 2(Integer), 2(Integer), 3(Integer), 4(Integer), 5(Integer), 6(Integer), 7(Integer)

符合预期