在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());
checkPermission("use", table, requestList.getUser());
// 列权限
List<String> desensitizationList = new ArrayList<>();
List<DatasetTableField> columnPermissionFields = permissionService.filterColumnPermissons(fields,
desensitizationList, table.getId(), requestList.getUser());
// 将没有权限的列删掉
List<String> dataeaseNames = columnPermissionFields.stream().map(DatasetTableField::getDataeaseName)
.collect(Collectors.toList());
dataeaseNames.add("*");
fieldCustomFilter = fieldCustomFilter.stream()
.filter(item -> !desensitizationList.contains(item.getDataeaseName())
&& dataeaseNames.contains(item.getDataeaseName()))
.collect(Collectors.toList());
extStack = extStack.stream().filter(item -> !desensitizationList.contains(item.getDataeaseName())
&& dataeaseNames.contains(item.getDataeaseName())).collect(Collectors.toList());
extBubble = extBubble.stream().filter(item -> !desensitizationList.contains(item.getDataeaseName())
&& dataeaseNames.contains(item.getDataeaseName())).collect(Collectors.toList());
drill = drill.stream().filter(item -> !desensitizationList.contains(item.getDataeaseName())
&& dataeaseNames.contains(item.getDataeaseName())).collect(Collectors.toList());
// 行权限
List<ChartFieldCustomFilterDTO> rowPermissionFields = permissionService.getCustomFilters(fields, table,
requestList.getUser());
fieldCustomFilter.addAll(rowPermissionFields);
for (ChartFieldCustomFilterDTO ele : fieldCustomFilter) {
ele.setField(dataSetTableFieldsService.get(ele.getId()));
}
if (CollectionUtils.isEmpty(xAxis) && CollectionUtils.isEmpty(yAxis)) {
return emptyChartViewDTO(view);
}
switch (view.getType()) {
case "text":
case "gauge":
case "liquid":
xAxis = new ArrayList<>();
yAxis = yAxis.stream().filter(item -> !desensitizationList.contains(item.getDataeaseName())
&& dataeaseNames.contains(item.getDataeaseName())).collect(Collectors.toList());
if (CollectionUtils.isEmpty(yAxis)) {
return emptyChartViewDTO(view);
}
break;
case "table-info":
yAxis = new ArrayList<>();
xAxis = xAxis.stream().filter(item -> dataeaseNames.contains(item.getDataeaseName()))
.collect(Collectors.toList());
if (CollectionUtils.isEmpty(xAxis)) {
return emptyChartViewDTO(view);
}
break;
case "table-normal":
xAxis = xAxis.stream().filter(item -> dataeaseNames.contains(item.getDataeaseName()))
.collect(Collectors.toList());
yAxis = yAxis.stream().filter(item -> dataeaseNames.contains(item.getDataeaseName()))
.collect(Collectors.toList());
break;
default:
xAxis = xAxis.stream().filter(item -> !desensitizationList.contains(item.getDataeaseName())
&& dataeaseNames.contains(item.getDataeaseName())).collect(Collectors.toList());
yAxis = yAxis.stream().filter(item -> !desensitizationList.contains(item.getDataeaseName())
&& dataeaseNames.contains(item.getDataeaseName())).collect(Collectors.toList());
}
// 过滤来自仪表板的条件
List<ChartExtFilterRequest> extFilterList = new ArrayList<>();
// 组件过滤条件
if (ObjectUtils.isNotEmpty(requestList.getFilter())) {
for (ChartExtFilterRequest request : requestList.getFilter()) {
// 解析多个fieldId,fieldId是一个逗号分隔的字符串
String fieldId = request.getFieldId();
if (StringUtils.isNotEmpty(fieldId)) {
String[] fieldIds = fieldId.split(",");
for (String fId : fieldIds) {
ChartExtFilterRequest filterRequest = new ChartExtFilterRequest();
BeanUtils.copyBean(filterRequest, request);
filterRequest.setFieldId(fId);
DatasetTableField datasetTableField = dataSetTableFieldsService.get(fId);
if (datasetTableField == null) {
continue;
}
if (!desensitizationList.contains(datasetTableField.getDataeaseName())
&& dataeaseNames.contains(datasetTableField.getDataeaseName())) {
filterRequest.setDatasetTableField(datasetTableField);
if (StringUtils.equalsIgnoreCase(datasetTableField.getTableId(), view.getTableId())) {
if (CollectionUtils.isNotEmpty(filterRequest.getViewIds())) {
if (filterRequest.getViewIds().contains(view.getId())) {
extFilterList.add(filterRequest);
}
} else {
extFilterList.add(filterRequest);
}
}
}
}
}
}
}
// 联动过滤条件联动条件全部加上
if (ObjectUtils.isNotEmpty(requestList.getLinkageFilters())) {
for (ChartExtFilterRequest request : requestList.getLinkageFilters()) {
DatasetTableField datasetTableField = dataSetTableFieldsService.get(request.getFieldId());
if (!desensitizationList.contains(datasetTableField.getDataeaseName())
&& dataeaseNames.contains(datasetTableField.getDataeaseName())) {
request.setDatasetTableField(datasetTableField);
if (StringUtils.equalsIgnoreCase(datasetTableField.getTableId(), view.getTableId())) {
if (CollectionUtils.isNotEmpty(request.getViewIds())) {
if (request.getViewIds().contains(view.getId())) {
extFilterList.add(request);
}
} else {
extFilterList.add(request);
}
}
}
}
}
// 下钻
List<ChartExtFilterRequest> drillFilters = new ArrayList<>();
boolean isDrill = false;
List<ChartDrillRequest> drillRequest = requestList.getDrill();
if (CollectionUtils.isNotEmpty(drillRequest) && (drill.size() > drillRequest.size())) {
for (int i = 0; i < drillRequest.size(); i++) {
ChartDrillRequest request = drillRequest.get(i);
for (ChartDimensionDTO dto : request.getDimensionList()) {
ChartViewFieldDTO chartViewFieldDTO = drill.get(i);
// 将钻取值作为条件传递，将所有钻取字段作为xAxis并加上下一个钻取字段
if (StringUtils.equalsIgnoreCase(dto.getId(), chartViewFieldDTO.getId())) {
isDrill = true;
DatasetTableField datasetTableField = dataSetTableFieldsService.get(dto.getId());
ChartViewFieldDTO d = new ChartViewFieldDTO();
BeanUtils.copyBean(d, datasetTableField);
ChartExtFilterRequest drillFilter = new ChartExtFilterRequest();
drillFilter.setFieldId(dto.getId());
drillFilter.setValue(new ArrayList<String>() {
{
add(dto.getValue());
}
});
drillFilter.setOperator("in");
drillFilter.setDatasetTableField(datasetTableField);
extFilterList.add(drillFilter);
drillFilters.add(drillFilter);
if (!checkDrillExist(xAxis, extStack, d, view)) {
xAxis.add(d);
}
if (i == drillRequest.size() - 1) {
ChartViewFieldDTO nextDrillField = drill.get(i + 1);
if (!checkDrillExist(xAxis, extStack, nextDrillField, view)) {
xAxis.add(nextDrillField);
}
}
}
}
}
}
// 判断连接方式，直连或者定时抽取 table.mode
DatasourceRequest datasourceRequest = new DatasourceRequest();
List<String[]> data = new ArrayList<>();
// 如果result_model='all',则服务端分页，否则使用客户端分页
String resultMode = view.getResultMode();
long total = 0;// 获取数据总数
if (resultMode.equals("all")) {
datasourceRequest.setPageable(true);
datasourceRequest.setPage(pageNo);
cache = false;
int pageSize = 20;
try {
String customAttr = view.getCustomAttr();
// tablePageSize 位置
int tpsPos = customAttr.indexOf("tablePageSize");
// : 位置
int colonPos = customAttr.indexOf(":", tpsPos);
// , 位置
int commaPos = customAttr.indexOf(",", tpsPos);
// tablePageSize 的值
String tablePageSize = customAttr.substring(colonPos + 2, commaPos - 1);
System.out.println("=======tablePageSize:" + tablePageSize);
pageSize = Integer.parseInt(tablePageSize);
} catch (Exception e) {
}
datasourceRequest.setPageSize(pageSize);
}
if (table.getMode() == 0) {// 直连
Datasource ds = datasourceService.get(table.getDataSourceId());
if (ObjectUtils.isEmpty(ds)) {
throw new RuntimeException(Translator.get("i18n_datasource_delete"));
}
if (StringUtils.isNotEmpty(ds.getStatus()) && ds.getStatus().equalsIgnoreCase("Error")) {
throw new Exception(Translator.get("i18n_invalid_ds"));
}
DatasourceProvider datasourceProvider = ProviderFactory.getProvider(ds.getType());
datasourceRequest.setDatasource(ds);
DataTableInfoDTO dataTableInfoDTO = new Gson().fromJson(table.getInfo(), DataTableInfoDTO.class);
QueryProvider qp = ProviderFactory.getQueryProvider(ds.getType());
if (StringUtils.equalsIgnoreCase(table.getType(), "db")) {
datasourceRequest.setTable(dataTableInfoDTO.getTable());
if (StringUtils.equalsIgnoreCase("text", view.getType())
|| StringUtils.equalsIgnoreCase("gauge", view.getType())
|| StringUtils.equalsIgnoreCase("liquid", view.getType())) {
datasourceRequest.setQuery(qp.getSQLSummary(dataTableInfoDTO.getTable(), yAxis, fieldCustomFilter,
extFilterList, view));
} else if (StringUtils.containsIgnoreCase(view.getType(), "stack")) {
datasourceRequest.setQuery(qp.getSQLStack(dataTableInfoDTO.getTable(), xAxis, yAxis,
fieldCustomFilter, extFilterList, extStack, ds, view));
} else if (StringUtils.containsIgnoreCase(view.getType(), "scatter")) {
datasourceRequest.setQuery(qp.getSQLScatter(dataTableInfoDTO.getTable(), xAxis, yAxis,
fieldCustomFilter, extFilterList, extBubble, ds, view));
} else if (StringUtils.equalsIgnoreCase("table-info", view.getType())) {
datasourceRequest.setQuery(qp.getSQLTableInfo(dataTableInfoDTO.getTable(), xAxis, fieldCustomFilter,
extFilterList, ds, view));
} else {
datasourceRequest.setQuery(qp.getSQL(dataTableInfoDTO.getTable(), xAxis, yAxis, fieldCustomFilter,
extFilterList, ds, view));
}
} else if (StringUtils.equalsIgnoreCase(table.getType(), "sql")) {
if (StringUtils.equalsIgnoreCase("text", view.getType())
|| StringUtils.equalsIgnoreCase("gauge", view.getType())
|| StringUtils.equalsIgnoreCase("liquid", view.getType())) {
datasourceRequest.setQuery(qp.getSQLSummaryAsTmp(dataTableInfoDTO.getSql(), yAxis,
fieldCustomFilter, extFilterList, view));
} else if (StringUtils.containsIgnoreCase(view.getType(), "stack")) {
datasourceRequest.setQuery(qp.getSQLAsTmpStack(dataTableInfoDTO.getSql(), xAxis, yAxis,
fieldCustomFilter, extFilterList, extStack, view));
} else if (StringUtils.containsIgnoreCase(view.getType(), "scatter")) {
datasourceRequest.setQuery(qp.getSQLAsTmpScatter(dataTableInfoDTO.getSql(), xAxis, yAxis,
fieldCustomFilter, extFilterList, extBubble, view));
} else if (StringUtils.equalsIgnoreCase("table-info", view.getType())) {
datasourceRequest.setQuery(qp.getSQLAsTmpTableInfo(dataTableInfoDTO.getSql(), xAxis,
fieldCustomFilter, extFilterList, ds, view));
} else {
datasourceRequest.setQuery(qp.getSQLAsTmp(dataTableInfoDTO.getSql(), xAxis, yAxis,
fieldCustomFilter, extFilterList, view));
}
} else if (StringUtils.equalsIgnoreCase(table.getType(), "custom")) {
DataTableInfoDTO dt = new Gson().fromJson(table.getInfo(), DataTableInfoDTO.class);
List<DataSetTableUnionDTO> list = dataSetTableUnionService
.listByTableId(dt.getList().get(0).getTableId());
String sql = dataSetTableService.getCustomSQLDatasource(dt, list, ds);
if (StringUtils.equalsIgnoreCase("text", view.getType())
|| StringUtils.equalsIgnoreCase("gauge", view.getType())
|| StringUtils.equalsIgnoreCase("liquid", view.getType())) {
datasourceRequest
.setQuery(qp.getSQLSummaryAsTmp(sql, yAxis, fieldCustomFilter, extFilterList, view));
} else if (StringUtils.containsIgnoreCase(view.getType(), "stack")) {
datasourceRequest.setQuery(
qp.getSQLAsTmpStack(sql, xAxis, yAxis, fieldCustomFilter, extFilterList, extStack, view));
} else if (StringUtils.containsIgnoreCase(view.getType(), "scatter")) {
datasourceRequest.setQuery(qp.getSQLAsTmpScatter(sql, xAxis, yAxis, fieldCustomFilter,
extFilterList, extBubble, view));
} else if (StringUtils.equalsIgnoreCase("table-info", view.getType())) {
datasourceRequest
.setQuery(qp.getSQLAsTmpTableInfo(sql, xAxis, fieldCustomFilter, extFilterList, ds, view));
} else {
datasourceRequest
.setQuery(qp.getSQLAsTmp(sql, xAxis, yAxis, fieldCustomFilter, extFilterList, view));
}
} else if (StringUtils.equalsIgnoreCase(table.getType(), "union")) {
DataTableInfoDTO dt = new Gson().fromJson(table.getInfo(), DataTableInfoDTO.class);
Map<String, Object> sqlMap = dataSetTableService.getUnionSQLDatasource(dt, ds);
String sql = (String) sqlMap.get("sql");
if (StringUtils.equalsIgnoreCase("text", view.getType())
|| StringUtils.equalsIgnoreCase("gauge", view.getType())
|| StringUtils.equalsIgnoreCase("liquid", view.getType())) {
datasourceRequest
.setQuery(qp.getSQLSummaryAsTmp(sql, yAxis, fieldCustomFilter, extFilterList, view));
} else if (StringUtils.containsIgnoreCase(view.getType(), "stack")) {
datasourceRequest.setQuery(
qp.getSQLAsTmpStack(sql, xAxis, yAxis, fieldCustomFilter, extFilterList, extStack, view));
} else if (StringUtils.containsIgnoreCase(view.getType(), "scatter")) {
datasourceRequest.setQuery(qp.getSQLAsTmpScatter(sql, xAxis, yAxis, fieldCustomFilter,
extFilterList, extBubble, view));
} else if (StringUtils.equalsIgnoreCase("table-info", view.getType())) {
datasourceRequest
.setQuery(qp.getSQLAsTmpTableInfo(sql, xAxis, fieldCustomFilter, extFilterList, ds, view));
} else {
datasourceRequest
.setQuery(qp.getSQLAsTmp(sql, xAxis, yAxis, fieldCustomFilter, extFilterList, view));
}
}
if (resultMode.equals("all")) {
data = datasourceProvider.getPageData(datasourceRequest);
total = datasourceProvider.getDataTotal(datasourceRequest);
} else {
data = datasourceProvider.getData(datasourceRequest);
}
} else if (table.getMode() == 1) {// 抽取
// 连接doris，构建doris数据源查询
Datasource ds = (Datasource) CommonBeanFactory.getBean("DorisDatasource");
DatasourceProvider datasourceProvider = ProviderFactory.getProvider(ds.getType());
datasourceRequest.setDatasource(ds);
String tableName = "ds_" + table.getId().replaceAll("-", "_");
datasourceRequest.setTable(tableName);
QueryProvider qp = ProviderFactory.getQueryProvider(ds.getType());
if (StringUtils.equalsIgnoreCase("text", view.getType())
|| StringUtils.equalsIgnoreCase("gauge", view.getType())
|| StringUtils.equalsIgnoreCase("liquid", view.getType())) {
datasourceRequest.setQuery(qp.getSQLSummary(tableName, yAxis, fieldCustomFilter, extFilterList, view));
} else if (StringUtils.containsIgnoreCase(view.getType(), "stack")) {
datasourceRequest.setQuery(
qp.getSQLStack(tableName, xAxis, yAxis, fieldCustomFilter, extFilterList, extStack, ds, view));
} else if (StringUtils.containsIgnoreCase(view.getType(), "scatter")) {
datasourceRequest.setQuery(qp.getSQLScatter(tableName, xAxis, yAxis, fieldCustomFilter, extFilterList,
extBubble, ds, view));
} else if (StringUtils.equalsIgnoreCase("table-info", view.getType())) {
datasourceRequest
.setQuery(qp.getSQLTableInfo(tableName, xAxis, fieldCustomFilter, extFilterList, ds, view));
} else {
datasourceRequest
.setQuery(qp.getSQL(tableName, xAxis, yAxis, fieldCustomFilter, extFilterList, ds, view));
}
// 仪表板有参数不实用缓存
if (!cache || CollectionUtils.isNotEmpty(requestList.getFilter())
|| CollectionUtils.isNotEmpty(requestList.getLinkageFilters())
|| CollectionUtils.isNotEmpty(requestList.getDrill())
|| CollectionUtils.isNotEmpty(rowPermissionFields)
|| fields.size() != columnPermissionFields.size()) {
if (resultMode.equals("all")) {
data = datasourceProvider.getPageData(datasourceRequest);
total = datasourceProvider.getDataTotal(datasourceRequest);
} else {
data = datasourceProvider.getData(datasourceRequest);
}
} else {
try {
data = cacheViewData(datasourceProvider, datasourceRequest, view.getId());
} catch (Exception e) {
LogUtil.error(e);
} finally {
// 如果当前对象被锁 且 当前线程冲入次数 > 0 则释放锁
if (lock.isLocked() && lock.getHoldCount() > 0) {
lock.unlock();
}
}
}
}
// 同比/环比计算，通过对比类型和数据设置，计算出对应指标的结果，然后替换结果data数组中的对应元素
// 如果因维度变化（如时间字段缺失，时间字段的展示格式变化）导致无法计算结果的，则结果data数组中的对应元素全置为null
// 根据不同图表类型，获得需要替换的指标index array
for (int i = 0; i < yAxis.size(); i++) {
ChartViewFieldDTO chartViewFieldDTO = yAxis.get(i);
ChartFieldCompareDTO compareCalc = chartViewFieldDTO.getCompareCalc();
if (ObjectUtils.isEmpty(compareCalc)) {
continue;
}
if (StringUtils.isNotEmpty(compareCalc.getType())
&& !StringUtils.equalsIgnoreCase(compareCalc.getType(), "none")) {
String compareFieldId = compareCalc.getField();// 选中字段
String resultData = compareCalc.getResultData();// 数据设置
// 获取选中字段以及下标
List<ChartViewFieldDTO> checkedField = new ArrayList<>(xAxis);
if (StringUtils.containsIgnoreCase(view.getType(), "stack")) {
checkedField.addAll(extStack);
}
int timeIndex = 0;// 时间字段下标
ChartViewFieldDTO timeField = null;
for (int j = 0; j < checkedField.size(); j++) {
if (StringUtils.equalsIgnoreCase(checkedField.get(j).getId(), compareFieldId)) {
timeIndex = j;
timeField = checkedField.get(j);
}
}
// 计算指标对应的下标
int dataIndex = 0;// 数据字段下标
if (StringUtils.containsIgnoreCase(view.getType(), "stack")) {
dataIndex = xAxis.size() + extStack.size() + i;
} else {
dataIndex = xAxis.size() + i;
}
// 无选中字段，或者选中字段已经不在维度list中，或者选中字段日期格式不符合对比类型的，直接将对应数据置为null
if (ObjectUtils.isEmpty(timeField) || !checkCalcType(timeField.getDateStyle(), compareCalc.getType())) {
// set null
for (String[] item : data) {
item[dataIndex] = null;
}
} else {
// 计算 同比/环比
// 1，处理当期数据；2，根据type计算上一期数据；3，根据resultData计算结果
Map<String, String> currentMap = new LinkedHashMap<>();
for (String[] item : data) {
String[] dimension = Arrays.copyOfRange(item, 0, checkedField.size());
currentMap.put(StringUtils.join(dimension, "-"), item[dataIndex]);
}
for (int index = 0; index < data.size(); index++) {
String[] item = data.get(index);
String cTime = item[timeIndex];
String cValue = item[dataIndex];
// 获取计算后的时间，并且与所有维度拼接
String lastTime = calcLastTime(cTime, compareCalc.getType(), timeField.getDateStyle(),
timeField.getDatePattern());
String[] dimension = Arrays.copyOfRange(item, 0, checkedField.size());
dimension[timeIndex] = lastTime;
String lastValue = currentMap.get(StringUtils.join(dimension, "-"));
if (StringUtils.isEmpty(cValue) || StringUtils.isEmpty(lastValue)) {
item[dataIndex] = null;
} else {
if (StringUtils.equalsIgnoreCase(resultData, "sub")) {
item[dataIndex] = new BigDecimal(cValue).subtract(new BigDecimal(lastValue)).toString();
} else if (StringUtils.equalsIgnoreCase(resultData, "percent")) {
if (new BigDecimal(lastValue).compareTo(BigDecimal.ZERO) == 0) {
item[dataIndex] = null;
} else {
item[dataIndex] = new BigDecimal(cValue)
.divide(new BigDecimal(lastValue), 2, RoundingMode.HALF_UP)
.subtract(new BigDecimal(1))
.setScale(2, RoundingMode.HALF_UP)
.toString();
}
}
}
}
}
}
}
// 构建结果
Map<String, Object> map = new TreeMap<>();
// 图表组件可再扩展
Map<String, Object> mapChart = new HashMap<>();
if (StringUtils.equalsIgnoreCase(view.getRender(), "echarts")) {
if (StringUtils.containsIgnoreCase(view.getType(), "stack")) {
mapChart = transStackChartData(xAxis, yAxis, view, data, extStack, isDrill);
} else if (StringUtils.containsIgnoreCase(view.getType(), "scatter")) {
mapChart = transScatterData(xAxis, yAxis, view, data, extBubble, isDrill);
} else if (StringUtils.containsIgnoreCase(view.getType(), "radar")) {
mapChart = transRadarChartData(xAxis, yAxis, view, data, isDrill);
} else if (StringUtils.containsIgnoreCase(view.getType(), "text")
|| StringUtils.containsIgnoreCase(view.getType(), "gauge")
|| StringUtils.equalsIgnoreCase("liquid", view.getType())) {
mapChart = transNormalChartData(xAxis, yAxis, view, data, isDrill);
} else if (StringUtils.containsIgnoreCase(view.getType(), "chart-mix")) {
mapChart = transMixChartData(xAxis, yAxis, view, data, isDrill);
} else {
mapChart = transChartData(xAxis, yAxis, view, data, isDrill);
}
} else if (StringUtils.equalsIgnoreCase(view.getRender(), "antv")) {
if (StringUtils.containsIgnoreCase(view.getType(), "bar-stack")) {
mapChart = transStackChartDataAntV(xAxis, yAxis, view, data, extStack, isDrill);
} else if (StringUtils.containsIgnoreCase(view.getType(), "line-stack")) {
mapChart = transStackChartDataAntV(xAxis, yAxis, view, data, extStack, isDrill);
} else if (StringUtils.containsIgnoreCase(view.getType(), "scatter")) {
mapChart = transScatterDataAntV(xAxis, yAxis, view, data, extBubble, isDrill);
} else if (StringUtils.containsIgnoreCase(view.getType(), "radar")) {
mapChart = transRadarChartDataAntV(xAxis, yAxis, view, data, isDrill);
} else if (StringUtils.containsIgnoreCase(view.getType(), "text")
|| StringUtils.containsIgnoreCase(view.getType(), "gauge")
|| StringUtils.equalsIgnoreCase("liquid", view.getType())) {
mapChart = transNormalChartData(xAxis, yAxis, view, data, isDrill);
} else if (StringUtils.containsIgnoreCase(view.getType(), "chart-mix")) {
mapChart = transMixChartDataAntV(xAxis, yAxis, view, data, isDrill);
} else {
mapChart = transChartDataAntV(xAxis, yAxis, view, data, isDrill);
}
}
// table组件，明细表，也用于导出数据
Map<String, Object> mapTableNormal = transTableNormal(xAxis, yAxis, view, data, extStack, desensitizationList);
map.putAll(mapChart);
map.putAll(mapTableNormal);
List<DatasetTableField> sourceFields = dataSetTableFieldsService.getFieldsByTableId(view.getTableId());
map.put("sourceFields", sourceFields);
ChartViewDTO dto = new ChartViewDTO();
if (resultMode.equals("all")) {
view.setResultCount(total);// 设置数据总数
}
BeanUtils.copyBean(dto, view);
dto.setData(map);
dto.setSql(datasourceRequest.getQuery());
dto.setDrill(isDrill);
dto.setDrillFilters(drillFilters);
return dto;
}
5.backend/src/main/java/io/dataease/provider/datasource/DatasourceProvider.java
//增加getPageData
abstract public List<String[]> getPageData(DatasourceRequest datasourceRequest) throws Exception;
//增加getDataTotal
abstract public long getDataTotal(DatasourceRequest datasourceRequest) throws Exception;// 服务端分页6.backend/src/main/java/io/dataease/provider/datasource/ApiProvider.java
7.backend/src/main/java/io/dataease/provider/datasource/EsProvider.java
//增加getPageData接口，未做实现
@Override
public List<String[]> getPageData(DatasourceRequest datasourceRequest) throws Exception {
// TODO Auto-generated method stub
return null;
}
//增加getDataTotal接口，未做实现
@Override
public long getDataTotal(DatasourceRequest datasourceRequest) throws Exception {
// TODO Auto-generated method stub
return 0;
}
8.backend/src/main/java/io/dataease/controller/panel/server/LinkServer.java
//增加viewPageDetail接口实现
@Override
public Object viewPageDetail(String viewId, String panelId, ChartExtRequest requestList, int pageNo)
throws Exception {
HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes())
.getRequest();
String linkToken = request.getHeader(F2CLinkFilter.LINK_TOKEN_KEY);
DecodedJWT jwt = JWT.decode(linkToken);
Long userId = jwt.getClaim("userId").asLong();
requestList.setUser(userId);
return chartViewService.getPageData(viewId, requestList, pageNo);
}9.backend/src/main/java/io/dataease/controller/panel/api/LinkApi.java
//增加viewPageDeatail接口
@ApiOperation("分页视图详息")
@PostMapping("/viewDetail/{viewId}/{panelId}/{pageNO}")
Object viewPageDetail(@PathVariable("viewId") String viewId, @PathVariable("panelId") String panelId,
@RequestBody ChartExtRequest requestList,
@PathVariable("pageNO") int pageNo) throws Exception;
未完待续。。。
类似资料：