如何实现后端开发框架（五）-单表自定义查询

如何实现后端开发框架（五）-单表自定义查询

1. 问题描述

Mybatis-Plus使用QueryWrapper实现了单表中条件查询的基础功能，但是当查询条件很多，或者多个查询条件之间是“OR”关系，而不是“AND”关系时，QueryWrapper相关代码写起来也比较繁琐，能不能进一步简化代码实现呢？

2. 实现思路

针对QueryWrapper相关代码实现进一步封装和简化，让开发人员使用更方便。

3. 实现步骤

3.1 查询条件封装类

此类主要封装查询条件的相关信息，包括查询字段名（columnName）,查询字段值（value），查询SQL操作符（sqlOperator）,多个查询条件之间的“AND”或者“OR”关系（linkOperator）。

public class SqlParam {
  private String columnName;
  private Object value;
  private SqlOperator sqlOperator;
  private SqlOperator linkOperator;

  public SqlParam() {}

  public SqlParam(String columnName, SqlOperator sqlOperator) {
    this.columnName = columnName;
    this.sqlOperator = sqlOperator;
  }

  public SqlParam(String columnName, Object value, SqlOperator sqlOperator) {
    this.columnName = columnName;
    this.value = value;
    this.sqlOperator = sqlOperator;
    this.linkOperator = SqlOperator.AND;
  }

  public SqlParam(
      String columnName, String value, SqlOperator sqlOperator, SqlOperator linkOperator) {
    this.columnName = columnName;
    this.value = value;
    this.sqlOperator = sqlOperator;
    this.linkOperator = linkOperator;
  }

  public Object getValue() {
    return value;
  }

  public void setValue(Object value) {
    this.value = value;
  }

  public SqlOperator getSqlOperator() {
    return sqlOperator;
  }

  public void setSqlOperator(SqlOperator sqlOperator) {
    this.sqlOperator = sqlOperator;
  }

  public String getColumnName() {
    return columnName;
  }

  public void setColumnName(String columnName) {
    this.columnName = columnName;
  }

  public SqlOperator getLinkOperator() {
    return linkOperator;
  }

  public void setLinkOperator(SqlOperator linkOperator) {
    this.linkOperator = linkOperator;
  }
}

3.2 SQL操作符枚举类

public enum SqlOperator {
  LIKE,
  EQ,
  OR,
  AND,
  GE,
  GT,
  LE,
  LT,
  NE,
  ORDER_BY_ASC,
  ORDER_BY_DESC,
  GROUP_BY
}

3.3 QueryWrapper生成类

@Slf4j
public class DaoUtils {
  public static QueryWrapper generateQueryWrapper(List<SqlParam> sqlParams) {
    QueryWrapper queryWrapper = new QueryWrapper();
    for (int i = 0; i < sqlParams.size(); i++) {
      SqlParam sqlParam = sqlParams.get(i);
      SqlOperator sqlOperator = sqlParam.getSqlOperator();
      SqlOperator linkOperator = sqlParam.getLinkOperator();
      Object value = sqlParam.getValue();

      // 查询条件间以“或”连接
      if (linkOperator == SqlOperator.OR) {
        queryWrapper.or();
      }

      // 否则查询条件间默认以”且“连接
      switch (sqlOperator) {
        case LIKE:
          // 模糊匹配
          if (checkValue(value)) {
            queryWrapper.like(sqlParam.getColumnName(), value);
          }
          break;
        case EQ:
          // 等于
          if (checkValue(value)) {
            queryWrapper.eq(sqlParam.getColumnName(), value);
          }
          break;
        case GE:
          // 大于等于
          if (checkValue(value)) {
            queryWrapper.ge(sqlParam.getColumnName(), value);
          }
          break;
        case GT:
          // 大于
          if (checkValue(value)) {
            queryWrapper.gt(sqlParam.getColumnName(), value);
          }
          break;
        case LE:
          // 小于等于
          if (checkValue(value)) {
            queryWrapper.le(sqlParam.getColumnName(), value);
          }
          break;
        case LT:
          // 小于
          if (checkValue(value)) {
            queryWrapper.lt(sqlParam.getColumnName(), value);
          }
          break;
        case NE:
          // 不等于
          if (checkValue(value)) {
            queryWrapper.ne(sqlParam.getColumnName(), value);
          }
          break;
        case ORDER_BY_ASC:
          // 升序排序
          queryWrapper.orderByAsc(sqlParam.getColumnName());
          break;
        case ORDER_BY_DESC:
          // 降序排序
          queryWrapper.orderByDesc(sqlParam.getColumnName());
          break;
        case GROUP_BY:
          // 分组排序
          queryWrapper.groupBy(sqlParam.getColumnName());
          break;
        default:
          log.error("QueryWrapper排序规则不支持:" + sqlOperator);
      }
    }
    return queryWrapper;
  }

