public static void main(String[] args) {
final String SOURCE_TABLE = "表1";
final String TARGET_TABLE = "表2";
final String UNIQUE_COLUMN = "id"; // 使用 id 作为唯一标识符列
try {
// 建立数据库连接
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/库名", "数据库用户名", "数据库密码");
DatabaseMetaData metaData = connection.getMetaData();
// 获取源表的列信息
ResultSet sourceColumns = metaData.getColumns(null, null, SOURCE_TABLE, null);
StringBuilder insertColumns = new StringBuilder();
StringBuilder values = new StringBuilder();
StringBuilder checkConditions = new StringBuilder();
boolean hasUniqueColumn = false; // 标记是否存在唯一标识列
while (sourceColumns.next()) {
String columnName = sourceColumns.getString("COLUMN_NAME");
insertColumns.append(columnName).append(",");
values.append("?,");
// 生成唯一列的检查条件
if (columnName.equals(UNIQUE_COLUMN)) {
checkConditions.append(columnName).append(" = ? AND ");
hasUniqueColumn = true; // 存在唯一列
}
}
// 移除多余的逗号和 AND
insertColumns.deleteCharAt(insertColumns.length() - 1);
values.deleteCharAt(values.length() - 1);
if (checkConditions.length() > 0) {
checkConditions.setLength(checkConditions.length() - 5); // 去除最后的 AND
}
// 构建 SQL 语句
String checkSql = "SELECT COUNT(*) FROM " + TARGET_TABLE + " WHERE " + checkConditions.toString();
String insertSql = "INSERT INTO " + TARGET_TABLE + " (" + insertColumns.toString() + ") VALUES (" + values.toString() + ")";
// 创建准备好的语句
PreparedStatement checkStatement = connection.prepareStatement(checkSql);
PreparedStatement insertStatement = connection.prepareStatement(insertSql);
PreparedStatement queryStatement = connection.prepareStatement("SELECT * FROM " + SOURCE_TABLE);
ResultSet sourceResult = queryStatement.executeQuery();
ResultSetMetaData resultSetMetaData = sourceResult.getMetaData();
int columnCount = resultSetMetaData.getColumnCount();
int numMigrated = 0;//计算迁移量
// 逐行处理源表数据
while (sourceResult.next()) {
boolean isDuplicate = false;
// 设置检查重复的参数
if (hasUniqueColumn) {
for (int i = 1; i <= columnCount; i++) {
String columnName = resultSetMetaData.getColumnName(i);
if (columnName.equals(UNIQUE_COLUMN)) {
checkStatement.setObject(1, sourceResult.getObject(i));
}
}
// 执行检查重复查询
ResultSet checkResult = checkStatement.executeQuery();
if (checkResult.next()) {
int count = checkResult.getInt(1);
if (count > 0) {
isDuplicate = true;
}
}
}
// 如果不是重复记录,则插入
if (!isDuplicate) {
for (int i = 1; i <= columnCount; i++) {
insertStatement.setObject(i, sourceResult.getObject(i));
}
insertStatement.executeUpdate();
numMigrated++;
}
}
System.out.println("数据迁移成功!"+"同步迁移数量:" + numMigrated);
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
通过java同步mysql从一张表的数据同步到另一张表(带去重+计算迁移量)
于 2024-08-26 14:20:06 首次发布