0301
package com.cmbchina.monitor.utils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.parser.ParserException;
import com.alibaba.druid.sql.parser.SQLParserUtils;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.cmbchina.monitor.dao.mapper.ApiConfigMapper;
import com.cmbchina.monitor.dao.mapper.CopybookFieldMapper;
import com.cmbchina.monitor.entity.configs.ApiConfigs;
import com.cmbchina.monitor.entity.configs.CopybookFieldConfig;
import org.springframework.util.StringUtils;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import static com.alibaba.druid.sql.SQLUtils.toSQLString;
public class SqlCheckUtil {
public static void main(String[] args) {
String str = "UPDATE COXDBO.COPYBOOK_FIELD_CONFIG_A SET FILLER = 0 WHERE COPYBOOK = 'CPS102' AND COLUMN_NAME='CM_EMPTY_UACCT_FLAG';";
// sqlFormat(str);
System.out.println(mergeContinuetyBlank(str));
System.out.println(getUpdateTableName(str));
// System.out.println(str.indexOf("WHERE"));
// System.out.println(str.substring(str.indexOf("WHERE")));
System.out.println(sqlToBack(str, null, null));
System.out.println(getUpdateWhere(str));
}
/**
* 格式化显示sql语句
*
* @param sql
* @return
*/
public static String sqlFormat(String sql) {
if (sql == null || "".equals(sql.trim()))
return "";
List<SQLStatement> statementList = null;
SQLStatementParser parser = null;
try {
parser = SQLParserUtils.createSQLStatementParser(sql, "mysql");
statementList = parser.parseStatementList();
} catch (ParserException e) {
System.out.println("SQL转换中发生了错误：" + e.getMessage());
throw e;
}
return toSQLString(statementList, "mysql");
}
/**
* 检查sql语法是否正确
*
* @param sql
* @return
*/
public static Boolean checkSql(String sql) {
if (sql == null || "".equals(sql.trim()))
return false;
List<SQLStatement> statementList = null;
SQLStatementParser parser = null;
try {
parser = SQLParserUtils.createSQLStatementParser(sql, "oracle");
statementList = parser.parseStatementList();
} catch (ParserException e) {
return false;
}
return true;
}
public static Map<String,Object> sqlToBack(String sql, CopybookFieldMapper copybookFieldMapper, ApiConfigMapper apiConfigMapper) {
if (StringUtils.isEmpty(sql))
return null;
Map<String,Object> resultMap = new HashMap<>();
//合并连续空格
sql = mergeContinuetyBlank(sql);
//判断时更新还是插入操作
if (sql.contains("INSERT INTO")) {
//获取表名
String tableName = getInsertTableName(sql);
//判断时api表还是field表
if (tableName.contains("API")) {
//获取API_ID
String apiId = getInsertColumnValue(sql, "API_ID");
//生成回退语句
String backSql = "DELETE FROM COXDBO.BILL_QUERY_API_CONFIG WHERE API_ID=" + apiId;
//TODO 插入历史记录
resultMap.put("backSql",backSql);
resultMap.put("type","INSERT");
resultMap.put("tableName",tableName);
resultMap.put("configEntity",null);
return resultMap;
} else {
//获取插入primaryKey
String copybook = getInsertColumnValue(sql, "COPYBOOK");
String fieldNo = getInsertColumnValue(sql, "FIELD_NO");
//生成回退语句
String backSql = "DELETE FROM " + tableName + " WHERE COPYBOOK=" + copybook + " AND FIELD_NO=" + fieldNo + "";
//TODO 插入历史记录
resultMap.put("backSql",backSql);
resultMap.put("type","INSERT");
resultMap.put("tableName",tableName);
resultMap.put("configEntity",null);
return resultMap;
}
} else {
//获取表名
String tableName = getUpdateTableName(sql);
//确定回退表信息
if (tableName.contains("API")) {
//获取where过滤条件
Map<String, Object> whereMap = new HashMap<>();
Map<String, Object> updateWhere = getUpdateWhere(sql);
if (updateWhere !=null){
whereMap = updateWhere;
}
//获取更新前的值
List<ApiConfigs> api_id = apiConfigMapper.query(1, 2, whereMap.get("API_ID").toString());
ApiConfigs apiConfigs = null;
if (api_id != null)
apiConfigs = api_id.get(0);
//生成回退语句
String backSql = "";
String apiInsertBack = getApiInsertBack(apiConfigs);
if (apiInsertBack!=null)
backSql = apiInsertBack;
resultMap.put("backSql",backSql);
resultMap.put("configEntity",apiConfigs);
resultMap.put("type","UPDATE");
resultMap.put("tableName",tableName);
return resultMap;
} else {
//获取where过滤条件
Map<String, Object> whereMap = new HashMap<>();
Map<String, Object> updateWhere = getUpdateWhere(sql);
if (updateWhere !=null){
whereMap = updateWhere;
}
CopybookFieldConfig copybookFieldConfig = new CopybookFieldConfig();
copybookFieldConfig.setAbTable(tableName.substring(tableName.length()-1));
copybookFieldConfig.setCopybook(whereMap.get("COPYBOOK") == null ? null : whereMap.get("COPYBOOK").toString());
copybookFieldConfig.setCobolName(whereMap.get("COLUMN_NAME") == null ? null : whereMap.get("COLUMN_NAME").toString());
copybookFieldConfig.setFieldNo(whereMap.get("FIELD_NO") == null ? null : Long.parseLong(whereMap.get("FIELD_NO").toString()));
//获取更新前的值
List<CopybookFieldConfig> copybookFieldConfigs = copybookFieldMapper.copybookFieldConfigQuery(copybookFieldConfig);
CopybookFieldConfig copybookFieldConfig1 = copybookFieldConfigs.get(0);
//拼接回退语句--只会更新filler字段
StringBuilder backSql = new StringBuilder("UPDATE "+ tableName +" ");
if (copybookFieldConfig1.getFiller() == null) {
backSql.append(" SET FILLER= NULL");
}else {
backSql.append(" SET FILLER= '" + copybookFieldConfig1.getFiller() + "'");
}
backSql.append(" WHERE COPYBOOK='"+ copybookFieldConfig1.getCopybook() +"' ");
backSql.append(" AND COLUMN_NAME='"+ copybookFieldConfig1.getColumnName() +"' ");
backSql.append(" AND FIELD_NO="+ copybookFieldConfig1.getFieldNo() +" ");
resultMap.put("backSql",backSql);
resultMap.put("configEntity",copybookFieldConfig1);
resultMap.put("type","UPDATE");
resultMap.put("tableName",tableName);
return resultMap;
}
}
}
private static String getApiInsertBack(ApiConfigs apiConfigs) {
StringBuilder backSql = new StringBuilder("UPDATE COXDBO.QUERY_API_CONFIG SET ");
if (apiConfigs.getStatus() != null)
backSql.append("STATUS='" + apiConfigs.getStatus() + "', ");
if (apiConfigs.getType() != null)
backSql.append("TYPE='" + apiConfigs.getType() + "', ");
if (apiConfigs.getUrl() != null)
backSql.append("URL='" + apiConfigs.getUrl() + "', ");
if (apiConfigs.getSwitchTail() != null)
backSql.append("SWITCH_TAIL='" + apiConfigs.getSwitchTail() + "', ");
if (apiConfigs.getCoxTps() != null)
backSql.append("COX_TPS=" + apiConfigs.getCoxTps() + ", ");
if (apiConfigs.getCoxTps() != null)
backSql.append("CTG_TPS=" + apiConfigs.getCtgTps() + ", ");
if (apiConfigs.getCoxTps() != null)
backSql.append("PERCENTAGE=" + apiConfigs.getPercentage() + ", ");
backSql = new StringBuilder(backSql.substring(0, backSql.length() - 2));
backSql.append(" WHERE API_ID='" + apiConfigs + "'");
return backSql.toString();
}
private static Map<String, Object> getUpdateWhere(String sql) {
if (StringUtils.isEmpty(sql))
return null;
sql = sql.replace(";", "");
String where = sql.substring(sql.indexOf("WHERE") + 5);
String[] ands = where.split("AND");
Map<String, Object> conditionMap = new HashMap<>();
for (String and : ands) {
and = and.replaceAll(" ", "");
String[] split = and.split("=");
conditionMap.put(split[0], String.valueOf(split[1]).contains("'") ? split[1].toString().replaceAll("'","") : split[1]);
}
return conditionMap;
}
/**
* 将字符串中多个连续的空格合并为一个
*
* @param str
* @return
*/
public static String mergeContinuetyBlank(String str) {
if (StringUtils.isEmpty(str))
return "";
String s = str.replaceAll("\n", " ").replaceAll(" {2,}", " ").replaceAll("\r", "").replaceAll("\t", "").replaceAll(";", "");
//若第一位为空格，则将第一位移除
if (s.charAt(0) == ' ') {
s = s.substring(1, s.length());
}
return s;
}
/**
* 获取插入操作表名
*
* @param sql
* @return
*/
public static String getInsertTableName(String sql) {
if (StringUtils.isEmpty(sql))
return "";
String[] s = sql.split(" ");
return s[2];
}
/**
* 获取更新操作表名
*
* @param sql
* @return
*/
public static String getUpdateTableName(String sql) {
if (StringUtils.isEmpty(sql))
return "";
String[] s = sql.split(" ");
return s[1];
}
/**
* 获取Api表主键值
*
* @param sql
* @return
*/
public static String getInsertColumnValue(String sql, String columnName) {
sql = sql;
String firstBraket = sql.substring(sql.indexOf('(') + 1, sql.indexOf(')'));
String firstBraketSql = firstBraket.replaceAll(" ", "");
String subSql = sql.substring(sql.indexOf(')') + 1);
String secondBraket = subSql.substring(subSql.indexOf('(') + 1, subSql.indexOf(')'));
String secondBraketSql = secondBraket.replaceAll(" ", "");
String[] nameSplit = firstBraketSql.split(",");
String[] valueSplit = secondBraketSql.split(",");
for (int i = 0; i < nameSplit.length; i++) {
if (columnName.equals(nameSplit[i]))
return valueSplit[i];
}
return "";
}
}
package com.cmbchina.monitor.service.imp;
import com.alibaba.fastjson.JSON;
import com.cmbchina.monitor.dao.es.ConfigEs;
import com.cmbchina.monitor.dao.mapper.ApiConfigMapper;
import com.cmbchina.monitor.dao.mapper.CopybookFieldMapper;
import com.cmbchina.monitor.dao.mysqlmapper.MdcMapper;
import com.cmbchina.monitor.dao.sqlfactory.ConfigSqlFactory;
import com.cmbchina.monitor.entity.ResponseData;
import com.cmbchina.monitor.entity.configs.CopybookFieldConfig;
import com.cmbchina.monitor.entity.configs.FieldConfigHistory;
import com.cmbchina.monitor.entity.configs.MdcAlarm;
import com.cmbchina.monitor.entity.configs.UpdateInfo;
import com.cmbchina.monitor.exception.QueryException;
import com.cmbchina.monitor.service.CopybookFieldService;
import com.cmbchina.monitor.utils.RestResultGenerator;
import com.cmbchina.monitor.utils.ResultCode;
import com.cmbchina.monitor.utils.SqlCheckUtil;
import lombok.extern.slf4j.Slf4j;
import org.elasticsearch.client.RestHighLevelClient;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import javax.annotation.Resource;
import java.util.*;
@Service
@Slf4j
public class CopybookFieldServiceImpl implements CopybookFieldService {
@Resource
MdcMapper mdcMapper;
@Resource
CopybookFieldMapper copybookFieldMapper;
@Resource
ApiConfigMapper apiConfigMapper;
@Resource
RestHighLevelClient webESClient;
static Set<String> ABSet = new HashSet(){{
add("A");
add("B");
}};
/**############################################操作数据库表 MDC_ALARM #####################################################**/
@Override
public ResponseData<MdcAlarm> mdcAlarmQuery(Integer currentPage, Integer pageSize, String alarmName) {
List<MdcAlarm> result = mdcMapper.mdcAlarmQuery((currentPage - 1) * pageSize, pageSize, alarmName);
Integer total = mdcMapper.mdcAlarmQueryTotal(alarmName);
return RestResultGenerator.genResult(ResultCode.SUCCESS.getCode(),ResultCode.SUCCESS.getMsg(),total,result);
}
@Override
public ResponseData<CopybookFieldConfig> copybookFieldConfigQuery(CopybookFieldConfig copybookFieldConfig) throws Exception{
//默认查询A档
checkInput(copybookFieldConfig);
List<CopybookFieldConfig> copybookFieldConfigs = copybookFieldMapper.copybookFieldConfigQuery(copybookFieldConfig);
Integer total = copybookFieldMapper.copybookFieldConfigQueryTotal(copybookFieldConfig);
return RestResultGenerator.genResult(ResultCode.SUCCESS.getCode(),ResultCode.SUCCESS.getMsg(),total,copybookFieldConfigs);
}
@Override
@Transactional(transactionManager = "oracleDataSourceTransactionManager")
public ResponseData<Boolean> batchAddCopybookField(UpdateInfo updateInfo) throws Exception{
String batchSql = updateInfo.getSql();
if (batchSql==null || "".equals(batchSql.trim()))
throw new QueryException("400","sql脚本语法错误");
String[] sqlSplit = batchSql.split("\n");
StringBuilder sql = new StringBuilder();
//移除注释语句
for (String s : sqlSplit) {
if (!("".equals(s.replaceAll(" ","")) || s.length()<2 || "--".equals(s.substring(0,2)))){
sql.append(s);
}
}
List<FieldConfigHistory> historyList = new ArrayList<>();
//逐条生成回退信息并插入历史信息
ConfigSqlFactory configSqlFactory = new ConfigSqlFactory();
for (String s : sqlSplit) {
s = s.replaceAll("\n"," ").replaceAll("\r","").replaceAll("\t","").replaceAll(";","");
if ("".equals(s) || "COMMIT".equals(s.toUpperCase()))
continue;
if (!SqlCheckUtil.checkSql(s)) {
throw new QueryException("400","sql脚本语法错误");
}
//获取回退sql相关信息
String subSql = configSqlFactory.addCopybookField(s);
Map<String, Object> backMap = SqlCheckUtil.sqlToBack(subSql, copybookFieldMapper, apiConfigMapper);
Object backSql = backMap.get("backSql");
Object type = backMap.get("type");
Object tableName = backMap.get("tableName");
Object configEntity = backMap.get("configEntity");
//保存回退历史信息
FieldConfigHistory configHistory = new FieldConfigHistory();
configHistory.setUpdateSql(subSql);
configHistory.setBackSql(backSql.toString());
configHistory.setBackJsonData(configEntity == null? null: JSON.toJSONString(configEntity));
configHistory.setRemark(updateInfo.getReason());
configHistory.setUpdater(updateInfo.getUserName());
configHistory.setHistoryId(UUID.randomUUID().toString());
configHistory.setRECORDTIME(new Date().getTime());
historyList.add(configHistory);
Thread.sleep(2);
}
//分解脚本逐条插入，异常直接回退
String[] split = sql.toString().split(";");
for (String s : split) {
s = s.replaceAll("\n"," ").replaceAll("\r","").replaceAll("\t","").replaceAll(";","");
if ("".equals(s) || "COMMIT".equals(s.toUpperCase()))
continue;
if (!SqlCheckUtil.checkSql(s)) {
throw new QueryException("400","sql脚本语法错误");
}
copybookFieldMapper.batchAddCopybookField(s);
}
//确认更新完成后保存更新历史信息
for (FieldConfigHistory configHistory : historyList) {
ConfigEs.saveFieldConfigs(webESClient,configHistory);
}
return RestResultGenerator.genResult(ResultCode.SUCCESS.getCode(),ResultCode.SUCCESS.getMsg(),true);
}
@Override
@Transactional(transactionManager = "oracleDataSourceTransactionManager")
public ResponseData<Boolean> updateCopybook(CopybookFieldConfig copybookFieldConfig) throws Exception{
if (StringUtils.isEmpty(copybookFieldConfig.getCopybook()) || copybookFieldConfig.getFieldNo() == null || StringUtils.isEmpty(copybookFieldConfig.getAbTable()))
throw new QueryException("400","跟新参数错误");
copybookFieldMapper.updateCopybook(copybookFieldConfig);
return RestResultGenerator.genResult(ResultCode.SUCCESS.getCode(),ResultCode.SUCCESS.getMsg(),true);
}
private void checkInput(CopybookFieldConfig copybookFieldConfig) throws Exception{
//默认查询A档
if (copybookFieldConfig.getAbTable()==null)
copybookFieldConfig.setAbTable("A");
//AB表参数检查
if (!ABSet.contains(copybookFieldConfig.getAbTable()))
throw new QueryException("400", "AB档参数异常");
}
@Override
public ResponseData<Integer> add(MdcAlarm mdcAlarm) {
Integer result = mdcMapper.mdcAlarmAdd(mdcAlarm);
return RestResultGenerator.genResult(ResultCode.SUCCESS.getCode(),ResultCode.SUCCESS.getMsg(),result);
}
@Override
public ResponseData<Integer> update(MdcAlarm mdcAlarm) {
Integer result = mdcMapper.mdcAlarmUpdate(mdcAlarm);
return RestResultGenerator.genResult(ResultCode.SUCCESS.getCode(),ResultCode.SUCCESS.getMsg(),result);
}
@Override
public ResponseData<Integer> delete(MdcAlarm mdcAlarm) {
Integer result = mdcMapper.mdcAlarmDelete(mdcAlarm);
return RestResultGenerator.genResult(ResultCode.SUCCESS.getCode(),ResultCode.SUCCESS.getMsg(),result);
}
}
package com.cmbchina.monitor.entity.configs;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class UpdateInfo {
private String sql;
private String userName;
private String userId;
private String reason;
}
package com.cmbchina.monitor.entity.configs;
import com.alibaba.fastjson.annotation.JSONField;
import lombok.*;
import java.util.Date;
/**
* @author FieldConfigHistory
* @date 2022/2/28
*/
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class FieldConfigHistory {
String updater = "";
String historyId = "";
String date = "";
String remark = "";
String updateSql = "";
String updateJsonData = "{}";
String backSql = "";
String backJsonData = "{}";
@JSONField(name = "RECORDTIME")
Long RECORDTIME = new Date().getTime();
}
package com.cmbchina.monitor.dao.es;
import com.alibaba.fastjson.JSON;
import com.cmbchina.monitor.entity.configs.ConfigHistory;
import com.cmbchina.monitor.entity.configs.FieldConfigHistory;
import org.elasticsearch.action.bulk.BulkRequest;
import org.elasticsearch.action.index.IndexRequest;
import org.elasticsearch.action.search.SearchRequest;
import org.elasticsearch.action.search.SearchResponse;
import org.elasticsearch.action.support.WriteRequest;
import org.elasticsearch.client.RequestOptions;
import org.elasticsearch.client.RestHighLevelClient;
import org.elasticsearch.common.xcontent.XContentType;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.index.reindex.BulkByScrollResponse;
import org.elasticsearch.index.reindex.DeleteByQueryRequest;
import org.elasticsearch.search.SearchHit;
import org.elasticsearch.search.builder.SearchSourceBuilder;
import org.elasticsearch.search.sort.SortOrder;
import org.springframework.stereotype.Component;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* @author ConfigEs
* @date 2021/12/27
*/
@Component
public class ConfigEs {
static final String MONITORTYPE = "monitor-data";
public static String CONFIG_HISTORY_INDEX = "monitor-interface-config-history";
public static String FIELD_CONFIG_HISTORY_INDEX = "monitor-interface-field-config-history";
public static boolean deleteByHistoryId(RestHighLevelClient client, String historyId) {
try {
DeleteByQueryRequest deleteByQueryRequest = new DeleteByQueryRequest(CONFIG_HISTORY_INDEX).types(MONITORTYPE).setQuery(QueryBuilders.matchQuery("historyId.keyword", historyId));
deleteByQueryRequest.setRefresh(true);
BulkByScrollResponse bulkByScrollResponse = client.deleteByQuery(deleteByQueryRequest, RequestOptions.DEFAULT);
} catch (Exception e) {
return false;
}
return true;
}
public static boolean saveConfigs(RestHighLevelClient webEsClient, ConfigHistory configHistory) {
try {
BulkRequest bulkRequest = new BulkRequest();
bulkRequest.setRefreshPolicy(WriteRequest.RefreshPolicy.IMMEDIATE);
if (configHistory == null)
return false;
configHistory.setRECORDTIME(new Date().getTime());
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
configHistory.setDate(simpleDateFormat.format(configHistory.getRECORDTIME()));
configHistory.setHistoryId(configHistory.getUpdater() + "_" + configHistory.getRECORDTIME());
String json = JSON.toJSONString(configHistory);
bulkRequest.add(new IndexRequest(CONFIG_HISTORY_INDEX, MONITORTYPE, configHistory.getUpdater()+"_"+configHistory.getRECORDTIME()).source(json, XContentType.JSON));
webEsClient.bulk(bulkRequest, RequestOptions.DEFAULT);
} catch (IOException e) {
return false;
}
return true;
}
public static List<ConfigHistory> getTenConfigs(RestHighLevelClient client) throws Exception {
SearchRequest searchRequest = new SearchRequest(CONFIG_HISTORY_INDEX);
searchRequest.types(MONITORTYPE);
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
searchSourceBuilder.size(10);
searchSourceBuilder.query(QueryBuilders.matchAllQuery()).sort("date.keyword", SortOrder.DESC);
searchRequest.source(searchSourceBuilder);
SearchResponse search = client.search(searchRequest, RequestOptions.DEFAULT);
SearchHit[] hits = search.getHits().getHits();
if (hits.length >= 1) {
List<ConfigHistory> configList = new ArrayList<>();
for (SearchHit hit : hits) {
Map<String, Object> sourceAsMap = hit.getSourceAsMap();
ConfigHistory config = JSON.parseObject(JSON.toJSONString(sourceAsMap), ConfigHistory.class);
configList.add(config);
}
return configList;
}
return new ArrayList<>();
}
public static boolean saveFieldConfigs(RestHighLevelClient webEsClient, FieldConfigHistory configHistory) {
try {
BulkRequest bulkRequest = new BulkRequest();
bulkRequest.setRefreshPolicy(WriteRequest.RefreshPolicy.IMMEDIATE);
if (configHistory == null)
return false;
configHistory.setRECORDTIME(new Date().getTime());
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
configHistory.setDate(simpleDateFormat.format(configHistory.getRECORDTIME()));
configHistory.setHistoryId(configHistory.getUpdater() + "_" + configHistory.getRECORDTIME());
String json = JSON.toJSONString(configHistory);
bulkRequest.add(new IndexRequest(FIELD_CONFIG_HISTORY_INDEX, MONITORTYPE, configHistory.getUpdater()+"_"+configHistory.getRECORDTIME()).source(json, XContentType.JSON));
webEsClient.bulk(bulkRequest, RequestOptions.DEFAULT);
} catch (IOException e) {
return false;
}
return true;
}
}
最轻松的学习方式莫过于在学习中找到属于自己的乐趣,与诸君共勉
浙公网安备 33010602011771号