package org.jeecg.modules.iot.depository;
|
|
import com.baomidou.dynamic.datasource.annotation.DS;
|
import org.springframework.stereotype.Service;
|
|
import java.sql.*;
|
import java.util.List;
|
|
/**
|
* @author cuikaidong
|
* @date 2025/5/6
|
*/
|
@Service
|
public class MysqlDataWriter {
|
// 创建MysqlTable表
|
public static void createMysqlTable(String tableName, String... columns) {
|
if (DbConfig.DATABASE.equals("sqlserver")) {
|
// 调用mysql创建表方法
|
createSqlserverTable(tableName, columns);
|
} else if (DbConfig.DATABASE.equals("mysql")) {
|
String sql = String.format("CREATE TABLE IF NOT EXISTS %s (%s)", tableName, String.join(", ", columns));
|
|
try (Connection conn = DriverManager.getConnection(DbConfig.URL, DbConfig.USER, DbConfig.PASSWORD);
|
Statement stmt = conn.createStatement()) {
|
stmt.executeUpdate(sql);
|
System.out.println("表创建成功: " + tableName);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
|
}
|
|
// 写入数据
|
public static void insertMysqlData(String tableName, String[] columns, Object[] values) {
|
if (columns.length != values.length) {
|
throw new IllegalArgumentException("字段与值的数量不匹配");
|
}
|
String placeholders = String.join(", ", java.util.Collections.nCopies(columns.length, "?"));
|
String columnNames = String.join(", ", columns);
|
String sql = String.format("INSERT INTO %s (%s) VALUES (%s)", tableName, columnNames, placeholders);
|
|
try (Connection conn = DriverManager.getConnection(DbConfig.URL, DbConfig.USER, DbConfig.PASSWORD);
|
PreparedStatement pstmt = conn.prepareStatement(sql)) {
|
|
for (int i = 0; i < values.length; i++) {
|
pstmt.setObject(i + 1, values[i]);
|
}
|
pstmt.executeUpdate();
|
// System.out.println("数据写入成功");
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
|
// 增加字段 在 类中添加以下方法
|
public static void addMysqlColumn(String tableName, String columnDefinition, String columnName) {
|
if (isColumnExists(tableName, columnName)) {
|
System.out.println("字段已存在，无需添加: " + columnName);
|
return;
|
}
|
String sql = String.format("ALTER TABLE %s ADD COLUMN %s", tableName, columnDefinition);
|
try (Connection conn = DriverManager.getConnection(DbConfig.URL, DbConfig.USER, DbConfig.PASSWORD);
|
Statement stmt = conn.createStatement()) {
|
stmt.executeUpdate(sql);
|
System.out.println("字段添加成功: " + columnDefinition);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
|
// 辅助方法：检查表中是否已存在指定字段
|
private static boolean isColumnExists(String tableName, String columnName) {
|
String sql = String.format(
|
"SELECT COUNT(*) FROM information_schema.columns " +
|
"WHERE table_name = '%s' AND column_name = '%s'",
|
tableName, columnName
|
);
|
|
try (Connection conn = DriverManager.getConnection(DbConfig.URL, DbConfig.USER, DbConfig.PASSWORD);
|
Statement stmt = conn.createStatement();
|
ResultSet rs = stmt.executeQuery(sql)) {
|
if (rs.next()) {
|
return rs.getInt(1) > 0;
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return false;
|
}
|
|
// 批量导入字段
|
public static void addMysqlColumns(String tableName, List<String> columnDefinitions, List<String> columnNames) {
|
if (columnDefinitions.size() != columnNames.size()) {
|
throw new IllegalArgumentException("字段定义和字段名的数量必须一致");
|
}
|
|
StringBuilder sqlBuilder = new StringBuilder("ALTER TABLE ").append(tableName).append(" ADD COLUMN ( ");
|
for (int i = 0; i < columnDefinitions.size(); i++) {
|
String columnName = columnNames.get(i);
|
if (batchIsColumnExists(tableName, columnName)) {
|
System.out.println("字段已存在，无需添加: " + columnName);
|
continue;
|
}
|
String columnDefinition = columnDefinitions.get(i);
|
if (i > 0) {
|
sqlBuilder.append(", ");
|
}
|
sqlBuilder.append(columnDefinition);
|
}
|
sqlBuilder.append(" ) ");
|
String sql = sqlBuilder.toString();
|
if (sql.endsWith(" ADD COLUMN ( )")) {
|
System.out.println("没有需要添加的新字段");
|
return;
|
}
|
try (Connection conn = DriverManager.getConnection(DbConfig.URL, DbConfig.USER, DbConfig.PASSWORD);
|
Statement stmt = conn.createStatement()) {
|
stmt.executeUpdate(sql);
|
System.out.println("字段批量添加成功");
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
|
// 验证字段是否重复
|
private static boolean batchIsColumnExists(String tableName, String columnName) {
|
String sql = String.format(
|
"SELECT COUNT(*) FROM information_schema.columns " +
|
"WHERE TABLE_SCHEMA = 'lx_iot' AND table_name = '%s' AND column_name = '%s'",
|
tableName, columnName
|
);
|
|
try (Connection conn = DriverManager.getConnection(DbConfig.URL, DbConfig.USER, DbConfig.PASSWORD);
|
Statement stmt = conn.createStatement();
|
ResultSet rs = stmt.executeQuery(sql)) {
|
if (rs.next()) {
|
return rs.getInt(1) > 0;
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return false;
|
}
|
|
|
/************************************************************ sqlserver版本 ***************************************/
|
// 创建sqlserver表
|
public static void createSqlserverTable(String tableName, String... columns) {
|
String schemaName = "dbo";
|
// 创建表的 SQL（如果不存在）
|
String tableSql = String.format(
|
"IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = '%s' AND schema_id = SCHEMA_ID('%s')) " +
|
"CREATE TABLE [%s].[%s] (%s)",
|
tableName, schemaName, schemaName, tableName, String.join(", ", columns)
|
);
|
// String sql = String.format("CREATE TABLE IF NOT EXISTS %s (%s)", tableName, String.join(", ", columns));
|
try (Connection conn = DriverManager.getConnection(DbConfig.URL, DbConfig.USER, DbConfig.PASSWORD);
|
Statement stmt = conn.createStatement()) {
|
stmt.executeUpdate(tableSql);
|
System.out.println("表创建成功: " + tableName);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
|
// 批量导入字段（SQL Server 版本）
|
public static void addSqlserverColumns(String schemaName, 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(schemaName).append("].[")
|
.append(tableName).append("] ADD ");
|
|
boolean hasNewColumns = false;
|
for (int i = 0; i < columnDefinitions.size(); i++) {
|
String columnName = columnNames.get(i);
|
if (sqlServeBatchIsColumnExists(schemaName, 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 = DriverManager.getConnection(DbConfig.URL, DbConfig.USER, DbConfig.PASSWORD);
|
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 static boolean sqlServeBatchIsColumnExists(String schemaName, String tableName, String columnName) {
|
String sql = "SELECT COUNT(*) FROM sys.columns " +
|
"WHERE object_id = OBJECT_ID('[" + schemaName + "].[" + tableName + "]') " +
|
"AND name = @columnName";
|
|
try (Connection conn = DriverManager.getConnection(DbConfig.URL, DbConfig.USER, DbConfig.PASSWORD);
|
PreparedStatement pstmt = conn.prepareStatement(sql)) {
|
pstmt.setString(1, columnName);
|
|
try (ResultSet rs = pstmt.executeQuery()) {
|
if (rs.next()) {
|
return rs.getInt(1) > 0;
|
}
|
}
|
} catch (SQLException e) {
|
System.err.println("检查字段存在性失败: " + sql);
|
e.printStackTrace();
|
}
|
return false;
|
}
|
}
|
