創作不易，關注、點贊，分享，轉發，評論，收藏，掌握網際網路前沿知識，支援一下，謝謝。
引言
提供多個資料庫中進行執行資料庫操作，并傳回相對應的結果集操作。便于減少背景代碼量，快速開發并擷取資料。
背景
在傳統開發過程中，需要編寫重複的代碼與資料庫互動，大大增加了項目的開發周期，基于此背景下設計一套基于配置實作與資料庫互動的基于架構。
提供資料分發功能，便于快速開發。
提供一套類似ORM架構。
軟體設計
參數設計
請求參數說明
| 參數類型 | 含義 | |
| column | String | 傳回列名 |
| dcName | String | 資料集名稱 |
| filter | String | 過濾參數值 |
| limit | int | 分頁參數 |
| start | int | 分頁參數 |
| sort | int | 排序參數（1、2、3、4、5等） |
| params | String | 傳遞參數（按順序執行時往下個資料集傳遞的過濾參數） |
傳回參數說明
| 參數名 | 參數類型 | 含義 |
| dcId | long | 資料集id |
| filter | String | 請求參數過濾值 |
| totalCount | long | 傳回總記錄數 |
| errorMsg | String | 傳回錯誤資訊 |
| success | boolean | 傳回狀态,是否成功 |
| code | Long | 狀态碼 |
| columnsNames | Map<String, Object> | 傳回資料<字段:名稱> |
| metadata | Map<String, Object> | 傳回資料<字段:類型> |
| strData | String | 傳回String |
| mapData | Map<String, Object> | 傳回對象 |
| listData | List | 傳回List |
| datas | List<Map<String, Object>> | 傳回List<Map> |
| empty | boolean | 傳回資料是否為空 |
接口設計
HTTP Rest API說明
| 接口名 | 接口路徑 | 傳回類型 |
| data | /dataservice/pageDataSet/data | ResultPattern |
| dataFromOrder | /dataservice/pageDataSet/dataFromOrder | ResultPattern |
| loadDataFromOrderSave | /dataservice/pageDataSet/loadDataFromOrderSave | ResultPattern |
資料流向設計
說明：如果該資料源是關系型資料庫，則需要通過資料連接配接池實作資料庫連接配接，優化系統性能。如果該資料源不是關系型資料庫類型，則需要根據該資料源去适配不同的處理資料方法，最終執行資料并傳回。
業務流程設計
說明：根據資料源和資料集相關配置資訊，與資料庫互動後傳回資料，接着對傳回的資料進行處理，根據參數資訊（單行單列，多行多列等）進行判斷，傳回指定的資料格式。
資料庫設計
資料集表設計
| 字段名稱 | 資料類型 | 長度 | 說明 |
| id | INT | Id | |
| name | VARCHAR | 255 | 資料集名稱 |
| group | VARCHAR | 255 | 組名稱 |
| alias | VARCHAR | 255 | 資料集别名 |
| dataset_type | VARCHAR | 255 | 資料集類型：單一、多個合并、不可查詢 |
| columns | VARCHAR | 255 | 傳回列名 |
| label_texts | VARCHAR | 255 | 傳回清單顯示名稱 |
| data_types | VARCHAR | 255 | 傳回列類型 |
| exec_type | VARCHAR | 255 | SQL表達式類型：QUERY、UPDATE、DELETE |
| exec_sql | VARCHAR | 255 | 表達式 |
| filter_param_names | VARCHAR | 255 | 過濾參數名稱 |
| filter_values | VARCHAR | 255 | 過濾參數值 |
| main_datasource_id | VARCHAR | 255 | 主資料源ID |
| union_datasource_ids | VARCHAR | 255 | 待合并資料源ID |
| union_dataset_ids | VARCHAR | 255 | 待合并資料集ID |
| union_columns | VARCHAR | 255 | 合并條件，格式為：字段名操作符字段名，比如字段名=字段名 |
| union_filter_values | VARCHAR | 255 | 合并過濾參數值 |
| is_write | CHAR | 1 | 是否可編輯 |
| is_enable | CHAR | 1 | 是否啟用 |
| remark | VARCHAR | 255 | 備注 |
| lm_timestamp | TIMESTAMP | 資料集名稱 | |
| 資料集别名 |
資料源表設計
| 字段名稱 | 資料類型 | 長度 | 說明 |
| id | INT | 資料源名稱 | |
| datasource_name | VARCHAR | 255 | 通路類型：HTTP，JDBC |
| access_mode | VARCHAR | 255 | 别名 |
| alias | VARCHAR | 255 | 擴充卡名稱 |
| adapter | VARCHAR | 255 | 資料庫驅動名稱 |
| driver_name | VARCHAR | 255 | 通路URL |
| url | VARCHAR | 255 | 資料庫類型 |
| database_type | VARCHAR | 255 | 資料庫名 |
| database | VARCHAR | 255 | 賬号 |
| username | VARCHAR | 255 | 密碼 |
| password | VARCHAR | 255 | 是否可編輯 |
| is_write | CHAR | 1 | 是否啟用 |
| is_enable | CHAR | 1 | 備注 |
| remark | VARCHAR | 255 | |
| lm_timestamp | TIMESTAMP | 資料源名稱 |
方法實作
說明：根據資料集過濾條件進行過濾，比如根據SQL表達式類型（增加，删除，修改，查詢，存儲過程）過濾，根據傳回結果類型（單行，多行，單行單列，多行單列）過濾，其次根據合并條件進行過濾并傳回值，根據SQL表達式，參數過濾值，參數進行過濾，根據傳回的列名，傳回列類型，傳回列顯示名稱進行過濾。
主要涉及的步驟如下所示：
●從接口中擷取參數params，根據id或名稱找到所對應的資料集。
●從參數params中得到filter過濾條件，根據過濾條件進行對資料的過濾。
●通過擴充卡，找到該資料源對應的實作類，實作對資料的處理。
●通過擴充卡處理業務邏輯，并傳回資料。
●如果資料集中存在合并過濾條件，則進行合并處理。
●通過過濾條件，将需要傳回的資料進行封裝，傳回資料，列名，字段等。
主要涉及技術如下所示：
擴充卡：提供擴充卡接口，不同的資料源通過擴充卡可以找到相應的加載資料庫方式。
資料源：使用Druid資料源，在保證線程安全的情況下提供擷取資料源和關閉資料源的方法。
編碼設計
代碼結構設計
| 名稱 | 說明 |
| 擴充卡代碼 | 通過提供各個擴充卡來實作資料處理 |
| 控制層代碼 | 提供可通路的API接口 |
| 服務層代碼 | 提供處理業務類方法實作 |
| 工具類代碼 | 提供字元串、日期等處理方法 |
代碼編制
擴充卡代碼
package com.demo.data.service.adapter;
import com.alibaba.druid.pool.DruidDataSource;
import com.demo.common.utils.StringUtils;
import com.demo.data.service.domain.PageDataSetEntity;
import com.demo.data.service.domain.PageDataSourceEntity;
import com.demo.data.service.properties.DataSourceProperties;
import com.demo.data.service.util.JdbcDataSourceUtil;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import javax.annotation.PostConstruct;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Component
public class JdbcAdapter extends AbstractDataAdapter {
static JdbcAdapter jdbcAdapter;
@PostConstruct
public void init() {
jdbcAdapter = this;
}
@Override
public List<Map<String, Object>> loadData(PageDataSourceEntity ds, PageDataSetEntity dc) {
JdbcTemplate jt = new JdbcTemplate();
DataSourceProperties dataSourceProperties = new DataSourceProperties();
dataSourceProperties.setUsername(ds.getUsername());
dataSourceProperties.setPassword(ds.getPassword());
dataSourceProperties.setDriverClassName(ds.getDriverName());
dataSourceProperties.setUrl(ds.getUrl());
try {
DruidDataSource dataSource = JdbcDataSourceUtil.getDataSource(dataSourceProperties, ds);
if (dataSource != null) {
jt.setDataSource(dataSource);
} else {
return null;
}
} catch (Exception e) {
e.printStackTrace();
}
String sql = dc.getExecSql();
return jt.queryForList(sql);
}
@Override
public synchronized List<Map<String, Object>> loadData(PageDataSourceEntity ds, List<String> sqls) {
JdbcTemplate jt = new JdbcTemplate();
DataSourceProperties dataSourceProperties = new DataSourceProperties();
dataSourceProperties.setUsername(ds.getUsername());
dataSourceProperties.setPassword(ds.getPassword());
dataSourceProperties.setDriverClassName(ds.getDriverName());
dataSourceProperties.setUrl(ds.getUrl());
try {
DruidDataSource dataSource = JdbcDataSourceUtil.getDataSource(dataSourceProperties, ds);
if (dataSource != null) {
jt.setDataSource(dataSource);
//jt.batchUpdate(sqls);
} else {
return null;
}
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
@Override
public synchronized List<Map<String, Object>> loadData(PageDataSourceEntity ds, PageDataSetEntity dc,
Map<String, Object> paramValue) {
/**
* 資料源
*/
// 擷取初始化後的druid資料源，SqlDBConfig存放了jdbcUrl、username、password
JdbcTemplate jt = new JdbcTemplate();
DataSourceProperties dataSourceProperties = new DataSourceProperties();
dataSourceProperties.setUsername(ds.getUsername());
dataSourceProperties.setPassword(ds.getPassword());
dataSourceProperties.setDriverClassName(ds.getDriverName());
dataSourceProperties.setUrl(ds.getUrl());
try {
DruidDataSource dataSource = JdbcDataSourceUtil.getDataSource(dataSourceProperties, ds);
if (dataSource != null) {
jt.setDataSource(dataSource);
} else {
return null;
}
} catch (Exception e) {
e.printStackTrace();
}
//拼接sql
String sql = dc.getExecSql();
String params = dc.getFilterParamNames();
if (params != null && !"".equals(params) && paramValue != null) {
String[] paramAttr = params.split("#");
for (String param : paramAttr) {
String value = paramValue.get(param) + "";
sql = sql.replace("#" + param + "#", value);
}
}
return jt.queryForList(sql);
}
@SuppressWarnings({"unchecked", "deprecation", "unused"})
@Override
public Map<String, Object> loadData(Map<String, Object> paramMap) {
//paramMap解析
PageDataSourceEntity ds = (PageDataSourceEntity) paramMap.get("dataSource");
PageDataSetEntity dc = (PageDataSetEntity) paramMap.get("dataSet");
String condition = (String) paramMap.get("condition");
DruidDataSource dataSource = new DruidDataSource();
JdbcTemplate jt = new JdbcTemplate();
DataSourceProperties dataSourceProperties = new DataSourceProperties();
dataSourceProperties.setUsername(ds.getUsername());
dataSourceProperties.setPassword(ds.getPassword());
dataSourceProperties.setDriverClassName(ds.getDriverName());
dataSourceProperties.setUrl(ds.getUrl());
try {
dataSource = JdbcDataSourceUtil.getDataSource(dataSourceProperties, ds);
if (dataSource != null) {
jt.setDataSource(dataSource);
} else {
return null;
}
} catch (Exception e) {
e.printStackTrace();
}
//拼湊sql
String sql = dc.getExecSql();
Map<String, Object> paramValue = (Map<String, Object>) paramMap.get("paramValue");
String params = dc.getFilterParamNames();
//用來對參數值進行處理
if (params != null && !"".equals(params) && paramValue != null) {
String[] paramAttr = params.split("#");
for (String param : paramAttr) {
String value = paramValue.get(param) + "";
// if (value == null || "null".equals(value)) {
// continue;
// }
sql = sql.replace("${" + param + "}", value);
}
}
Map<String, Object> map = new HashMap<>();
//擷取分頁資料
if ("mysql".equals(ds.getDatabaseType())) {
if (condition != null) {
//擷取分頁之前總記錄數
String countSql = "select count(*) from (" + sql + ") alias";
long totalCount = jt.queryForObject(countSql, Long.class);
map.put("totalCount", totalCount);
sql += " limit " + condition;
}
}
System.out.println(sql);
List<Map<String, Object>> list = new ArrayList<>();
//Object list=null;
/**
* 判斷是否是增、删、改操作,有疑問
*/
String execType = dc.getExecType();
//判斷是否為級聯操作
String cascade = (String) paramMap.get("cascade");
if (cascade.equals("Y")){
if ("ADD".equals(dc.getExecType()) || "DELETE".equals(dc.getExecType()) || "UPDATE".equals(dc.getExecType())) {
map.put("sql", sql);
}
}else{
if ("ADD".equals(dc.getExecType()) || "DELETE".equals(dc.getExecType()) || "UPDATE".equals(dc.getExecType())) {
jt.execute(sql);
} else if ("QUERY".equals(dc.getExecType())) {//查
//logger.info(sql);
list = jt.queryForList(sql);
} else if ("PROC".equals(dc.getExecType())){
System.out.println(sql);
if (dc.getReturnType().equals("NODATA")) {//NORS
jt.execute(sql);
//logger.info("NODATA:"+sql);
} else if (dc.getReturnType().equals("NORS")) {
list = this.getResultSet(sql, jt, dc);
//logger.info("NORS:"+sql);
} else if (dc.getReturnType().equals("RS")) {
//logger.info("RS:"+sql);
list = this.getResultSet(sql, jt);
}else if(dc.getReturnType().equals("MRS")){
list = this.getMultipleResultSet(sql, jt,dc);
}
}
}
try {
dataSource.getConnection().close();
} catch (SQLException e) {
e.printStackTrace();
}
//if(list==null)list=new ArrayList<Object>();
map.put("datas", list);
return map;
}
public List<Map<String, Object>> getResultSet(String sql, JdbcTemplate jdbcTemplate) {
return (List<Map<String, Object>>) jdbcTemplate.execute(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement cs = con.prepareCall(sql);
return cs;
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException {
List<Map<String, Object>> list = new ArrayList<>();
ResultSet rs = cs.executeQuery();
ResultSetMetaData rmd = rs.getMetaData();
int columnCount = rmd.getColumnCount();
while (rs.next()) {
Map<String, Object> rowMap = new HashMap<>(columnCount);
for (int i = 1; i <= columnCount; i++) {
rowMap.put(rmd.getColumnName(i), rs.getObject(i));
}
list.add(rowMap);
}
rs.close();
return list;
}
});
}
public List<Map<String,Object>> getMultipleResultSet(String sql, JdbcTemplate jdbcTemplate,PageDataSetEntity dc) {
String[] outtypes = StringUtils.substringAfter(dc.getDataTypes(),"#34;).split("#");
List<Integer> list = this.checkDataType(outtypes);
Integer[] sqlTypes = list.toArray(new Integer[0]);
//Integer[] sqlTypes = (Integer[]) integers.toArray();
String[] outcolumns=StringUtils.substringAfter(dc.getColumns(),"#34;).split("#");
return (List<Map<String, Object>>) jdbcTemplate.execute(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement cs = con.prepareCall(sql);
if (StringUtils.isNotEmpty(sqlTypes)) {
//String[] columns = null;
//columns = StringUtils.substringAfter(dc.getColumns(),"#34;).split("#");
for (int i = 0; i < outcolumns.length; i++) {
cs.registerOutParameter(i+1, sqlTypes[i]);// 注冊輸出參數的類型
}
}
return cs;
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
cs.executeQuery();
Map<String,Object> outRow=new HashMap<String,Object>();
outRow.put("out_count",outcolumns.length);
for(int i=0;i<outcolumns.length;i++){
outRow.put(outcolumns[i],cs.getObject(i+1));
}
list.add(outRow);
ResultSet rs =cs.getResultSet();
ResultSetMetaData rmd = rs.getMetaData();
int columnCount = rmd.getColumnCount();
while (rs.next()) {
Map<String, Object> rowMap = new HashMap<>(columnCount);
for (int i = 1; i <= columnCount; i++) {
rowMap.put(rmd.getColumnName(i), rs.getObject(i));
}
list.add(rowMap);
}
cs.getMetaData();
return list;
}
});
}
public List<Map<String, Object>> getResultSet(String sql, JdbcTemplate jdbcTemplate, PageDataSetEntity dc) {
String[] types = dc.getDataTypes().split("#");
List<Integer> list = this.checkDataType(types);
Integer[] sqlTypes = list.toArray(new Integer[0]);
return (List<Map<String, Object>>) jdbcTemplate.execute(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement cs = con.prepareCall(sql);
if (StringUtils.isNotEmpty(sqlTypes)) {
String[] columns = null;
columns = dc.getColumns().split("#");
for (int i = 0; i < columns.length; i++) {
cs.registerOutParameter(i+1, sqlTypes[i]);// 注冊輸出參數的類型
}
}
return cs;
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException {
List<Map<String, Object>> list = new ArrayList<>();
Map<String, Object> map = new HashMap<>();
cs.execute();
String[] columns = null;
columns = dc.getColumns().split("#");
for (int i = 0; i < columns.length; i++) {
map.put(columns[i],cs.getString(i+1));
}
list.add(map);
return list;
}
});
}
public List<Integer> checkDataType(String[] types) {
List<Integer> list = new ArrayList();
for (String params : types) {
int result = 0;
if (params.equals("string")) {
result = Types.VARCHAR;
} else if (params.equals("int") || params.equals("long")) {
result = Types.INTEGER;
} else if (params.equals("float")) {
result = Types.DOUBLE;
}
list.add(result);
}
return list;
}
}
服務層代碼
package com.demo.data.service.service.impl;
import com.alibaba.fastjson.JSON;
import com.fasterxml.jackson.databind.JavaType;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.demo.common.utils.StringUtils;
import com.demo.data.service.adapter.DataAdapter;
import com.demo.data.service.domain.PageDataSetEntity;
import com.demo.data.service.domain.PageDataSourceEntity;
import com.demo.data.service.dto.DataLoadParams;
import com.demo.data.service.dto.ResultPattern;
import com.demo.data.service.service.DataFacadeService;
import com.demo.data.service.service.PageDataSetService;
import com.demo.data.service.service.PageDataSourceService;
import com.demo.data.service.util.Constent;
import com.demo.data.service.util.DataServiceUtil;
import com.demo.data.service.util.ListMergeUtil;
import com.demo.data.service.util.UserDefinedDataSetUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import springfox.documentation.spring.web.json.Json;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
/**
* @author: demo
* @description: 處理資料分發相關實作方法
* @date: create in 2019/11/25 0025 13:22
*/
@Service
public class DataFacadeServiceImpl implements DataFacadeService {
@Autowired
PageDataSourceService dataSourceService;
@Autowired
PageDataSetService dataSetService;
public List<Map<String, Object>> loadDataFromDS(long dcId) {
PageDataSetEntity dc = dataSetService.selectPageDataSetById(dcId);
if (dc != null) {
PageDataSourceEntity ds = dataSourceService.selectPageDataSourceById(dc.getId());
if (ds != null) {
DataAdapter adapter = null;
try {
adapter = (DataAdapter) Class.forName(ds.getAdapter()).newInstance();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
List<Map<String, Object>> maps = adapter.loadData(ds, dc);
if (maps != null && maps.size() > 0) {
return maps;
}
}
}
return null;
}
public Map<String, Object> loadDataFromDSToMap(long dcId, String[] fields, String paramJsonValue) {
Map<String, Object> resultMap = new HashMap<String, Object>();
boolean success = true;
String errorMsg = "";
long count = 0;
Map<String, Object> meta = new HashMap<String, Object>();
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
try {
PageDataSetEntity dc = dataSetService.selectPageDataSetById(dcId);
// paramJsonValue
ObjectMapper mapper = new ObjectMapper();
JavaType javaType = mapper.getTypeFactory().constructParametricType(Map.class, String.class, Object.class);
Map<String, Object> pm = mapper.readValue(paramJsonValue, javaType);
System.out.println("pm:" + pm);
if (dc != null) {
PageDataSourceEntity ds = dataSourceService.selectPageDataSourceById(Long.valueOf(dc.getMainDatasourceId()));
if (ds != null) {
if (fields == null || fields.equals("")) {
// resultList = loadDataFromDS(dcId);
if (dc.getColumns() == null || dc.getColumns().trim().equals("")) {
} else {
fields = dc.getColumns().split("#");
}
}
/* * if (fields == null || fields.equals("")) { // resultList
* = loadDataFromDS(dcId); fields =
* dc.getColumns().split("#"); }*/
DataAdapter adapter = (DataAdapter) Class.forName(ds.getAdapter()).newInstance();
List<Map<String, Object>> dataList = adapter.loadData(ds, dc, pm);
if (dataList != null && fields != null && !fields.equals("")) {
for (Map<String, Object> tmp : dataList) {
Map<String, Object> mp = new HashMap<String, Object>();
for (String fieldName : fields) {
mp.put(fieldName, tmp.get(fieldName));
}
resultList.add(mp);
}
} else {
resultList = dataList;
}
}
String[] filterFields = dc.getColumns().split("#");
if (fields != null && !fields.equals("")) {
filterFields = fields;
}
String[] types = dc.getDataTypes().split("#");
String[] allFields = dc.getColumns().split("#");
for (int i = 0; i < allFields.length; i++) {
for (String fieldName : filterFields) {
if (fieldName.equals(allFields[i])) {
meta.put(fieldName, types[i]);
}
}
}
}
} catch (Exception e) {
//logger.error(e.getMessage());
e.printStackTrace();
errorMsg = e.getMessage();
success = false;
}
resultMap.put("totalCount", count);
resultMap.put("metadata", meta);
resultMap.put("datas", resultList);
resultMap.put("success", success);
resultMap.put("errorMsg", errorMsg);
return resultMap;
}
public List<Map<String, Object>> loadDataFromDSToList(long dcId, String[] fields) {
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
if (fields == null || fields.equals("")) {
resultList = loadDataFromDS(dcId);
} else {
PageDataSetEntity dc = dataSetService.selectPageDataSetById(dcId);
if (dc != null) {
PageDataSourceEntity ds = dataSourceService.selectPageDataSourceById(Long.valueOf(dc.getMainDatasourceId()));
if (ds != null) {
DataAdapter adapter = null;
try {
adapter = (DataAdapter) Class.forName(ds.getAdapter()).newInstance();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
List<Map<String, Object>> dataList = adapter.loadData(ds, dc);
if (dataList != null) {
for (Map<String, Object> tmp : dataList) {
Map<String, Object> mp = new HashMap<String, Object>();
for (String fieldName : fields) {
mp.put(fieldName, tmp.get(fieldName));
}
resultList.add(mp);
}
}
}
}
}
return resultList;
}
public Map<String, Object> loadDataFromDSToMap(long dcId, String[] fields) {
Map<String, Object> resultMap = new HashMap<String, Object>();
boolean success = true;
String errorMsg = "";
long count = 0;
Map<String, Object> meta = new HashMap<String, Object>();
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
try {
PageDataSetEntity dc = dataSetService.selectPageDataSetById(dcId);
if (dc != null) {
PageDataSourceEntity ds = dataSourceService.selectPageDataSourceById(Long.valueOf(dc.getMainDatasourceId()));
if (ds != null) {
if (fields == null || fields.equals("")) {
// resultList = loadDataFromDS(dcId);
if (dc.getColumns() == null || dc.getColumns().trim().equals("")) {
} else {
fields = dc.getColumns().split("#");
}
}
// if (fields == null || fields.equals("")) {
// resultList = loadDataFromDS(dcId);
// } else {
System.out.println("==============------------");
DataAdapter adapter = (DataAdapter) Class.forName(ds.getAdapter()).newInstance();
List<Map<String, Object>> dataList = adapter.loadData(ds, dc);
// 根據傳入的field過濾資料
if (dataList != null && fields != null && !fields.equals("")) {
for (Map<String, Object> tmp : dataList) {
Map<String, Object> mp = new HashMap<String, Object>();
for (String fieldName : fields) {
mp.put(fieldName, tmp.get(fieldName));
}
resultList.add(mp);
}
} else {
resultList = dataList;
}
}
// 封裝metadata資料類型
if (dc.getColumns() != null) {
String[] filterFields = dc.getColumns().split("#");
if (fields != null && !fields.equals("")) {
filterFields = fields;
}
String[] types = dc.getDataTypes().split("#");
String[] allFields = dc.getColumns().split("#");
for (int i = 0; i < allFields.length; i++) {
for (String fieldName : filterFields) {
if (fieldName.equals(allFields[i])) {
meta.put(fieldName, types[i]);
}
}
}
}
}
// }
} catch (Exception e) {
//logger.error(e.getMessage());
e.printStackTrace();
errorMsg = e.getMessage();
success = false;
}
resultMap.put("totalCount", count);
resultMap.put("metadata", meta);
resultMap.put("datas", resultList);
resultMap.put("success", success);
resultMap.put("errorMsg", errorMsg);
return resultMap;
}
@SuppressWarnings("unchecked")
@Transactional(rollbackFor = Exception.class)
public ResultPattern loadDataFromDSToMap(Map<String, Object> map) {
//擷取資料集id
String dcId = String.valueOf(map.get("dcId"));
String[] fields = (String[]) map.get("fields");
String filter = (String) map.get("filter");
String condition = (String) map.get("condition");
String type = (String) map.get("type");
String cascade = (String) map.get("cascade");
ResultPattern resultPattern = new ResultPattern();
boolean success = true;
String errorMsg = "";
long count = 1L;
boolean empty = true;
long code = 200;
Map<String, Object> meta = new HashMap<>();
Map<String, Object> fieldNames = new HashMap<>();
List<Map<String, Object>> resultList = new ArrayList<>();
try {
PageDataSetEntity dc = dataSetService.selectPageDataSetById(Long.valueOf(dcId));
Map<String, Object> pm = null;
ObjectMapper mapper = new ObjectMapper();
String a = dc.getFilterValues();
if (filter != null) {
JavaType javaType = mapper.getTypeFactory().constructParametricType(Map.class, String.class,
Object.class);
pm = mapper.readValue(filter, javaType);
} else if (dc.getFilterValues() != null && !dc.getFilterValues().equals("")) {
//如果前台沒有傳過來過濾參數，那就判斷一下資料集中是否已配置過濾參數，如果配置，取資料集中的配置
JavaType javaType = mapper.getTypeFactory().constructParametricType(Map.class, String.class,
Object.class);
pm = mapper.readValue(dc.getFilterValues(), javaType);
}
if (dc != null) {
PageDataSourceEntity ds = dataSourceService.selectPageDataSourceById(Long.valueOf(dc.getMainDatasourceId()));
if (ds != null) {
if (fields == null || fields.equals("")) {
//如果資料集中的過濾列為空
if (dc.getColumns() == null || dc.getColumns().trim().equals("")) {
} else {
fields = StringUtils.substringBefore(dc.getColumns(), "#34;).split("#");
}
}
// 封裝參數
Map<String, Object> paramMap = new ConcurrentHashMap<String, Object>();
paramMap.put("dataSource", ds);
paramMap.put("dataSet", dc);
if (pm != null && pm.size() > 0) {
paramMap.put("paramValue", pm);
}
if (condition != null) {
paramMap.put("condition", condition);
}
paramMap.put("cascade", "Y");
Map<String, Object> loadMap = null;
DataAdapter adapter = (DataAdapter) Class.forName(ds.getAdapter()).newInstance();
//加載擴充卡，并傳回資料
loadMap = adapter.loadData(paramMap);
List<Map<String, Object>> dataList = null;
if (loadMap != null) {
if (loadMap.get("totalCount") != null) {
count = (Long) loadMap.get("totalCount");
}
if (loadMap.get("datas") != null) {
dataList = (List<Map<String, Object>>) loadMap.get("datas");
// 根據傳入的field過濾資料
if (dataList != null && fields != null && !"".equals(fields)) {
if (dataList != null && !dataList.isEmpty()) {
// 根據傳入的field過濾資料
resultList = filterCloumn(dataList, fields);
if (resultList != null && !resultList.isEmpty()) {
if (dc.getReturnType().equals("MRSC")) {
resultPattern.setListData(resultList);
} else if (dc.getReturnType().equals("SRMC") || dc.getReturnType().equals("NORS")) {//傳回map
Map<String, Object> data = resultList.get(0);
resultPattern.setMapData(data);
//傳回listMap
} else if (dc.getReturnType().equals("MRMC") || dc.getReturnType().equals("RS")) {//傳回listMap
resultPattern.setDatas(resultList);
} else if (dc.getReturnType().equals("SRSC")) {//傳回string
Map<String, Object> resMap = resultList.get(0);
Set<String> keSet = resMap.keySet();
for (Iterator<String> iterator = keSet.iterator(); iterator.hasNext(); ) {
String string = iterator.next();
resultPattern.setStrData(resMap.get(string).toString());
}
} else if (dc.getReturnType().equals("MRS")) {
Map<String, Object> resMap = resultList.get(0);
resultList.remove(0);
resultPattern.setDatas(resultList);
resultPattern.setMapData(resMap);
}
} else {
empty = false;
}
}
} else {
resultList = dataList;
}
}
}
}
// 封裝metadata資料類型
if (dc.getColumns() != null && !"".equals(dc.getColumns())) {
String[] filterFields = dc.getColumns().replace("#34;, "#").split("#");
if (fields != null && !"".equals(fields)) {
filterFields = fields;
}
String[] types = dc.getDataTypes().replace("#34;, "#").split("#");
String[] allFields = dc.getColumns().replace("#34;, "#").split("#");
String[] name = dc.getLabelTexts().replace("#34;, "#").split("#");
for (int i = 0; i < allFields.length; i++) {
for (String fieldName : filterFields) {
if (fieldName.equals(allFields[i])) {
meta.put(fieldName, types[i]);
fieldNames.put(fieldName, name[i]);
}
}
}
}
}
} catch (Exception e) {
code = Integer.parseInt(Constent.getServererror());
errorMsg = e.getMessage();
success = false;
resultPattern.setCode(code);
resultPattern.setErrorMsg(errorMsg);
resultPattern.setMetadata(meta);
resultPattern.setColumnsNames(fieldNames);
resultPattern.setSuccess(success);
resultPattern.setTotalCount(count);
resultPattern.setDcId(Long.parseLong(dcId));
resultPattern.setFilter(filter);
resultPattern.setEmpty(empty);
e.printStackTrace();
return resultPattern;
}
resultPattern.setCode(code);
resultPattern.setErrorMsg(errorMsg);
resultPattern.setMetadata(meta);
resultPattern.setColumnsNames(fieldNames);
resultPattern.setSuccess(success);
resultPattern.setTotalCount(count);
resultPattern.setDcId(Long.parseLong(dcId));
resultPattern.setFilter(filter);
resultPattern.setEmpty(empty);
return resultPattern;
}
// 根據配置字段進行參數過濾
public List<Map<String, Object>> filterCloumn(List<Map<String, Object>> objList, String[] fields) {
List<Map<String, Object>> newObjList = new ArrayList<Map<String, Object>>();
for (Map<String, Object> tmp : objList) {
Map<String, Object> mp = new HashMap<String, Object>();
for (String fieldName : fields) {
mp.put(fieldName.trim(), tmp.get(fieldName.trim()));
}
newObjList.add(mp);
}
return newObjList;
}
@Transactional(rollbackFor = Exception.class)
public ResultPattern loadDataFromComplexDSToMap(Map<String, Object> map) {
String dcId = String.valueOf(map.get("dcId"));
String dcName = (String) map.get("dcName");
String[] fields = (String[]) map.get("fields");
ResultPattern resultPattern = new ResultPattern();
boolean success = true;
String errorMsg = "";
long count = 1L;
boolean empty = true;
Map<String, Object> meta = new HashMap<>();
Map<String, Object> fieldNames = new HashMap<>();
List<Map<String, Object>> resultList = new ArrayList<>();
try {
PageDataSetEntity cdc = new PageDataSetEntity();
if (dcName != null) {
cdc = dataSetService.selectPageDataSetByName(dcName);
}
//PageDataSetEntity cdc= dataSetService.getById(dcId);
if (cdc != null) {
//擷取合并資料集id
String dcIds = cdc.getUnionDatasetIds();
//擷取合并資料集合并條件
String joinParam = cdc.getUnionColumns();
if (dcIds != null && !"".equals(dcIds)) {
String[] dcArray = dcIds.split("#");
String[] joinParamArray = null;
if (joinParam == null || "".equals(joinParam)) {
if (dcArray.length == 1) {
} else {
success = false;
}
} else {
joinParamArray = joinParam.split("#");
}
List<Map<String, Object>> result = new ArrayList<>();
for (int i = 0; dcArray != null && i < dcArray.length; i++) {
String singleDcId = dcArray[i];
map.remove("dcId");
map.remove("fields");
map.put("dcId", Long.parseLong(singleDcId));
ResultPattern rp = loadDataFromDSToMap(map);
//count = rp.getTotalCount();
List<Map<String, Object>> tmpList = null;
if (rp != null) {
tmpList = rp.getDatas();
}
String keys = null;
if (i > 0) {
keys = joinParamArray[i - 1];
} else if (i == 0 && (tmpList == null || tmpList.isEmpty())) {
break;
}
// list合并---關鍵處理
if (cdc.getRemark() != null && "default".equals(cdc.getRemark())) {
result = UserDefinedDataSetUtil.leftJoin(result, tmpList, keys);
} else if (cdc.getRemark() != null && "severity".equals(cdc.getRemark())) {
result = UserDefinedDataSetUtil.leftJoinSeverity(result, tmpList, keys);
} else {
result = ListMergeUtil.leftJoin(result, tmpList, keys);
}
if (cdc.getRemark() != null && "alarm".equals(cdc.getRemark())) {
result = UserDefinedDataSetUtil.getData(result, keys);
}
}
count = result.size();
// 過濾field
if (fields == null) {
if (cdc.getColumns() == null || "".equals(cdc.getColumns().trim())) {
} else {
fields = cdc.getColumns().split("#");
}
}
if (result.size() != 0 && fields != null) {
resultList = filterCloumn(result, fields);
} else {
resultList = result;
}
} else {
ResultPattern rp = loadDataFromDSToMap(map);
return rp;
}
// 封裝metadata資料類型
if (cdc.getColumns() != null && !"".equals(cdc.getColumns())) {
String[] filterFields = cdc.getColumns().split("#");
if (fields != null) {
filterFields = fields;
}
String[] types = cdc.getDataTypes().split("#");
String[] allFields = cdc.getColumns().split("#");
String[] name = cdc.getLabelTexts().split("#");
for (int i = 0; i < allFields.length; i++) {
for (String fieldName : filterFields) {
if (fieldName.equals(allFields[i])) {
meta.put(fieldName, types[i]);
fieldNames.put(fieldName, name[i]);
}
}
}
}
} else {
errorMsg = "該資料集在資料庫不存在，請查證";
}
if (resultList.size() != 0 && !resultList.isEmpty()) {
empty = false;
} else {
success = false;
empty = true;
}
} catch (Exception e) {
errorMsg = e.getMessage();
success = false;
e.printStackTrace();
} finally {
resultPattern.setDatas(resultList);
resultPattern.setErrorMsg(errorMsg);
resultPattern.setMetadata(meta);
resultPattern.setColumnsNames(fieldNames);
resultPattern.setSuccess(success);
resultPattern.setTotalCount(count);
resultPattern.setDcId(Long.parseLong(dcId));
resultPattern.setFilter(map.get("filter") + "");
resultPattern.setEmpty(empty);
}
return resultPattern;
}
@Override
public ResultPattern loadDataFromOrder(List<DataLoadParams> list) {
//排序後的集合
List<DataLoadParams> dataLoadParamsList = orderList(list);
ResultPattern resultPattern = new ResultPattern();
Map<String, Object> map = new HashMap<>();
for (DataLoadParams dataLoadParams : dataLoadParamsList) {
if (StringUtils.isNotEmpty(map)) {
dataLoadParams.setFilter(JSON.toJSONString(map));
map.clear();
}
resultPattern = DataServiceUtil.dataLoad(dataLoadParams);
String[] params = null;
if (StringUtils.isNotEmpty(dataLoadParams.getParams())) {
//擷取需要往下傳遞的參數
params = dataLoadParams.getParams().split("#");
}
if (StringUtils.isNotEmpty(resultPattern.getMapData())) {
Map<String, Object> mapData = resultPattern.getMapData();
//封裝接下去需要傳的過濾參數
for (String param : params) {
//封裝filterVlaue,為過濾值
String filterValue = String.valueOf(mapData.get(param));
map.put(param, filterValue);
}
}
}
return resultPattern;
}
@Override
public ResultPattern loadDataFromOrderSave(List<DataLoadParams> list) {
//排序後的集合
List<DataLoadParams> dataLoadParamsList = orderList(list);
ResultPattern resultPattern = new ResultPattern();
List<Map<String, Object>> datas = new ArrayList<>();
for (DataLoadParams dataLoadParams : dataLoadParamsList) {
if (StringUtils.isNotEmpty(datas)) {
List<String> sqls=new ArrayList<String>();
for (Map<String, Object> map : datas) {
dataLoadParams.setFilter(JSON.toJSONString(map));
resultPattern = DataServiceUtil.dataLoad(dataLoadParams);
String sql = resultPattern.getSql();
sqls.add(sql);
}
return resultPattern;
}
resultPattern = DataServiceUtil.dataLoad(dataLoadParams);
//擷取執行之後的資料
datas = resultPattern.getDatas();
}
return resultPattern;
}
@Override
public List<DataLoadParams> orderList(List<DataLoadParams> list) {
Collections.sort(list, new Comparator<DataLoadParams>() {
public int compare(DataLoadParams o1, DataLoadParams o2) {
// 按照傳入參數的排序字段進行升序排列
if (o1.getSort() > o2.getSort()) {
return 1;
}
if (o1.getSort() == o2.getSort()) {
return 0;
}
return -1;
}
});
return list;
}
}
工具類代碼
處理集合合并代碼：
package com.demo.data.service.util;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class UserDefinedDataSetUtil {
/**
* 自定義資料封裝
*
* @param list
* @param keys 比較符
* @return
* @category alarm
*/
public static List<Map<String, Object>> getData(
List<Map<String, Object>> list, String keys) {
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
if (keys == null) {
return list;
}
for (Map<String, Object> obj : list) {
int state = 0;
for (Map<String, Object> newObj : resultList) {
String value1 = newObj.get("classId").toString();
String value2 = obj.get("classId").toString();
if (value1 != null && value1.equals(value2)) {
state = 1;
String num1 = newObj.get("number") == null ? "0" : (newObj.get("number") + "");
String num2 = obj.get("number") == null ? "0" : (obj.get("number") + "");
if (obj.get("severity") != null && newObj.get("severity") != null) {
if (Integer.parseInt(newObj.get("severity") + "") < Integer.parseInt(obj.get("severity") + "")) {
newObj.remove("severity");
newObj.put("severity", Integer.parseInt(obj.get("severity") + ""));
}
} else if (obj.get("severity") != null && newObj.get("severity") == null) {
newObj.remove("severity");
newObj.put("severity", Integer.parseInt(obj.get("severity") + ""));
}
newObj.remove("number");
int count = Integer.parseInt(num1) + Integer.parseInt(num2);
newObj.put("number", count == 0 ? null : count);
newObj.put("event_category", newObj.get("event_category") + ",'" + obj.get("event_category").toString().trim() + "'");
break;
}
}
if (state != 1) {
obj.put("event_category", "'" + obj.get("event_category").toString().trim() + "'");
resultList.add(obj);
}
}
return resultList;
}
public static List<Map<String, Object>> leftJoin(List<Map<String, Object>> left, List<Map<String, Object>> right, String keys) {
if (left == null || left.size() == 0) {
return right;
} else if (right == null || right.size() == 0) {
return left;
} else {
if (keys == null) { //如果比較的關鍵字為空，預設選中第一個list
return left;
}
String[] key = keys.split("=");
for (Map<String, Object> lm : left) {
boolean bool = true;
for (Map<String, Object> rm : right) {
if (lm.get(key[0]) != null && rm.get(key[1]) != null && lm.get(key[0]).toString().trim().equals(rm.get(key[1]).toString().trim())) {
if (bool) {
lm.putAll(rm);
bool = false;
} else {
rm.remove(key[1]);
for (String k : rm.keySet()) {
if (lm.get(k) != null) {
lm.put(k, lm.get(k) + "," + rm.get(k));
} else {
lm.put(k, rm.get(k));
}
}
}
}
}
}
return left;
}
}
public static List<Map<String, Object>> leftJoinSeverity(List<Map<String, Object>> left, List<Map<String, Object>> right, String keys) {
if (left == null || left.size() == 0) {
return right;
} else if (right == null || right.size() == 0) {
return left;
} else {
if (keys == null) { //如果比較的關鍵字為空，預設選中第一個list
return left;
}
String[] key = keys.split("=");
for (Map<String, Object> lm : left) {
for (Map<String, Object> rm : right) {
if (lm.get(key[0]) != null && rm.get(key[1]) != null && lm.get(key[0]).toString().trim().equals(rm.get(key[1]).toString().trim())) {
String severity = rm.get("severity") + "";
if (severity.equals("3")) {
lm.put("warning", rm.get("number"));
} else if (severity.equals("4")) {
lm.put("serious", rm.get("number"));
} else if (severity.equals("5")) {
lm.put("major", rm.get("number"));
} else {
lm.putAll(rm);
}
}
}
}
return left;
}
}
}