package org.jeecg.modules.iot.service.impl;
|
|
import com.alibaba.fastjson.JSONObject;
|
import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper;
|
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
|
import org.apache.commons.collections.CollectionUtils;
|
import org.apache.http.HttpResponse;
|
import org.apache.http.client.HttpClient;
|
import org.apache.http.client.methods.HttpPost;
|
import org.apache.http.entity.StringEntity;
|
import org.apache.http.impl.client.HttpClients;
|
import org.apache.http.util.EntityUtils;
|
import org.jeecg.common.api.vo.Result;
|
import org.jeecg.modules.iot.depository.DbConfig;
|
import org.jeecg.modules.iot.depository.MysqlDataWriter;
|
import org.jeecg.modules.iot.entity.*;
|
import org.jeecg.modules.iot.mapper.EmptyParameterMapper;
|
import org.jeecg.modules.iot.mapper.RealParameterMapper;
|
import org.jeecg.modules.iot.mdc.entity.MdcDriveTypeParamConfig;
|
import org.jeecg.modules.iot.mdc.service.IMdcDriveTypeParamConfigService;
|
import org.jeecg.modules.iot.service.IEmptyParameterService;
|
import org.jeecg.modules.iot.service.IEquipmentService;
|
import org.jeecg.modules.iot.service.IServerDeployService;
|
import org.jeecg.modules.iot.util.AESUtil;
|
import org.jeecg.modules.iot.util.HttpClientUtil;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.beans.factory.annotation.Value;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
|
import javax.annotation.Resource;
|
import javax.sql.DataSource;
|
import java.sql.*;
|
import java.util.*;
|
import java.util.Date;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
import java.util.stream.Collectors;
|
|
/**
|
* @Description: 虚设备参数
|
* @Author: cuikaidong
|
* @Date: 2025-1-7
|
* @Version: V1.0
|
*/
|
@Service
|
public class EmptyParameterServiceImpl extends ServiceImpl<EmptyParameterMapper, EmptyParameter> implements IEmptyParameterService {
|
@Value("${databaseType}")
|
private String databaseType;
|
@Value("${ftp.address}")
|
private String ftpAddress;
|
@Resource
|
private RealParameterMapper realParameterMapper;
|
@Autowired
|
private IEquipmentService equipmentService;
|
@Autowired
|
private IServerDeployService serverDeployService;
|
@Autowired
|
private DataSource dataSource;
|
@Autowired
|
private IMdcDriveTypeParamConfigService driveTypeParamConfigService;
|
|
@Override
|
@Transactional(rollbackFor = Exception.class)
|
public Result<?> addEmptyParameter(EmptyParameter emptyParameter) {
|
// 验证编码名称是否重复
|
if (findEmptyParameterByName(emptyParameter)) {
|
return Result.error("参数名称已存在！");
|
}
|
emptyParameter.setParameterCode(findEmptyParameterCode(emptyParameter.getEquipmentId()));
|
emptyParameter.setDataLength("1");
|
baseMapper.insert(emptyParameter);
|
|
// 查询设备信息
|
Equipment equipment = equipmentService.getById(emptyParameter.getEquipmentId());
|
if (equipment == null) {
|
return Result.error("设备信息不存在！");
|
}
|
// 验证mdc是否存在驱动参数
|
boolean englishNameBoolean = driveTypeParamConfigService.selectEnglishNameByEquipment(equipment.getControlSystem(), emptyParameter.getParameterName());
|
if (!englishNameBoolean) {
|
// 给mdc写入驱动参数
|
MdcDriveTypeParamConfig mdcDriveTypeParamConfig = new MdcDriveTypeParamConfig();
|
mdcDriveTypeParamConfig.setControlSystemType(equipment.getControlSystem());
|
mdcDriveTypeParamConfig.setEnglishName(emptyParameter.getParameterName());
|
mdcDriveTypeParamConfig.setChineseName(emptyParameter.getParameterDescribe());
|
mdcDriveTypeParamConfig.setShowFlag("Y");
|
mdcDriveTypeParamConfig.setCurveGenerationFlags("N");
|
driveTypeParamConfigService.save(mdcDriveTypeParamConfig);
|
}
|
return Result.ok("添加成功！");
|
}
|
|
@Override
|
@Transactional(rollbackFor = Exception.class)
|
public Result<?> addBatchEmptyParameter(List<EmptyParameter> emptyParameters) {
|
Integer parameterCode = findEmptyParameterCode(emptyParameters.get(0).getEquipmentId());
|
final int[] code = {parameterCode - 1};
|
for (EmptyParameter e : emptyParameters) {
|
code[0] = code[0] + 1;
|
e.setParameterCode(code[0]);
|
// 验证编码名称是否重复
|
if (findEmptyParameterByName(e)) {
|
return Result.error(e.getParameterName() + "参数名称已存在！");
|
}
|
}
|
this.saveBatch(emptyParameters);
|
// 查询设备信息
|
Equipment equipment = equipmentService.getById(emptyParameters.get(0).getEquipmentId());
|
if (equipment == null) {
|
return Result.error("设备信息不存在！");
|
}
|
List<MdcDriveTypeParamConfig> driveTypeParamConfigs = new ArrayList<>();
|
for (EmptyParameter emptyParameter : emptyParameters) {
|
// 验证mdc是否存在驱动参数
|
boolean englishNameBoolean = driveTypeParamConfigService.selectEnglishNameByEquipment(equipment.getControlSystem(), emptyParameter.getParameterName());
|
if (!englishNameBoolean) {
|
// 给mdc写入驱动参数
|
MdcDriveTypeParamConfig mdcDriveTypeParamConfig = new MdcDriveTypeParamConfig();
|
mdcDriveTypeParamConfig.setControlSystemType(equipment.getControlSystem());
|
mdcDriveTypeParamConfig.setEnglishName(emptyParameter.getParameterName());
|
mdcDriveTypeParamConfig.setChineseName(emptyParameter.getParameterDescribe());
|
mdcDriveTypeParamConfig.setShowFlag("Y");
|
mdcDriveTypeParamConfig.setCurveGenerationFlags("N");
|
driveTypeParamConfigs.add(mdcDriveTypeParamConfig);
|
}
|
}
|
if (driveTypeParamConfigs.size() > 0) {
|
driveTypeParamConfigService.saveBatch(driveTypeParamConfigs);
|
}
|
return Result.ok("批量添加成功！");
|
}
|
|
@Override
|
public void editEmptyParameter(EmptyParameter emptyParameter) {
|
// 查询设备信息
|
Equipment equipment = equipmentService.getById(emptyParameter.getEquipmentId());
|
emptyParameter.setCreateTime(new Date());
|
EmptyParameter emptyParameter1 = baseMapper.selectById(emptyParameter.getId());
|
baseMapper.updateById(emptyParameter);
|
if (!emptyParameter1.getParameterName().equals(emptyParameter.getParameterName())) {
|
MdcDriveTypeParamConfig mdcDriveTypeParamConfig = driveTypeParamConfigService.selectMdcDriveTypeParamConfigByEquipment(equipment.getControlSystem(), emptyParameter1.getParameterName());
|
mdcDriveTypeParamConfig.setEnglishName(emptyParameter.getParameterName());
|
driveTypeParamConfigService.updateById(mdcDriveTypeParamConfig);
|
}
|
}
|
|
@Override
|
public void deleteEmptyParameter(String id) {
|
EmptyParameter emptyParameter = baseMapper.selectById(id);
|
if (emptyParameter.getType().equals("0")) {
|
//删除虚设备时调用`
|
Map<String, String> param = new HashMap<>();
|
Equipment equipment = equipmentService.getById(emptyParameter.getEquipmentId());
|
ServerDeploy serverDeploy = serverDeployService.getById(equipment.getServerId());
|
param.put("path", ftpAddress + serverDeploy.getServerCode() + "/script");
|
HttpClientUtil.doGet("http://localhost:3002/ScriptCompiler/ProjectPath", param);
|
param.clear();
|
param.put("deviceid", emptyParameter.getEquipmentId());
|
|
param.put("id", equipment.getEqptName());
|
HttpClientUtil.doGet("http://localhost:3002/ScriptCompiler/DeleteParameterScript", param);
|
}
|
baseMapper.deleteById(id);
|
// 查询设备信息
|
Equipment equipment = equipmentService.getById(emptyParameter.getEquipmentId());
|
// 删除mdc设备参数信息
|
MdcDriveTypeParamConfig mdcDriveTypeParamConfig = driveTypeParamConfigService.selectMdcDriveTypeParamConfigByEquipment(equipment.getControlSystem(), emptyParameter.getParameterName());
|
if (mdcDriveTypeParamConfig != null) {
|
driveTypeParamConfigService.removeById(mdcDriveTypeParamConfig);
|
}
|
}
|
|
@Override
|
public List<EmptyParameter> findParameterByEquipmentIds(Set<String> ids) {
|
return new LambdaQueryChainWrapper<>(baseMapper).in(EmptyParameter::getEquipmentId, ids).list();
|
}
|
|
@Override
|
public Result<?> handleCompile(EmptyParameter emptyParameter) {
|
Map<String, String> referenceInfo = new HashMap<>();
|
// 验证脚本实设备
|
List<String> attributes = new ArrayList<>(getAttributes(emptyParameter.getScript(), "IOT_[\\p{L}\\p{N}_]+_[\\p{L}\\p{N}_]+_[\\p{L}\\p{N}_]+"));
|
if (!attributes.isEmpty()) {
|
List<RealParameter> realParameterList = realParameterMapper.findRealParameterList(attributes);
|
// 判断实设备参数数量是否相等
|
if (attributes.size() != realParameterList.size()) {
|
// 求差集
|
Set<String> difference1 = new HashSet<>(attributes);
|
// 使用 Stream API 将 name 字段转换为 Set<String>
|
Set<String> difference2 = realParameterList.stream()
|
.map(RealParameter::getAddress).collect(Collectors.toSet());
|
difference1.removeAll(difference2);
|
return Result.error(difference1 + "属性有误！！！");
|
}
|
realParameterList.forEach(r -> {
|
if (r.getSystemDataType().isEmpty()) {
|
referenceInfo.put(r.getAddress(), r.getParameterType());
|
} else {
|
referenceInfo.put(r.getAddress(), r.getSystemDataType());
|
}
|
});
|
}
|
// 验证虚设备
|
List<String> empAttributes = new ArrayList<>(getAttributes(emptyParameter.getScript(), "IOT_[\\p{L}\\p{N}_]+_[\\p{L}\\p{N}_]+"));
|
if (!empAttributes.isEmpty()) {
|
List<EmptyParameter> emptyParameterList = new ArrayList<>();
|
Set<String> difference = new HashSet<>(empAttributes);
|
if (!attributes.isEmpty()) {
|
// 求差集
|
difference.removeAll(attributes);
|
List<String> www = new ArrayList<>(difference);
|
// 查询数据库中匹配的数量
|
if (!www.isEmpty()) {
|
emptyParameterList = baseMapper.findEmptyParameterList(www);
|
}
|
} else {
|
emptyParameterList = baseMapper.findEmptyParameterList(empAttributes);
|
}
|
// 使用 HashSet 去重
|
Set<EmptyParameter> personSet = new HashSet<>(emptyParameterList);
|
// 判断虚设备参数数量是否相等
|
if (difference.size() != personSet.size()) {
|
// 求差集
|
Set<String> difference1 = new HashSet<>(difference);
|
// 使用 Stream API 将 name 字段转换为 Set<String>
|
Set<String> difference2 = personSet.stream()
|
.map(EmptyParameter::getAddress).collect(Collectors.toSet());
|
difference1.removeAll(difference2);
|
return Result.error(difference1 + "属性有误！！！");
|
}
|
personSet.forEach(p -> {
|
if (p.getSystemDataType().isEmpty()) {
|
referenceInfo.put(p.getAddress(), p.getParameterType());
|
} else {
|
referenceInfo.put(p.getAddress(), p.getSystemDataType());
|
}
|
});
|
}
|
// --------AES_CBC加密---------
|
String cbcResult = AESUtil.encryptCBC(emptyParameter.getScript(), "xhVs6DRXLfUGxw+AhtfQdpQGoa+8SA9d");
|
try {
|
ScriptCompilation scriptCompilation = new ScriptCompilation();
|
Equipment equipment = equipmentService.getById(emptyParameter.getEquipmentId());
|
scriptCompilation.setDeviceId(equipment.getEqptCode());
|
scriptCompilation.setParameterCode(cbcResult);
|
scriptCompilation.setParameterDataType(emptyParameter.getParameterType());
|
scriptCompilation.setParameterName(emptyParameter.getParameterName());
|
scriptCompilation.setReferenceinfo(referenceInfo);
|
String jsonPayload = JSONObject.toJSONString(scriptCompilation);
|
|
// 创建 HttpClient 实例
|
HttpClient httpClient = HttpClients.createDefault();
|
// 创建 HttpPost 请求实例
|
HttpPost httpPost = new HttpPost("http://localhost:3002/ScriptCompiler/ScriptCompiler");
|
// 设置请求头，指定内容类型为 JSON，编码为 UTF-8
|
httpPost.setHeader("Content-Type", "application/json; charset=UTF-8");
|
|
// 创建 StringEntity 并指定编码为 UTF-8
|
StringEntity entity = new StringEntity(jsonPayload, "UTF-8");
|
httpPost.setEntity(entity);
|
|
// 执行请求并获取响应
|
HttpResponse response = httpClient.execute(httpPost);
|
// 获取响应实体内容
|
String responseBody = EntityUtils.toString(response.getEntity(), "UTF-8");
|
boolean isContain = responseBody.contains("编译成功");
|
if (isContain) {
|
return Result.ok(responseBody);
|
} else {
|
// 输出响应内容
|
return Result.error(responseBody);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return Result.error("编译失败");
|
}
|
|
@Override
|
public Result<?> preserveScript(EmptyParameter emptyParameter) {
|
Map<String, String> referenceInfo = new HashMap<>();
|
// 验证脚本实设备
|
List<String> attributes = new ArrayList<>(getAttributes(emptyParameter.getScript(), "IOT_[\\p{L}\\p{N}_]+_[\\p{L}\\p{N}_]+_[\\p{L}\\p{N}_]+"));
|
if (!attributes.isEmpty()) {
|
List<RealParameter> realParameterList = realParameterMapper.findRealParameterList(attributes);
|
realParameterList.forEach(r -> {
|
if (r.getSystemDataType().isEmpty()) {
|
referenceInfo.put(r.getAddress(), r.getParameterType());
|
} else {
|
referenceInfo.put(r.getAddress(), r.getSystemDataType());
|
}
|
});
|
}
|
// 验证虚设备
|
List<String> empAttributes = new ArrayList<>(getAttributes(emptyParameter.getScript(), "IOT_[\\p{L}\\p{N}_]+_[\\p{L}\\p{N}_]+"));
|
if (!empAttributes.isEmpty()) {
|
List<EmptyParameter> emptyParameterList = new ArrayList<>();
|
Set<String> difference = new HashSet<>(empAttributes);
|
if (!attributes.isEmpty()) {
|
// 求差集
|
difference.removeAll(attributes);
|
List<String> www = new ArrayList<>(difference);
|
// 查询数据库中匹配的数量
|
if (!www.isEmpty()) {
|
emptyParameterList = baseMapper.findEmptyParameterList(www);
|
}
|
} else {
|
emptyParameterList = baseMapper.findEmptyParameterList(empAttributes);
|
}
|
// 使用 HashSet 去重
|
Set<EmptyParameter> personSet = new HashSet<>(emptyParameterList);
|
personSet.forEach(p -> {
|
if (p.getSystemDataType().isEmpty()) {
|
referenceInfo.put(p.getAddress(), p.getParameterType());
|
} else {
|
referenceInfo.put(p.getAddress(), p.getSystemDataType());
|
}
|
});
|
}
|
// --------AES_CBC加密---------
|
String cbcResult = AESUtil.encryptCBC(emptyParameter.getScript(), "xhVs6DRXLfUGxw+AhtfQdpQGoa+8SA9d");
|
try {
|
ScriptCompilation scriptCompilation = new ScriptCompilation();
|
Equipment equipment = equipmentService.getById(emptyParameter.getEquipmentId());
|
scriptCompilation.setDeviceId(equipment.getEqptCode());
|
scriptCompilation.setParameterCode(cbcResult);
|
scriptCompilation.setParameterDataType(emptyParameter.getParameterType());
|
scriptCompilation.setParameterName(emptyParameter.getParameterName());
|
scriptCompilation.setReferenceinfo(referenceInfo);
|
String jsonPayload = JSONObject.toJSONString(scriptCompilation);
|
|
// 创建 HttpClient 实例
|
HttpClient httpClient = HttpClients.createDefault();
|
Map<String, String> param = new HashMap<>();
|
// 发送文件地址
|
ServerDeploy serverDeploy = serverDeployService.getById(equipment.getServerId());
|
param.put("path", ftpAddress + serverDeploy.getServerCode() + "/script");
|
HttpClientUtil.doGet("http://localhost:3002/ScriptCompiler/ProjectPath", param);
|
// 创建 HttpPost 请求实例
|
HttpPost httpPost = new HttpPost("http://localhost:3002/ScriptCompiler/ScriptSave");
|
// 设置请求头，指定内容类型为 JSON，编码为 UTF-8
|
httpPost.setHeader("Content-Type", "application/json; charset=UTF-8");
|
|
// 创建 StringEntity 并指定编码为 UTF-8
|
StringEntity entity = new StringEntity(jsonPayload, "UTF-8");
|
httpPost.setEntity(entity);
|
|
// 执行请求并获取响应
|
HttpResponse response = httpClient.execute(httpPost);
|
// 获取响应实体内容
|
String responseBody = EntityUtils.toString(response.getEntity(), "UTF-8");
|
boolean isContain = responseBody.contains("保存成功");
|
// 将 JSON 字符串转换为 JSONObject
|
cn.hutool.json.JSONObject jsonObject = new cn.hutool.json.JSONObject(responseBody);
|
// 访问 JSON 对象的属性
|
String message = jsonObject.getStr("Message");
|
if (isContain) {
|
// 保存脚本标记列表
|
StringBuilder scriptTagList = new StringBuilder();
|
for (String key : referenceInfo.keySet()) {
|
if (scriptTagList.toString().equals("")) {
|
scriptTagList = new StringBuilder(key);
|
} else {
|
scriptTagList.append("|").append(key);
|
}
|
}
|
// 保存属性
|
emptyParameter.setScriptTagList(scriptTagList.toString());
|
// 保存脚本
|
emptyParameter.setScriptContent(emptyParameter.getScript());
|
baseMapper.updateById(emptyParameter);
|
return Result.ok(message);
|
} else {
|
// 输出响应内容
|
return Result.error(message);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return Result.error("编译失败");
|
}
|
|
@Override
|
public Integer findEmptyParameterCode(String equipmentId) {
|
List<EmptyParameter> list = new LambdaQueryChainWrapper<>(baseMapper)
|
.eq(EmptyParameter::getEquipmentId, equipmentId)
|
.orderByDesc(EmptyParameter::getParameterCode)
|
.list();
|
if (list.size() == 0) {
|
return 1;
|
}
|
return list.get(0).getParameterCode() + 1;
|
}
|
|
@Override
|
public Boolean findEmptyParameterByName(EmptyParameter emptyParameter) {
|
List<EmptyParameter> realParameters = new LambdaQueryChainWrapper<>(baseMapper)
|
.eq(EmptyParameter::getEquipmentId, emptyParameter.getEquipmentId())
|
.eq(EmptyParameter::getParameterName, emptyParameter.getParameterName())
|
.list();
|
return realParameters.size() > 0;
|
}
|
|
@Override
|
public void createEmptyEmptyField(Date deployDate, String serverId) {
|
List<EmptyParameter> emptyParameterList = baseMapper.findEmptyParameterListByTime(deployDate, serverId);
|
Map<String, List<EmptyParameter>> emptyParameterListMap = emptyParameterList.stream()
|
.collect(Collectors.groupingBy(EmptyParameter::getEqptCode));
|
emptyParameterListMap.forEach((eqptCode, parameterList) -> {
|
// 将所有的 '-' 替换为 '_'
|
String code = eqptCode.replace("-", "_");
|
Equipment equipment = equipmentService.findEquipmentByCode(eqptCode);
|
List<String> columnDefinitions = new ArrayList<>();
|
Set<String> columnNames = new HashSet<>();
|
// 遍历当前设备下的所有参数
|
parameterList.forEach(param -> {
|
columnNames.add(param.getParameterName());
|
if (databaseType.equals("SqlServer")) {
|
columnDefinitions.add("NVARCHAR(100) DEFAULT ''");
|
} else if (databaseType.equals("MySql")) {
|
columnDefinitions.add("VARCHAR(100) CHARACTER SET utf8mb4");
|
}
|
});
|
if (CollectionUtils.isEmpty(columnNames)){
|
return;
|
}
|
if (databaseType.equals("SqlServer")) {
|
addSqlserverColumns(equipment.getControlSystem() + '_' + code, new ArrayList<>(columnDefinitions),
|
new ArrayList<>(columnNames));
|
} else if (databaseType.equals("MySql")) {
|
addMySqlColumns(equipment.getControlSystem() + '_' + code, new ArrayList<>(columnDefinitions),
|
new ArrayList<>(columnNames));
|
}
|
});
|
}
|
|
/**
|
* // 批量导入字段（SQL Server 版本）
|
*
|
* @param tableName
|
* @param columnDefinitions
|
* @param columnNames
|
*/
|
public void addSqlserverColumns(String tableName, List<String> columnDefinitions, List<String> columnNames) {
|
if (columnDefinitions.size() != columnNames.size()) {
|
throw new IllegalArgumentException("字段定义和字段名的数量必须一致");
|
}
|
|
// 转义表名中的方括号，防止SQL注入
|
tableName = tableName.replace("[", "[[").replace("]", "]]");
|
|
StringBuilder sqlBuilder = new StringBuilder();
|
sqlBuilder.append("ALTER TABLE").append("[")
|
.append(tableName).append("] ADD ");
|
|
boolean hasNewColumns = false;
|
for (int i = 0; i < columnDefinitions.size(); i++) {
|
String columnName = columnNames.get(i);
|
if (sqlServeBatchIsColumnExists(tableName, columnName)) {
|
System.out.println("字段已存在，无需添加: " + columnName);
|
continue;
|
}
|
|
String columnDefinition = columnDefinitions.get(i);
|
if (hasNewColumns) {
|
sqlBuilder.append(", ");
|
}
|
sqlBuilder.append("[").append(columnName.replace("[", "[[").replace("]", "]]")).append("] ")
|
.append(columnDefinition);
|
hasNewColumns = true;
|
}
|
|
String sql = sqlBuilder.toString();
|
if (!hasNewColumns) {
|
System.out.println("没有需要添加的新字段");
|
return;
|
}
|
|
try (Connection conn = dataSource.getConnection();
|
Statement stmt = conn.createStatement()) {
|
System.out.println("执行SQL: " + sql); // 调试用
|
stmt.executeUpdate(sql);
|
System.out.println("字段批量添加成功");
|
} catch (SQLException e) {
|
System.err.println("批量添加字段失败: " + sql);
|
e.printStackTrace();
|
}
|
}
|
|
/**
|
* 验证字段是否存在（SQL Server 版本）
|
*/
|
private boolean sqlServeBatchIsColumnExists(String tableName, String columnName) {
|
// 自动转义表名（处理方括号和特殊字符）
|
String quotedTable = "[" + tableName.replace("]", "]]") + "]";
|
|
String sql = "SELECT COUNT(*) FROM sys.columns " +
|
"WHERE object_id = OBJECT_ID(?) AND name = ?";
|
|
try (Connection conn = dataSource.getConnection();
|
PreparedStatement stmt = conn.prepareStatement(sql)) {
|
// 设置第一个参数：转义后的表名
|
stmt.setString(1, quotedTable);
|
// 设置第二个参数：字段名
|
stmt.setString(2, columnName);
|
|
try (ResultSet rs = stmt.executeQuery()) {
|
if (rs.next()) {
|
return rs.getInt(1) > 0;
|
}
|
}
|
} catch (SQLException e) {
|
// log.error("检查字段 {} 在表 {} 中是否存在时出错", columnName, tableName, e);
|
return false;
|
}
|
return false;
|
}
|
|
/**
|
* 批量导入字段（MySQL 版本）
|
*
|
* @param tableName 表名
|
* @param columnDefinitions 列定义列表，格式如 "VARCHAR(255) NOT NULL"
|
* @param columnNames 列名列表
|
*/
|
public void addMySqlColumns(String tableName, List<String> columnDefinitions, List<String> columnNames) {
|
if (columnDefinitions.size() != columnNames.size()) {
|
throw new IllegalArgumentException("字段定义和字段名的数量必须一致");
|
}
|
|
tableName = sanitizeSqlIdentifier(tableName);
|
|
// 对每个列单独执行 ALTER TABLE 语句
|
for (int i = 0; i < columnNames.size(); i++) {
|
String columnName = sanitizeSqlIdentifier(columnNames.get(i));
|
if (columnName.isEmpty()) {
|
System.out.println("忽略无效字段名: " + columnNames.get(i));
|
continue;
|
}
|
|
if (mySqlBatchIsColumnExists(tableName, columnName)) {
|
System.out.println("字段已存在，无需添加: " + columnName);
|
continue;
|
}
|
|
String columnDefinition = columnDefinitions.get(i);
|
// 确保格式正确
|
columnDefinition = formatMySqlColumnDefinition(columnDefinition);
|
|
String sql = String.format(
|
"ALTER TABLE `%s` ADD `%s` %s",
|
tableName, columnName, columnDefinition
|
);
|
|
try (Connection conn = dataSource.getConnection();
|
Statement stmt = conn.createStatement()) {
|
System.out.println("执行SQL: " + sql);
|
stmt.executeUpdate(sql);
|
System.out.println("字段添加成功: " + columnName);
|
} catch (SQLException e) {
|
System.err.println("添加字段失败: " + sql);
|
e.printStackTrace();
|
}
|
}
|
}
|
|
// 确保 MySQL 列定义格式正确
|
private String formatMySqlColumnDefinition(String definition) {
|
// 修正 VARCHAR 格式
|
definition = definition.replaceAll("VARCHAR\\s*\\(\\s*(\\d+)\\s*\\)", "VARCHAR($1)");
|
|
// 确保 CHARACTER SET 紧跟数据类型
|
definition = definition.replaceAll("(VARCHAR\\(\\d+\\))\\s+(CHARACTER SET)", "$1 $2");
|
|
// 移除多余空格
|
definition = definition.replaceAll("\\s+", " ");
|
|
return definition;
|
}
|
|
// 清理 SQL 标识符
|
private String sanitizeSqlIdentifier(String identifier) {
|
if (identifier == null || identifier.isEmpty()) {
|
return "_invalid_";
|
}
|
return identifier.replaceAll("[^a-zA-Z0-9_]", "_");
|
}
|
|
/**
|
* 检查MySQL表中是否存在指定列
|
*
|
* @param tableName 表名
|
* @param columnName 列名
|
* @return true if column exists
|
*/
|
private boolean mySqlBatchIsColumnExists(String tableName, String columnName) {
|
String escapedTableName = tableName.replace("`", "``");
|
String escapedColumnName = columnName.replace("`", "``");
|
String sql = "SELECT COUNT(*) FROM information_schema.columns " +
|
"WHERE table_name = ? AND column_name = ? " +
|
"AND table_schema = DATABASE()";
|
|
try (Connection conn = dataSource.getConnection();
|
PreparedStatement pstmt = conn.prepareStatement(sql)) {
|
pstmt.setString(1, escapedTableName);
|
pstmt.setString(2, escapedColumnName);
|
try (ResultSet rs = pstmt.executeQuery()) {
|
if (rs.next()) {
|
return rs.getInt(1) > 0;
|
}
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return false;
|
}
|
|
/**
|
* 根据表达式查询脚本中包含的属性
|
*
|
* @param script 脚本
|
* @param regex 表达式
|
* @return 属性
|
*/
|
Set<String> getAttributes(String script, String regex) {
|
Set<String> scriptList = new HashSet<>();
|
// 编译正则表达式
|
Pattern pattern = Pattern.compile(regex);
|
// 创建 Matcher 对象
|
Matcher matcher = pattern.matcher(script);
|
// 查找所有匹配的子字符串
|
while (matcher.find()) {
|
// 获取当前匹配到的内容
|
scriptList.add(matcher.group());
|
}
|
return scriptList;
|
}
|
}
|
