创作不易，关注、点赞，分享，转发，评论，收藏，掌握互联网前沿知识，支持一下，谢谢。
引言
提供多个数据库中进行执行数据库操作，并返回相对应的结果集操作。便于减少后台代码量，快速开发并获取数据。
背景
在传统开发过程中，需要编写重复的代码与数据库交互，大大增加了项目的开发周期，基于此背景下设计一套基于配置实现与数据库交互的基于框架。
提供数据分发功能，便于快速开发。
提供一套类似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;
}
}
}