前言

最近要实现对table按列要进行排序的需求， 故此整理一下从前端到后端整个的完整逻辑，供大家参考。

---

开发环境

项目为前后端分离项目，使用的是Ruoyi-VUE版前后端分离框架

• 前端：VUE + elementUI
• 后端：JAVA + Mybatis + PageHelper（分页）

---

前端部分

html内容:

代码如下：

<el-table :data="tableData"
                style="width: 100%"
                @sort-change="sortChange">
	<el-table-column prop="id"
                     label="ID"
                     fixed
                     sortable="custom"
                     width="60">
   </el-table-column>
   <el-table-column prop="shardNum"
                    width="100"
                    label="Shard编号">
   </el-table-column>
   <el-table-column prop="store"
                    width="140"
                    sortable="custom"
                    label="Shard大小">
  </el-table-column> 
</el-table>

<!-- 分页逻辑实现 -->
<pagination v-show="total>0"
            :total="total"
            :page.sync="queryParams.pageNum"
            :limit.sync="queryParams.pageSize"
            @pagination="getShardStore" />            

注意的点：

• @sort-change="sortChange"
• sortable="custom"

import js

import { getShardStore } from "@/api/kirara/elastic_search";

data内容：

data () {
    return {
      // 查询参数 分页参数和orderBy和是否desc参数
      queryParams: {
        pageNum: 1,
        pageSize: 10,
        orderBy: undefined,
        isAsc: undefined,
      },
      // table数据
      tableData: [],
      // 数据条数
      total: 0,

motheds内容：

getShardStore()方法

async getShardStore () {
      const that = this;
      getShardStore(that.queryParams).then((response) => {
        that.total = response.total;
        that.tableData = response.rows;
      });
    },

sortChange()方法

sortChange (column) {
      //打印可以分别得到上下箭头的数据
      console.log(column);
      if (column.order === 'descending') {
        this.queryParams.orderBy = column.prop
        this.queryParams.isAsc = 'desc'
      } else {
        this.queryParams.orderBy = column.prop
        this.queryParams.isAsc = 'asc'
      }
      this.queryParams.pageNum = 1;
      this.getShardStore(this.queryParams);//查询列表方法
    },

Ajax请求后台js代码

// 查看shard store
export function getShardStore (queryParams) {
  return request({
    url: '/api/v1/elastic-search/index/shard-store',
    method: 'get',
    params: queryParams
  })
}

---

后台部分

crontroller层

@GetMapping(value ="/index/shard-store" )
    public TableDataInfo indexShardStore(@RequestParam Integer pageNum,
                                      @RequestParam Integer pageSize,
                                      @RequestParam(defaultValue = "") String indexName,
                                      @RequestParam(defaultValue = "") String node,
                                      @RequestParam(defaultValue = "") String disk,
                                      @RequestParam(value = "orderBy", defaultValue = "id") String orderBy,
                                      @RequestParam(defaultValue = "isAsc") String isAsc) {
        startPage();
        List<Shard> pager = elasticsearchService.getShardStore(pageNum, pageSize, indexName, node, disk, orderBy, isAsc);
        return getDataTable(pager);
    }

service层

public List<Shard> getShardStore(Integer page, Integer size, String indexName, String node, String disk, String orderBy, String desc) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("node", node);
        params.put("disk",disk);
        params.put("indexName", indexName);
        params.put("orderBy", orderBy);
        params.put("desc", desc);
        Pager<Shard> pager = new Pager<Shard>();
        List<Shard> shardStore = elasticSearchMapper.getShardStore(params);
        return shardStore;
    }

mapper层

List<Shard> getShardStore(Map<String, Object> params);

xml

    <resultMap type="com.ruoyi.kirara.domain.entity.Shard" id="shard">
        <result property="id" column="id"/>
        <result property="indexName"     column="index_name"/>
        <result property="store"     column="store"/>
        <result property="prirep"     column="prirep"/>
        <result property="ip"     column="ip"/>
        <result property="disk"     column="disk"/>
        <result property="uuid"    column="uuid"/>
        <result property="docs"   column="docs"/>
        <result property="node" column="node"/>
    </resultMap>


 <sql id="selectShardStore">
        SELECT c.id,
               c.index_name,
               c.uuid,
               c.shard_num,
               c.store,
               c.prirep,
               c.docs,
               c.ip,
               d.disk,
               c.node
        FROM
            (SELECT a.index_name,
                    a.shard_num,
                    a.ip,
                    b.uuid,
                    a.prirep,
                    a.store,
                    a.id,
                    a.docs,
                    a.node
             FROM es_shard a
                      LEFT JOIN es_index b ON a.index_name = b.index_name)c
                LEFT JOIN es_shard_store d ON c.uuid = d.index_uuid
                AND d.shard_num = c.shard_num
                AND d.node = c.ip
    </sql>


<select id="getShardStore"  resultMap="shard"  useCache="false" flushCache="true">
        <include refid="selectShardStore"/>
        <where>
            <if test="indexName != null and indexName != ''">
                AND c.index_name like concat('%', #{indexName, jdbcType=VARCHAR}, '%')
            </if>
            <if test="node != null and node != ''">
                AND d.node = #{node, jdbcType=VARCHAR}
            </if>
            <if test="disk != null and disk != ''">
                AND d.disk = #{disk, jdbcType=VARCHAR}
            </if>
        </where>
        <if test="orderBy != null and orderBy!= ''">
            order by
            ${orderBy}
            <if test="desc == 'desc'">
                desc
            </if>
        </if>

    </select>

总结

到此完成了从前端到后端的的分页和按列排序功能。