多线程的方式的将100万条数据，从一个数据库迁移到另一个数据库

以下是使用Java多线程方式将100万条数据从一个数据库迁移到另一个数据库的示例代码：

import java.sql.*;

import java.util.concurrent.ExecutorService;

import java.util.concurrent.Executors;

import java.util.concurrent.TimeUnit;

public class DataMigration {

public static void main(String[] args) {

int batchSize = 10000; // 每个批次的数据量

int totalCount = 1000000; // 要迁移的总数据量

int threadCount = 4; // 线程数

String sourceUrl = "jdbc:mysql://localhost:3306/source_db";

String targetUrl = "jdbc:mysql://localhost:3306/target_db";

String sourceUsername = "root";

String sourcePassword = "password";

String targetUsername = "root";

String targetPassword = "password";

ExecutorService executor = Executors.newFixedThreadPool(threadCount);

try (

Connection sourceConn = DriverManager.getConnection(sourceUrl, sourceUsername, sourcePassword);

Connection targetConn = DriverManager.getConnection(targetUrl, targetUsername, targetPassword);

Statement sourceStmt = sourceConn.createStatement();

PreparedStatement targetStmt = targetConn.prepareStatement("INSERT INTO target_table VALUES (?, ?, ?)")

) {

// 设置源数据库查询的起始位置和每个批次的数据量

int offset = 0;

sourceStmt.setFetchSize(batchSize);

while (offset < totalCount) {

for (int i = 0; i < threadCount; i++) {

// 创建一个线程来处理每个批次的数据

executor.submit(new DataMigrationThread(offset, batchSize, sourceStmt, targetStmt));

offset += batchSize;

}

}

} catch (SQLException e) {

e.printStackTrace();

}

executor.shutdown();

try {

executor.awaitTermination(Long.MAX_VALUE, TimeUnit.NANOSECONDS);

} catch (InterruptedException e) {

e.printStackTrace();

}

}

private static class DataMigrationThread implements Runnable {

private final int offset;

private final int batchSize;

private final Statement sourceStmt;

private final PreparedStatement targetStmt;

public DataMigrationThread(int offset, int batchSize, Statement sourceStmt, PreparedStatement targetStmt) {

this.offset = offset;

this.batchSize = batchSize;

this.sourceStmt = sourceStmt;

this.targetStmt = targetStmt;

}

@Override

public void run() {

try {

// 查询数据并将其添加到批处理中

ResultSet rs = sourceStmt.executeQuery("SELECT * FROM source_table LIMIT " + batchSize + " OFFSET " + offset);

while (rs.next()) {

int column1 = rs.getInt("column1");

String column2 = rs.getString("column2");

Date column3 = rs.getDate("column3");

targetStmt.setInt(1, column1);

targetStmt.setString(2, column2);

targetStmt.setDate(3, column3);

targetStmt.addBatch();

}

// 执行批处理并提交事务

targetStmt.executeBatch();

targetStmt.getConnection().commit();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}

在这个示例中，我们使用了Java中的线程池来创建多个线程来同时处理数据。我们将要迁移的数据分成多个批次，然后为每个批次创建一个线程来处理。在每个线程中，我们从源数据库中查询数据，然后将其添加到批处理中。