在DataEase上支持服务端分页功能（上）

       近期由于业务需求，需要显示大量明细数据的查询功能，而DataEase目前仅支持客户端分页功能，显示大量明细，一是会导致响应特别慢，二是会导致浏览器内存溢出。为了解决该问题，本人研究了DataEase的源码，并在此基础上增加了服务端分页功能，即少量明细数据显示（1000条）时仍然使用客户端分页功能，全部显示时则使用服务端分页功能。由于本人比较喜欢DataEase v1.8.0的仪表板设计界面（设计元素居左，v1.9.0设计元素居右），主要是针对v1.8.0版本进行修改的，主要支持了JDBC类型数据库的分页实现，包括mysql、dorisdb，主要修改包括：

一、修改后端支持服务端分页的实现；

backend 涉及的修改文件及代码如下：

1.backend/src/main/java/io/dataease/controller/chart/ChartViewController.java

//增加getPageData接口

@DePermissionProxy(value = "proxy", paramIndex = 2)
    @DePermission(type = DePermissionType.PANEL, level = ResourceAuthLevel.PANNEL_LEVEL_VIEW, paramIndex = 1)
    @ApiOperation("分页数据")
    @PostMapping("/getPageData/{id}/{panelId}/{pageNo}")
    public ChartViewDTO getPageData(@PathVariable String id, @PathVariable String panelId,
            @RequestBody ChartExtRequest requestList, @PathVariable int pageNo)
            throws Exception {
        return chartViewService.getPageData(id, requestList, pageNo);
    }

//增加calcPageData接口

@DePermission(type = DePermissionType.PANEL, level = ResourceAuthLevel.PANNEL_LEVEL_VIEW)
    @ApiOperation("分页计算结果")
    @PostMapping("/calcPageData/{panelId}/{pageNo}")
    public ChartViewDTO calcPageData(@PathVariable String panelId, @RequestBody ChartCalRequest request,
            @PathVariable int pageNo) throws Exception {
        return chartViewService.calcPageData(request.getView(), request.getRequestList(), false, pageNo);
    }

2.backend/src/main/java/io/dataease/provider/datasource/JdbcProvider.java

//增加getPageData的实现

@Override
public List<String[]> getPageData(DatasourceRequest dsr) throws Exception {
        List<String[]> list = new LinkedList<>();
        StringBuilder limit = new StringBuilder(" LIMIT ");
        StringBuilder limit = new StringBuilder(" order by f_ax_0 LIMIT ");
        limit.append((dsr.getPage() - 1) * dsr.getPageSize()).append(",").append(dsr.getPageSize());
        String sql = dsr.getQuery();
        int limit_pos = sql.indexOf(" LIMIT ");
        if (limit_pos > 0)
            sql = sql.substring(0, limit_pos) + limit;
        else
            sql = sql + limit;
        System.out.println("drs Page SQL:" + sql);
        try (
                Connection connection = getConnectionFromPool(dsr);
                Statement stat = connection.createStatement();
                ResultSet rs = stat.executeQuery(rebuildSqlWithFragment(sql))) {
            list = fetchResult(rs);
            if (dsr.isPageable() && (dsr.getDatasource().getType().equalsIgnoreCase(DatasourceTypes.sqlServer.name())
                    || dsr.getDatasource().getType().equalsIgnoreCase(DatasourceTypes.db2.name()))) {
                Integer realSize = dsr.getPage() * dsr.getPageSize() < list.size() ? dsr.getPage() * dsr.getPageSize()
                        : list.size();
                list = list.subList((dsr.getPage() - 1) * dsr.getPageSize(), realSize);
            }
        } catch (SQLException e) {
            DataEaseException.throwException(Translator.get("i18n_sql_error") + e.getMessage());
        } catch (Exception e) {
            DataEaseException.throwException(Translator.get("i18n_datasource_connect_error") + e.getMessage());
        }
        return list;
    }

//增加getDataTotal的实现