  /**
   * 判断查询条件的值是否为空， 当查询条件的值为空时就不需要执行对应的QueryWrapper设置方法。
   *
   * @param value
   * @return
   */
  private static boolean checkValue(Object value) {
    boolean result = (null != value) && (StringUtils.isNotBlank(value.toString()));
    return result;
  }
}

3.4 自定义查询条件方法

public class MyBaseServiceImpl<M extends BaseMapper<T>, T> extends ServiceImpl<M, T>
    implements MyBaseService<T> {
  @Override
  public MyPage<T> customPagingQuery(List<SqlParam> sqlParams, long currentPage, long pageSize) {
    MyPage<T> page = new MyPage<>(currentPage, pageSize);
    QueryWrapper queryWrapper = DaoUtils.generateQueryWrapper(sqlParams);
    MyPage<T> myPage = this.myPage(page, queryWrapper);
    return myPage;
  }

  @Override
  public List<T> customQuery(List<SqlParam> sqlParams) {
    QueryWrapper queryWrapper = DaoUtils.generateQueryWrapper(sqlParams);
    List<T> result = this.list(queryWrapper);
    return result;
  }
}

4. 测试代码

@RestController
@RequestMapping("/test/user")
public class UserController extends MyBaseController<UserService, User> {
  /**
   * 根据条件查询所有记录
   *
   * @param user
   * @return
   */
  @RequestMapping(value = "/list", method = RequestMethod.POST)
  public List<User> list(@RequestBody(required = false) User user) {
    List<SqlParam> sqlParams = new ArrayList<>();
    SqlParam sqlParam1 = new SqlParam("ACCOUNT", user.getAccount(), SqlOperator.EQ);
    SqlParam sqlParam2 = new SqlParam("REAL_NAME", user.getRealName(), SqlOperator.LIKE);
    sqlParams.add(sqlParam1);
    sqlParams.add(sqlParam2);

    SqlParam sortParam = new SqlParam("SORT", SqlOperator.ORDER_BY_ASC);
    sqlParams.add(sortParam);

    List<User> result = myBaseService.customQuery(sqlParams);
    return result;
  }

  /**
   * 根据条件分页查询记录
   *
   * @param user
   * @return
   */
  @RequestMapping(value = "/pageList", method = RequestMethod.POST)
  public MyPage<User> pageList(@RequestBody User user) {
    List<SqlParam> sqlParams = new ArrayList<>();
    SqlParam sqlParam1 = new SqlParam("ACCOUNT", user.getAccount(), SqlOperator.EQ);
    // SqlParam2第四个参数表示这两个查询条件之间是”OR”关系，默认是“AND”关系
    SqlParam sqlParam2 =
        new SqlParam("REAL_NAME", user.getRealName(), SqlOperator.LIKE, SqlOperator.OR);
    sqlParams.add(sqlParam1);
    sqlParams.add(sqlParam2);

    SqlParam sortParam = new SqlParam("SORT", SqlOperator.ORDER_BY_ASC);
    sqlParams.add(sortParam);

    MyPage<User> result =
        myBaseService.customPagingQuery(sqlParams, user.getCurrentPage(), user.getPageSize());
    return result;
  }
}

5. 完整代码

完整代码见以下Git仓库中的single-table-query子项目：

https://github.com/randy0098/framework-samples