👨‍💻作者简介：在笑大学牲

🎟️个人主页：无所谓^_^

 ps：点赞是免费的，却可以让写博客的作者开心好几天😎

前言

后台系统对table组件的需求是最常见的，不过element-ui的el-table组件只是能满足最基本的需求而已。本篇文章就是对table组件进行自定义，实现列的自定义排序、筛选功能。替换掉原来的输入框搜索。

一、项目介绍

项目下载（本篇文章的源码在工程目录通用后台管理系统中）

gitee：https://gitee.com/wusupweilgy/springboot-vue.git

1.项目运行效果

2.技术栈

前端：vue2、element-ui组件、axios

后端：springboot、mybatis-plus、redis

3.功能

• 表头自定义筛选
• 表头自定义排序

4.流程图

二、前端实现

1.定义表头筛选组件：

该组件在src/components/FilterHeader/inddex.vue中，可以实现字典、数字、文本、日期的筛选排序功能

<template>
  <div class="app-container">
    <el-form :model="queryParams" ref="queryForm" size="small" :inline="true" v-show="showSearch" label-width="68px">
      <el-form-item label="字典名称" prop="dictName">
        <el-input
          v-model="queryParams.dictName"
          placeholder="请输入字典名称"
          clearable
          style="width: 240px"
          @keyup.enter.native="handleQuery"
        />
      </el-form-item>
      <el-form-item label="字典类型" prop="dictType">
        <el-input
          v-model="queryParams.dictType"
          placeholder="请输入字典类型"
          clearable
          style="width: 240px"
          @keyup.enter.native="handleQuery"
        />
      </el-form-item>
      <el-form-item label="状态" prop="status">
        <el-select
          v-model="queryParams.status"
          placeholder="字典状态"
          clearable
          style="width: 240px"
        >
          <el-option
            v-for="dict in dict.type.sys_normal_disable"
            :key="dict.value"
            :label="dict.label"
            :value="dict.value"
          />
        </el-select>
      </el-form-item>
      <el-form-item label="创建时间">
        <el-date-picker
          v-model="dateRange"
          style="width: 240px"
          value-format="yyyy-MM-dd"
          type="daterange"
          range-separator="-"
          start-placeholder="开始日期"
          end-placeholder="结束日期"
        ></el-date-picker>
      </el-form-item>
      <el-form-item>
        <el-button type="primary" icon="el-icon-search" size="mini" @click="handleQuery">搜索</el-button>
        <el-button icon="el-icon-refresh" size="mini" @click="resetQuery">重置</el-button>
      </el-form-item>
    </el-form>

    <el-row :gutter="10" class="mb8">
      <el-col :span="1.5">
        <el-button
          type="primary"
          plain
          icon="el-icon-plus"
          size="mini"
          @click="handleAdd"
          v-hasPermi="['system:dict:add']"
        >新增</el-button>
      </el-col>
      <el-col :span="1.5">
        <el-button
          type="success"
          plain
          icon="el-icon-edit"
          size="mini"
          :disabled="single"
          @click="handleUpdate"
          v-hasPermi="['system:dict:edit']"
        >修改</el-button>
      </el-col>
      <el-col :span="1.5">
        <el-button
          type="danger"
          plain
          icon="el-icon-delete"
          size="mini"
          :disabled="multiple"
          @click="handleDelete"
          v-hasPermi="['system:dict:remove']"
        >删除</el-button>
      </el-col>
      <el-col :span="1.5">
        <el-button
          type="warning"
          plain
          icon="el-icon-download"
          size="mini"
          @click="handleExport"
          v-hasPermi="['system:dict:export']"
        >导出</el-button>
      </el-col>
      <el-col :span="1.5">
        <el-button
          type="danger"
          plain
          icon="el-icon-refresh"
          size="mini"
          @click="handleRefreshCache"
          v-hasPermi="['system:dict:remove']"
        >刷新缓存</el-button>
      </el-col>
      <right-toolbar :showSearch.sync="showSearch" @queryTable="getList"></right-toolbar>
    </el-row>

    <el-table v-loading="loading" :data="typeList" @selection-change="handleSelectionChange">
      <el-table-column type="selection" width="55" align="center" />
      <el-table-column label="字典编号" align="center" prop="dictId" />
      <el-table-column label="字典名称" align="center" prop="dictName" :show-overflow-tooltip="true" />
      <el-table-column label="字典类型" align="center" :show-overflow-tooltip="true">
        <template slot-scope="scope">
          <router-link :to="'/system/dict-data/index/' + scope.row.dictId" class="link-type">
            <span>{{ scope.row.dictType }}</span>
          </router-link>
        </template>
      </el-table-column>
      <el-table-column label="状态" align="center" prop="status">
        <template slot-scope="scope">
          <dict-tag :options="dict.type.sys_normal_disable" :value="scope.row.status"/>
        </template>
      </el-table-column>
      <el-table-column label="备注" align="center" prop="remark" :show-overflow-tooltip="true" />
      <el-table-column label="创建时间" align="center" prop="createTime" width="180">
        <template slot-scope="scope">
          <span>{{ parseTime(scope.row.createTime) }}</span>
        </template>
      </el-table-column>
      <el-table-column label="操作" align="center" class-name="small-padding fixed-width">
        <template slot-scope="scope">
          <el-button
            size="mini"
            type="text"
            icon="el-icon-edit"
            @click="handleUpdate(scope.row)"
            v-hasPermi="['system:dict:edit']"
          >修改</el-button>
          <el-button
            size="mini"
            type="text"
            icon="el-icon-delete"
            @click="handleDelete(scope.row)"
            v-hasPermi="['system:dict:remove']"
          >删除</el-button>
        </template>
      </el-table-column>
    </el-table>

    <pagination
      v-show="total>0"
      :total="total"
      :page.sync="queryParams.pageNum"
      :limit.sync="queryParams.pageSize"
      @pagination="getList"
    />

    <!-- 添加或修改参数配置对话框 -->
    <el-dialog :title="title" :visible.sync="open" width="500px" append-to-body>
      <el-form ref="form" :model="form" :rules="rules" label-width="80px">
        <el-form-item label="字典名称" prop="dictName">
          <el-input v-model="form.dictName" placeholder="请输入字典名称" />
        </el-form-item>
        <el-form-item label="字典类型" prop="dictType">
          <el-input v-model="form.dictType" placeholder="请输入字典类型" />
        </el-form-item>
        <el-form-item label="状态" prop="status">
          <el-radio-group v-model="form.status">
            <el-radio
              v-for="dict in dict.type.sys_normal_disable"
              :key="dict.value"
              :label="dict.value"
            >{{dict.label}}</el-radio>
          </el-radio-group>
        </el-form-item>
        <el-form-item label="备注" prop="remark">
          <el-input v-model="form.remark" type="textarea" placeholder="请输入内容"></el-input>
        </el-form-item>
      </el-form>
      <div slot="footer" class="dialog-footer">
        <el-button type="primary" @click="submitForm">确 定</el-button>
        <el-button @click="cancel">取 消</el-button>
      </div>
    </el-dialog>
  </div>