@Override
public long getDataTotal(DatasourceRequest dsr) throws Exception {
        long total = 0;
        StringBuilder sb = new StringBuilder("");
        sb.append(dsr.getQuery());
        String sql = sb.toString().replace("*", "count(*) as total");
        int limit_pos = sql.indexOf(" LIMIT ");
        if (limit_pos > 0)
            sql = sql.substring(0, limit_pos);
        System.out.println("drs Count SQL:" + sql);
        try (
                Connection connection = getConnectionFromPool(dsr);
                Statement stat = connection.createStatement();
                ResultSet rs = stat
                        .executeQuery(rebuildSqlWithFragment(sql))) {
            if (rs.next())
                total = rs.getLong("total");
        } catch (SQLException e) {
            DataEaseException.throwException(Translator.get("i18n_sql_error") + e.getMessage());
        } catch (Exception e) {
            DataEaseException.throwException(Translator.get("i18n_datasource_connect_error") + e.getMessage());
        }
        return total;
    }

3.backend/src/main/java/io/dataease/base/domain/ChartView.java

//resultCount修改为long类型

private long resultCount;

4.backend/src/main/java/io/dataease/service/chart/ChartViewService.java

//增加getPageData接口的实现

public ChartViewDTO getPageData(String id, ChartExtRequest request, int pageNo) throws Exception {
        ChartViewDTO view = this.getOneWithPermission(id);
        // 如果是从仪表板获取视图数据，则仪表板的查询模式，查询结果的数量，覆盖视图对应的属性
        if (CommonConstants.VIEW_QUERY_FROM.PANEL.equals(request.getQueryFrom())
                && CommonConstants.VIEW_RESULT_MODE.CUSTOM.equals(request.getResultMode())) {
            view.setResultMode(request.getResultMode());
            // view.setResultCount(request.getResultCount());获取数据后赋值总数
        }
        return calcPageData(view, request, request.isCache(), pageNo);
    }


//增加calcPageData接口的实现

 public ChartViewDTO calcPageData(ChartViewDTO view, ChartExtRequest requestList,
            boolean cache, int pageNo) throws Exception {
        if (ObjectUtils.isEmpty(view)) {
            throw new RuntimeException(Translator.get("i18n_chart_delete"));
        }
        List<ChartViewFieldDTO> xAxis = new Gson().fromJson(view.getXAxis(), new TypeToken<List<ChartViewFieldDTO>>() {
        }.getType());
        if (StringUtils.equalsIgnoreCase(view.getType(), "table-pivot")) {
            List<ChartViewFieldDTO> xAxisExt = new Gson().fromJson(view.getXAxisExt(),
                    new TypeToken<List<ChartViewFieldDTO>>() {
                    }.getType());
            xAxis.addAll(xAxisExt);
        }
        List<ChartViewFieldDTO> yAxis = new Gson().fromJson(view.getYAxis(), new TypeToken<List<ChartViewFieldDTO>>() {
        }.getType());
        if (StringUtils.equalsIgnoreCase(view.getType(), "chart-mix")) {
            List<ChartViewFieldDTO> yAxisExt = new Gson().fromJson(view.getYAxisExt(),
                    new TypeToken<List<ChartViewFieldDTO>>() {
                    }.getType());
            yAxis.addAll(yAxisExt);
        }
        List<ChartViewFieldDTO> extStack = new Gson().fromJson(view.getExtStack(),
                new TypeToken<List<ChartViewFieldDTO>>() {
                }.getType());
        List<ChartViewFieldDTO> extBubble = new Gson().fromJson(view.getExtBubble(),
                new TypeToken<List<ChartViewFieldDTO>>() {
                }.getType());
        List<ChartFieldCustomFilterDTO> fieldCustomFilter = new Gson().fromJson(view.getCustomFilter(),
                new TypeToken<List<ChartFieldCustomFilterDTO>>() {
                }.getType());
        List<ChartViewFieldDTO> drill = new Gson().fromJson(view.getDrillFields(),
                new TypeToken<List<ChartViewFieldDTO>>() {
                }.getType());
        DatasetTableField datasetTableFieldObj = DatasetTableField.builder().tableId(view.getTableId())
                .checked(Boolean.TRUE).build();
        List<DatasetTableField> fields = dataSetTableFieldsService.list(datasetTableFieldObj);
        // 获取数据集,需校验权限
        DataSetTableDTO table = dataSetTableService.getWithPermission(view.getTableId(), requestList.getUser());