数据库有60w条数据,如何使用多线程对这些数据进行修改,Java实现
时间: 2024-03-17 16:40:56 浏览: 23
Java实现多线程对数据库进行修改的代码示例如下:
```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class MultiThreadUpdateDataBase {
// 数据库连接信息
private static final String DB_URL = "jdbc:mysql://localhost:3306/test";
private static final String DB_USER = "root";
private static final String DB_PASSWORD = "123456";
// 每个批次包含的数据量
private static final int BATCH_SIZE = 1000;
// 修改数据的 SQL 语句
private static final String UPDATE_SQL = "UPDATE user SET age = age + 1 WHERE id = ?";
// 查询数据的 SQL 语句
private static final String SELECT_SQL = "SELECT id FROM user LIMIT ?, ?";
public static void main(String[] args) {
// 计算需要分成的批次数量
int totalCount = 600000;
int batchCount = totalCount / BATCH_SIZE + 1;
// 创建多个线程
List<Thread> threads = new ArrayList<>();
for (int i = 0; i < batchCount; i++) {
int start = i * BATCH_SIZE;
int end = start + BATCH_SIZE;
if (end > totalCount) {
end = totalCount;
}
Thread thread = new UpdateThread(start, end);
threads.add(thread);
thread.start();
}
// 等待所有线程执行完成
for (Thread thread : threads) {
try {
thread.join();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
// 合并数据
// ...
}
private static class UpdateThread extends Thread {
private int start;
private int end;
public UpdateThread(int start, int end) {
this.start = start;
this.end = end;
}
@Override
public void run() {
// 连接数据库
Connection conn = null;
try {
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
conn.setAutoCommit(false);
// 查询需要修改的数据
PreparedStatement selectStmt = conn.prepareStatement(SELECT_SQL);
selectStmt.setInt(1, start);
selectStmt.setInt(2, end - start);
List<Integer> ids = new ArrayList<>();
selectStmt.execute();
while (selectStmt.getResultSet().next()) {
ids.add(selectStmt.getResultSet().getInt("id"));
}
// 修改数据
PreparedStatement updateStmt = conn.prepareStatement(UPDATE_SQL);
for (int id : ids) {
updateStmt.setInt(1, id);
updateStmt.addBatch();
}
updateStmt.executeBatch();
// 提交事务并关闭数据库连接
conn.commit();
updateStmt.close();
selectStmt.close();
conn.close();
} catch (SQLException e) {
try {
if (conn != null) {
conn.rollback();
conn.close();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}
}
}
}
```
需要注意的是,这只是一个简单的示例代码,实际应用中还需要考虑一些细节问题,如线程安全、错误处理、数据合并等。