</template>

<script>
import { listType, getType, delType, addType, updateType, refreshCache } from "@/api/system/dict/type";

export default {
  name: "Dict",
  dicts: ['sys_normal_disable'],
  data() {
    return {
      // 遮罩层
      loading: true,
      // 选中数组
      ids: [],
      // 非单个禁用
      single: true,
      // 非多个禁用
      multiple: true,
      // 显示搜索条件
      showSearch: true,
      // 总条数
      total: 0,
      // 字典表格数据
      typeList: [],
      // 弹出层标题
      title: "",
      // 是否显示弹出层
      open: false,
      // 日期范围
      dateRange: [],
      // 查询参数
      queryParams: {
        pageNum: 1,
        pageSize: 10,
        dictName: undefined,
        dictType: undefined,
        status: undefined
      },
      // 表单参数
      form: {},
      // 表单校验
      rules: {
        dictName: [
          { required: true, message: "字典名称不能为空", trigger: "blur" }
        ],
        dictType: [
          { required: true, message: "字典类型不能为空", trigger: "blur" }
        ]
      }
    };
  },
  created() {
    this.getList();
  },
  methods: {
    /** 查询字典类型列表 */
    getList() {
      this.loading = true;
      listType(this.addDateRange(this.queryParams, this.dateRange)).then(response => {
          this.typeList = response.rows;
          this.total = response.total;
          this.loading = false;
        }
      );
    },
    // 取消按钮
    cancel() {
      this.open = false;
      this.reset();
    },
    // 表单重置
    reset() {
      this.form = {
        dictId: undefined,
        dictName: undefined,
        dictType: undefined,
        status: "0",
        remark: undefined
      };
      this.resetForm("form");
    },
    /** 搜索按钮操作 */
    handleQuery() {
      this.queryParams.pageNum = 1;
      this.getList();
    },
    /** 重置按钮操作 */
    resetQuery() {
      this.dateRange = [];
      this.resetForm("queryForm");
      this.handleQuery();
    },
    /** 新增按钮操作 */
    handleAdd() {
      this.reset();
      this.open = true;
      this.title = "添加字典类型";
    },
    // 多选框选中数据
    handleSelectionChange(selection) {
      this.ids = selection.map(item => item.dictId)
      this.single = selection.length!=1
      this.multiple = !selection.length
    },
    /** 修改按钮操作 */
    handleUpdate(row) {
      this.reset();
      const dictId = row.dictId || this.ids
      getType(dictId).then(response => {
        this.form = response.data;
        this.open = true;
        this.title = "修改字典类型";
      });
    },
    /** 提交按钮 */
    submitForm: function() {
      this.$refs["form"].validate(valid => {
        if (valid) {
          if (this.form.dictId != undefined) {
            updateType(this.form).then(response => {
              this.$modal.msgSuccess("修改成功");
              this.open = false;
              this.getList();
            });
          } else {
            addType(this.form).then(response => {
              this.$modal.msgSuccess("新增成功");
              this.open = false;
              this.getList();
            });
          }
        }
      });
    },
    /** 删除按钮操作 */
    handleDelete(row) {
      const dictIds = row.dictId || this.ids;
      this.$modal.confirm('是否确认删除字典编号为"' + dictIds + '"的数据项？').then(function() {
        return delType(dictIds);
      }).then(() => {
        this.getList();
        this.$modal.msgSuccess("删除成功");
      }).catch(() => {});
    },
    /** 导出按钮操作 */
    handleExport() {
      this.download('system/dict/type/export', {
        ...this.queryParams
      }, `type_${new Date().getTime()}.xlsx`)
    },
    /** 刷新缓存按钮操作 */
    handleRefreshCache() {
      refreshCache().then(() => {
        this.$modal.msgSuccess("刷新成功");
      });
    }
  }
};
</script>

2.在main.js中全局注册组件：

并且还需要注册全局事件总线用于组件之间的通信

// 表头筛选组件
import FilterHeader from '@/components/FilterHeader'
Vue.component('FilterHeader', FilterHeader)
new Vue({
  router,
  store,
  render: h => h(App),
  beforeCreate(){
    Vue.prototype.$bus = this	//安装全局事件总线
  }
}).$mount('#app')

/**
 * @param: fileName - 文件名称
 * @param: 数据返回 1) 无后缀匹配 - false
 * @param: 数据返回 2) 匹配图片 - image
 * @param: 数据返回 3) 匹配 txt - txt
 * @param: 数据返回 4) 匹配 excel - excel
 * @param: 数据返回 5) 匹配 word - word
 * @param: 数据返回 6) 匹配 pdf - pdf
 * @param: 数据返回 7) 匹配 ppt - ppt
 * @param: 数据返回 8) 匹配 视频 - video
 * @param: 数据返回 9) 匹配 音频 - radio
 * @param: 数据返回 10) 其他匹配项 - other
 * @author: ljw
 **/

export function fileSuffixTypeUtil(fileName){
    // 后缀获取
    var suffix = "";
    // 获取类型结果
    var result = "";
    try {
        var flieArr = fileName.split(".");
        suffix = flieArr[flieArr.length - 1];
    } catch (err) {
        suffix = "";
    }
    // fileName无后缀返回 false
    if (!suffix) {
        result = false;
        return result;
    }
    // 图片格式
    var imglist = ["png", "jpg", "jpeg", "bmp", "gif"];
    // 进行图片匹配
    result = imglist.some(function (item) {
        return item == suffix;
    });
    if (result) {
        result = "image";
        return result;
    }
    // 匹配txt
    var txtlist = ["txt"];
    result = txtlist.some(function (item) {
        return item == suffix;
    });
    if (result) {
        result = "txt";
        return result;
    }
    // 匹配 excel
    var excelist = ["xls", "xlsx"];
    result = excelist.some(function (item) {
        return item == suffix;
    });
    if (result) {
        result = "excel";
        return result;
    }
    // 匹配 word
    var wordlist = ["doc", "docx"];
    result = wordlist.some(function (item) {
        return item == suffix;
    });
    if (result) {
        result = "word";
        return result;
    }
    // 匹配 pdf
    var pdflist = ["pdf"];
    result = pdflist.some(function (item) {
        return item == suffix;
    });
    if (result) {
        result = "pdf";
        return result;
    }
    // 匹配 ppt
    var pptlist = ["ppt"];
    result = pptlist.some(function (item) {
        return item == suffix;
    });
    if (result) {
        result = "ppt";
        return result;
    }
    // 匹配 视频
    var videolist = ["mp4", "m2v", "mkv","ogg", "flv", "avi", "wmv", "rmvb"];
    result = videolist.some(function (item) {
        return item == suffix;
    });
    if (result) {
        result = "video";
        return result;
    }
    // 匹配 音频
    var radiolist = ["mp3", "wav", "wmv"];
    result = radiolist.some(function (item) {
        return item == suffix;
    });
    if (result) {
        result = "radio";
        return result;
    }
    // 其他 文件类型
    result = "other";
    return result;
};

三、后端实现

1.表头筛选数据库结构

CREATE TABLE `sys_header_filter` (
     `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
     `user_name` varchar(32) DEFAULT NULL COMMENT '用户名',
     `page_name` varchar(32) DEFAULT NULL COMMENT '当前页面的路由名',
     `table_name` varchar(32) DEFAULT NULL COMMENT '字段所属表',
     `field_name` varchar(32) DEFAULT NULL COMMENT '属性名称',
     `condition_type` varchar(16) DEFAULT NULL COMMENT '条件',
     `text` varchar(64) DEFAULT NULL COMMENT '文本值',
     `start_value` varchar(64) DEFAULT '\0' COMMENT '开始值',
     `del_flag` varbinary(16) DEFAULT '0' COMMENT '逻辑删除',
     `end_value` varchar(64) DEFAULT NULL COMMENT '结束值',
     `type` varchar(16) DEFAULT NULL COMMENT '类型',
     `order_type` varchar(16) DEFAULT NULL COMMENT '排序条件',
     `checkbox` varchar(64) DEFAULT NULL COMMENT '多选框值',
     `create_by` varchar(32) DEFAULT NULL COMMENT '创建人',
     `update_by` varchar(32) DEFAULT NULL COMMENT '最后更新人',
     `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
     `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
     PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=295 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='用户默认表头筛选表'

2.后端主要代码：Mybatis自定义实现sql拦截器

package com.wusuowei.common.utils.mybatis;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLOrderBy;
import com.alibaba.druid.sql.ast.SQLOrderingSpecification;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOperator;
import com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr;
import com.alibaba.druid.sql.ast.statement.SQLSelect;
import com.alibaba.druid.sql.ast.statement.SQLSelectOrderByItem;
import com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.parser.SQLExprParser;
import com.alibaba.druid.sql.parser.SQLParserUtils;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.alibaba.druid.util.JdbcUtils;
import com.alibaba.fastjson2.JSON;
import com.wusuowei.common.utils.ServletUtils;
import com.wusuowei.common.utils.StringUtils;
import com.wusuowei.common.web.domain.BaseEntity;
import com.wusuowei.common.web.page.ConditionDomain;
import com.wusuowei.common.web.page.TableSupport;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.HttpMethod;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletRequest;
import java.io.BufferedReader;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.util.Iterator;
import java.util.List;


/**
 * Mybagis拦截器，拦截分页查询带筛选条件的请求，该拦截器在分页拦截器之后执行
 *
 * @author liguangyao
 * @date 2023/9/5
 */
@Component
//拦截StatementHandler类中参数类型为Statement的prepare方法（prepare=在预编译SQL前加入修改的逻辑）
//即拦截 Statement prepare(Connection var1, Integer var2) 方法
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class HeadFilterInterceptor implements Interceptor {
    private static final Logger log = LoggerFactory.getLogger(HeadFilterInterceptor.class);

    /**
     * 获取分页请求表头筛选的条件
     *
     * @param request
     * @return
     */
    private BaseEntity getParamsPlusFromRequest(HttpServletRequest request) {
        if (HttpMethod.GET.name().equals(request.getMethod()) && ObjectUtil.isNotNull(request.getParameter(TableSupport.PARAMS_PLUS))) {
            BaseEntity baseEntity = new BaseEntity();
            baseEntity.setParamsPlus(request.getParameter(TableSupport.PARAMS_PLUS));
            baseEntity.setDatabaseTable(request.getParameter(TableSupport.DATABASE_TABLE));
            baseEntity.setPageNum(request.getParameter(TableSupport.PAGE_NUM));
            baseEntity.setPageSize(request.getParameter(TableSupport.PAGE_SIZE));
            return baseEntity;
        } else if (HttpMethod.POST.name().equals(request.getMethod())) {
            BaseEntity baseEntity = new BaseEntity();
            StringBuilder sb = new StringBuilder();
            try (BufferedReader reader = request.getReader();) {
                char[] buff = new char[1024];
                int len;
                while ((len = reader.read(buff)) != -1) {
                    sb.append(buff, 0, len);
                }
                if (StrUtil.isBlank(sb)) {
                    return null;
                }
                // 判断是否是分页请求
                if (!sb.toString().contains(TableSupport.PAGE_NUM) || !sb.toString().contains(TableSupport.PAGE_SIZE) || !sb.toString().contains(TableSupport.PARAMS_PLUS)) {
                    return null;
                }
                baseEntity = JSON.parseObject(sb.toString(), BaseEntity.class);
                if (StringUtils.isBlank(baseEntity.getPageNum()) || StringUtils.isBlank(baseEntity.getPageSize())) {
                    return null;
                }
            } catch (Exception e) {
                log.error("表头筛选参数JSON转换异常：{}", e);
            }

            // 判断是否存在sql注入
            if (ObjectUtil.isNull(baseEntity) || MySqlUtil.sqlInjectionVerification(baseEntity.getParamsPlus()) || StringUtils.isBlank(baseEntity.getParamsPlus())) {
                return null;
            }
            // 将json格式的筛选条件字符串转换成集合
            return baseEntity;
        }
        return null;
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        // 判断是否是前台的请求
        if (ObjectUtil.isEmpty(ServletUtils.getRequestAttributes())) {
            return invocation.proceed();
        }

        HttpServletRequest request = ServletUtils.getRequest();

        // 获取表头筛选条件
        BaseEntity baseEntity = this.getParamsPlusFromRequest(request);
        if (ObjectUtil.isNull(baseEntity)) {
            return invocation.proceed();
        }
        List<ConditionDomain> paramsPlus = JSON.parseArray(baseEntity.getParamsPlus(), ConditionDomain.class);

        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        BoundSql boundSql = statementHandler.getBoundSql();
        // 获取到原始sql语句
        String sql = boundSql.getSql();

        // 如果获取不到该sql中的数据库名，执行原语句
        String tableName = MySqlUtil.getTableName(sql, baseEntity.getDatabaseTable());
        if (StringUtils.isBlank(tableName)) {
            return invocation.proceed();
        }
        // 根据条件拼接sql
        String mSql = resetSQL(tableName, sql, paramsPlus);

        // 如果拼接的sql不正确直接执行原sql
        if (!MySqlUtil.isSqlValid(mSql)) {
            return invocation.proceed();
        }

        // 通过反射修改sql语句
        Field field = boundSql.getClass().getDeclaredField("sql");
        field.setAccessible(true);
        field.set(boundSql, mSql);

        log.debug("原来的SQL====>" + sql);
        log.debug("拼接后的SQL====>" + mSql);

        return invocation.proceed();


    }

    /**
     * 获取拼接后的完整sql
     *
     * @param tableName
     * @param sql
     * @param paramsPlus
     * @return
     */
    private String resetSQL(String tableName, String sql, List<ConditionDomain> paramsPlus) {
        // 获取表的别名
        String tableAliases = tableName + ".";

        SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, JdbcUtils.MYSQL);
        List<SQLStatement> stmtList = parser.parseStatementList();
        SQLStatement stmt = stmtList.get(0);
        if (stmt instanceof SQLSelectStatement) {

            // 根据参数拼接的where条件sql
            String whereStr = splicingWhereSQL(tableAliases, sql, paramsPlus);

            // 拿到SQLSelect
            SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
            SQLSelect sqlselect = selectStmt.getSelect();
            SQLSelectQueryBlock query = (SQLSelectQueryBlock) sqlselect.getQuery();

            if (ObjectUtil.isNotEmpty(whereStr)) {
                SQLExprParser constraintsParser = SQLParserUtils.createExprParser(whereStr, JdbcUtils.MYSQL);
                SQLExpr constraintsExpr = constraintsParser.expr();

                SQLExpr whereExpr = query.getWhere();
                // 修改where表达式
                if (whereExpr == null) {
                    query.setWhere(constraintsExpr);
                } else {
                    SQLBinaryOpExpr newWhereExpr = new SQLBinaryOpExpr(whereExpr, SQLBinaryOperator.BooleanAnd, constraintsExpr);
                    query.setWhere(newWhereExpr);
                }
            }

            // 创建新的排序项
            for (ConditionDomain item : paramsPlus) {
                SQLIdentifierExpr newOrderByExpr = new SQLIdentifierExpr(tableAliases + StringUtils.toUnderScoreCase(item.getFieldName()));
                SQLSelectOrderByItem newOrderByItem = null;
                // 判断字段升序降序
                boolean isAsc = SQLOrderingSpecification.ASC.toString().equalsIgnoreCase(item.getOrderType());
                if (isAsc) {
                    newOrderByItem = new SQLSelectOrderByItem(newOrderByExpr, SQLOrderingSpecification.ASC);
                } else {
                    newOrderByItem = new SQLSelectOrderByItem(newOrderByExpr, SQLOrderingSpecification.DESC);
                }

                // 将新的排序项添加到已有的排序项后面
                SQLOrderBy orderBy = query.getOrderBy();
                // 判断原sql是否有排序规则
                if (orderBy == null) {
                    SQLOrderBy sqlOrderBy = new SQLOrderBy();
                    sqlOrderBy.addItem(newOrderByItem);
                    query.addOrderBy(sqlOrderBy);
                } else {
                    orderBy.addItem(newOrderByItem);
                }
            }
            return sqlselect.toString();
        }
        return sql;
    }

    /**
     * where条件拼接sql (table.name = '李四' AND table.age = 18) 带括号和表名称的格式
     *
     * @param paramsPlus
     * @param tableAliases
     * @return
     */
    private String splicingWhereSQL(String tableAliases, String sql, List<ConditionDomain> paramsPlus) {
        StringBuffer whereBuffer = new StringBuffer();
        Iterator<ConditionDomain> keyIter = paramsPlus.iterator();

        // 找到第一个where条件进行拼接
        while (keyIter.hasNext()) {
            ConditionDomain conditionDomain = keyIter.next();
            String codition = ConditionChoseMap.getCodition(conditionDomain);
            if (ObjectUtil.isNotEmpty(conditionDomain.getTableName())) {
                whereBuffer.append(MySqlUtil.getTableAlias(conditionDomain.getTableName(), sql)).append(".").append(StringUtils.toUnderScoreCase(conditionDomain.getFieldName())).append(ConditionChoseMap.getCodition(conditionDomain));
                break;
            }
            // 如果查询
            if (ObjectUtil.isNotEmpty(codition)) {
                whereBuffer.append(tableAliases).append(StringUtils.toUnderScoreCase(conditionDomain.getFieldName())).append(ConditionChoseMap.getCodition(conditionDomain));
                break;
            }
        }

        // 后面的where条件用AND进行拼接
        while (keyIter.hasNext()) {
            ConditionDomain conditionDomain = keyIter.next();
            String codition = ConditionChoseMap.getCodition(conditionDomain);
            if (ObjectUtil.isNotEmpty(conditionDomain.getTableName())) {
                whereBuffer.append(MySqlUtil.getTableAlias(conditionDomain.getTableName(), sql)).append(".").append(StringUtils.toUnderScoreCase(conditionDomain.getFieldName())).append(ConditionChoseMap.getCodition(conditionDomain));
                break;
            }
            if (ObjectUtil.isNotEmpty(codition)) {
                whereBuffer.append(" AND ").append(tableAliases).append(StringUtils.toUnderScoreCase(conditionDomain.getFieldName())).append(ConditionChoseMap.getCodition(conditionDomain));
            }
        }
        return whereBuffer.toString();
    }
}

四、使用

<template>
  <div>
    <div style="margin: 10px 0">
      <right-toolbar @handleQuery="handleQuery" @resetFilter="resetFilter" @queryTable="getList"></right-toolbar>
    </div>
    <div style="margin: 10px 0">
      <el-button
          type="primary"
          plain
          icon="el-icon-top"
          size="mini"
          @click="showUploadDialog"
      >点击上传
      </el-button>
      <el-popconfirm
          class="ml-5"
          confirm-button-text='确定'
          cancel-button-text='我再想想'
          icon="el-icon-info"
          icon-color="red"
          title="您确定批量删除这些数据吗？"
          @confirm="delBatch"
      >
        <el-button style="margin: 0 5px" icon="el-icon-delete" size="mini" type="danger" slot="reference" plain>
          批量删除
        </el-button>
      </el-popconfirm>
    </div>
    <el-table :data="tableData" border stripe v-loading="loading"
              @selection-change="handleSelectionChange">

      <el-table-column type="selection" width="55"></el-table-column>
      <el-table-column prop="id" label="ID" width="80">
        <template slot="header" slot-scope="scope">
          <filter-header
              @handleQuery="handleQuery"
              :paramsPlusTemp.sync="paramsPlusTemp"
              :column="scope.column"
              field-name="type"
              filter-type="number"
          ></filter-header>
        </template>
      </el-table-column>
      <el-table-column prop="fileName" width="160" label="文件名称" :show-overflow-tooltip="true">
        <template slot="header" slot-scope="scope">
          <filter-header
              @handleQuery="handleQuery"
              :paramsPlusTemp.sync="paramsPlusTemp"
              :column="scope.column"
              field-name="type"
              filter-type="text"
          ></filter-header>
        </template>
        <template slot-scope="scope" v-if="scope.row.fileName">
          <span @click="copyText(scope.row.fileName)" style="cursor: pointer">{{
              scope.row.fileName
            }}</span>
        </template>
      </el-table-column>
      <el-table-column prop="fileType" align="center" label="文件类型">
        <template slot="header" slot-scope="scope">
          <filter-header
              @handleQuery="handleQuery"
              :paramsPlusTemp.sync="paramsPlusTemp"
              :column="scope.column"
              :customArrList="dict.type.sys_file_type"
              field-name="type"
              filter-type="checkbox"
          ></filter-header>
        </template>
        <template v-slot="scope">
          <dict-tag :options="dict.type.sys_file_type" :value="scope.row.fileType"/>
        </template>
      </el-table-column>
      <el-table-column prop="fileSize" label="文件大小(mb)">
        <template slot="header" slot-scope="scope">
          <filter-header
              @handleQuery="handleQuery"
              :paramsPlusTemp.sync="paramsPlusTemp"
              :column="scope.column"
              field-name="type"
              filter-type="number"
          ></filter-header>
        </template>
        <template slot-scope="scope">
          {{ scope.row.fileSize | transformByte }}
        </template>
      </el-table-column>
      <el-table-column prop="nickName" label="上传用户" :show-overflow-tooltip="true">
        <template slot="header" slot-scope="scope">
          <filter-header
              @handleQuery="handleQuery"
              :paramsPlusTemp.sync="paramsPlusTemp"
              :column="scope.column"
              field-name="type"
              filter-type="text"
          ></filter-header>
        </template>
      </el-table-column>
      <el-table-column prop="createTime" label="上传时间">
        <template slot="header" slot-scope="scope">
          <filter-header
              @handleQuery="handleQuery"
              :paramsPlusTemp.sync="paramsPlusTemp"
              :column="scope.column"
              field-name="type"
              filter-type="date"
          ></filter-header>
        </template>
      </el-table-column>
      <el-table-column prop="enable" width="80" label="启用">
        <template slot="header" slot-scope="scope">
          <filter-header
              @handleQuery="handleQuery"
              :paramsPlusTemp.sync="paramsPlusTemp"
              :column="scope.column"
              :customArrList="dict.type.sys_file_status"
              field-name="type"
              filter-type="checkbox"
          ></filter-header>
        </template>
        <template slot-scope="scope">
          <el-switch v-model="scope.row.enable" active-color="#13ce66" inactive-color="#ccc"
                     @change="changeEnable(scope.row)"></el-switch>
        </template>
      </el-table-column>
      <el-table-column fixed="right" label="操作" width="200" align="center">
        <template slot-scope="scope">
          <el-button
              size="mini"
              type="text"
              @click="lookonline(scope.row.url)"
              slot="reference"><i class="el-icon-view"></i>预览
          </el-button>
          <el-button
              size="mini"
              type="text"

              @click="download(scope.row)"
              slot="reference"><i class="el-icon-download"></i>下载
          </el-button>
          <el-popconfirm
              confirm-button-text='确定'
              cancel-button-text='我再想想'
              icon="el-icon-info"
              icon-color="red"
              title="您确定删除吗？"
              @confirm="del(scope.row)"
          >
            <el-button style="margin: 0 10px" size="mini"
                       type="text"
                       slot="reference"><i class="el-icon-delete"></i>删除
            </el-button>
          </el-popconfirm>
        </template>
      </el-table-column>
    </el-table>
    <div style="padding: 10px 0">
      <el-pagination
          @size-change="handleSizeChange"
          @current-change="handleCurrentChange"
          :current-page="pagequery.pageNum"
          :page-sizes="[2, 5, 10, 20]"
          :page-size="pagequery.pageSize"
          layout="total, sizes, prev, pager, next, jumper"
          :total="total">
      </el-pagination>
    </div>
    <FileUpload :fileTableVisible="fileTableVisible" @uploadFileList="uploadFileList"
                @changeFileDialogVisible="changeFileDialogVisible"></FileUpload>
  </div>
</template>

<script>
import FileUpload from "@/components/FileUpload";
import {getFilesPage, delFilesByIds, delFileById, updateFile} from '@/api/system/file'
import axios from "axios";
import * as base64Encode from "js-base64";
import {getDefaultHeaderFilter, uploadDefaultHeaderFilter} from "@/api/system/headerFilter";
import {copyText} from "@/utils";

export default {
  name: "File",
  components: {FileUpload},
  dicts: ['sys_file_type','sys_file_status'],
  data() {
    return {
      // 查询参数
      queryParams: {
        pageNum: 1,
        pageSize: 10,
        databaseTable: 'sys_file',
      },
      // 筛选和排序条件
      paramsPlusTemp: [],
      dateRange: '',
      // 遮罩层
      loading: true,
      fileTypes: [{label: '图片', value: 'image'}, {label: '文本', value: 'txt'}, {
        label: '视频',
        value: 'video'
      }, {label: '音频', value: 'radio'}, {label: 'Excel', value: 'excel'}, {
        label: 'Word',
        value: 'word'
      }, {label: 'pdf', value: 'pdf'}, {label: 'PPT', value: 'ppt'}, {label: '其他', value: 'other'}],
      pagequery: {  //分页查询条件
        pageNum: 1,
        pageSize: 5,
      },
      fileTableVisible: false,
      uploadHeaders: localStorage.getItem("token"),
      tableData: [],
      multipleSelection: [],
      total: 0,
      headers: localStorage.getItem('token'),
    }
  },
  created() {
    getDefaultHeaderFilter().then(res => {
      if (res.data) {
        localStorage.setItem("defaultHeader", JSON.stringify(res.data))
        this.paramsPlusTemp = res.data[this.$route.name]
      }
      this.getList();
    })
  },
  methods: {
    copyText,
    /** 刷新按钮操作 */
    resetQuery() {
      this.resetForm("queryForm");
      this.handleQuery();
    },
    /** 重置更新所有表头筛选组件 */
    resetFilter() {
      this.$bus.$emit('resetFilter')
      this.paramsPlusTemp = []
      this.queryParams.paramsPlus = null
      uploadDefaultHeaderFilter(this.$route.name, null).then(res => {
        localStorage.removeItem('defaultHeader')
      })
      this.getList()
    },
    getList() {
      this.loading = true;
      this.queryParams.paramsPlus = this.paramsPlusTemp && this.paramsPlusTemp.length === 0 ? null : JSON.stringify(this.paramsPlusTemp)
      getFilesPage(this.queryParams).then((res) => {
        //console.log("resp:", res);
        this.total = res.total
        this.tableData = res.rows
        this.loading = false;
      });
    },
    showUploadDialog() {
      //如果有文件没有上传成功就保留这个文件，这样下次打开弹框还有记录
      this.fileTableVisible = true
    },
    changeFileDialogVisible(value) {
      this.fileTableVisible = value
    },
    uploadFileList() {
      this.getList()
    },
    changeEnable(row) {
      updateFile(row).then(res => {
        if (res.code === 200) {
          this.$message.success("操作成功")
        }
      })
    },
    del(file) {
      delFileById(file).then(res => {
        if (res.code === 200) {
          this.$message.success("删除成功")
          this.getList()
        }
      })
    },
    handleSelectionChange(val) {
      this.multipleSelection = val
    },
    delBatch() {
      if (this.multipleSelection.length === 0) {
        this.$message.warning("请选择删除的文件")
        return
      }
      delFilesByIds(this.multipleSelection).then(res => {
        if (res.code === 200) {
          this.$message.success("批量删除成功")
          this.getList()
        }
      })
    },
    reset() {
      this.dateRange = ''
      this.pagequery = {
        pageNum: 1,
        pageSize: 5,
      }
      this.getList()
    },
    handleSizeChange(pageSize) {
      this.pagequery.pageSize = pageSize
      this.getList()
    },
    handleCurrentChange(pageNum) {
      this.pagequery.pageNum = pageNum
      this.getList()
    },

    // 下载文件
    download(row) {
      axios.get(row.url,
          {responseType: 'blob'}
      ).then((res) => {
        console.log('文件下载成功');
        const blob = new Blob([res.data]);
        const fileName = row.fileName;

        //对于<a>标签，只有 Firefox 和 Chrome（内核） 支持 download 属性
        //IE10以上支持blob，但是依然不支持download
        if ('download' in document.createElement('a')) {
          //支持a标签download的浏览器
          const link = document.createElement('a');//创建a标签
          link.download = fileName;//a标签添加属性
          link.style.display = 'none';
          link.href = URL.createObjectURL(blob);
          document.body.appendChild(link);
          link.click();//执行下载
          URL.revokeObjectURL(link.href); //释放url
          document.body.removeChild(link);//释放标签
        } else {
          navigator.msSaveBlob(blob, fileName);
        }
      }).catch((res) => {
        console.log('文件下载失败');
      });
    },
    lookonline(url) {
      console.log(url)
      window.open('http://127.0.0.1:8012/onlinePreview?url=' + encodeURIComponent(base64Encode.encode(url)));
    },
    /** 搜索按钮操作 */
    handleQuery() {
      this.queryParams.pageNum = 1;
      this.getList();
      uploadDefaultHeaderFilter(this.$route.name, this.queryParams.paramsPlus).then(res => {
        if (res.data) {
          localStorage.setItem('defaultHeader', JSON.stringify(res.data))
          this.paramsPlusTemp = res.data[this.$route.name]
        }
      })
      this.getList();
    },
    //获取时间区间方法
    dateFormat(picker) {
      this.pagequery.startTime = picker[0]
      this.pagequery.endTime = picker[1]
    },
  },
  filters: {
    transformByte(size) {
      if (!size) {
        return '0B'
      }
      const unitSize = 1024
      // if (size < unitSize) {
      //   return size + ' B'
      // }
      // // KB
      // if (size < Math.pow(unitSize, 2)) {
      //   return (size / unitSize).toFixed(2) + ' K';
      // }
      // MB
      // if (size < Math.pow(unitSize, 3)) {
        return (size / Math.pow(unitSize, 2)).toFixed(2) + ' MB'
      // }
      // // GB
      // if (size < Math.pow(unitSize, 4)) {
      //   return (size / Math.pow(unitSize, 3)).toFixed(2) + ' GB';
      // }
      // // TB
      // return (size / Math.pow(unitSize, 4)).toFixed(2) + ' TB';
    },
    transformType(filename) {
      return filename.substr(filename.lastIndexOf('.') + 1)
    }
  }
}
</script>

<style scoped>

</style>

五、注意点

本片文章的大概交互流程是，前端当前页面的表头筛选组件（子组件），将数据传递到当前组件中（当前页面父组件），并且请求了后端，持久化了表头筛选数据，发送列表请求。后台根据参数修改原sql。然后下次再查询当前页面或刷新时，回先从redis缓存中获取全部的表头筛选数据，获取成功后放入浏览器缓存，进而每个页面就能获取到对应的表头筛选数据进行数据渲染。

小结

文章贴的代码有点多了，大家可以去我的gitee上下载下来运行。项目的功能我测试优化了很多次，如果代码有何异常或者不完整欢迎在评论区留言。如果这篇文章有幸帮助到你，希望读者大大们可以给作者点个赞呀😶‍🌫️😶‍🌫️😶‍🌫️