用java同步两个oracle数据库中的表数据,两个库表的表结构相同,在不知道表栏位的情况下,实现多表并发执行同步
时间: 2023-09-16 12:09:58 浏览: 90
java多个数据库实现数据同步
4星 · 用户满意度95%
实现多表并发执行同步的基本思路如下:
1.获取源数据库和目标数据库的连接;
2.获取源数据库中所有表的表名和表结构;
3.在目标数据库中创建与源数据库相同的表结构;
4.分别对每个表进行数据同步。
Java中可以使用JDBC API来连接Oracle数据库,具体实现如下:
```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class OracleSync {
private String sourceUrl; // 源数据库连接字符串
private String sourceUsername; // 源数据库用户名
private String sourcePassword; // 源数据库密码
private String targetUrl; // 目标数据库连接字符串
private String targetUsername; // 目标数据库用户名
private String targetPassword; // 目标数据库密码
public OracleSync(String sourceUrl, String sourceUsername, String sourcePassword,
String targetUrl, String targetUsername, String targetPassword) {
this.sourceUrl = sourceUrl;
this.sourceUsername = sourceUsername;
this.sourcePassword = sourcePassword;
this.targetUrl = targetUrl;
this.targetUsername = targetUsername;
this.targetPassword = targetPassword;
}
public void sync() {
Connection sourceConn = null;
Connection targetConn = null;
try {
// 获取源数据库和目标数据库的连接
sourceConn = DriverManager.getConnection(sourceUrl, sourceUsername, sourcePassword);
targetConn = DriverManager.getConnection(targetUrl, targetUsername, targetPassword);
// 获取源数据库中所有表的表名和表结构
Statement stmt = sourceConn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT table_name, column_name, data_type FROM user_tab_cols");
Map<String, List<String>> sourceTables = new HashMap<>();
while (rs.next()) {
String tableName = rs.getString("table_name");
String columnName = rs.getString("column_name");
String dataType = rs.getString("data_type");
if (!sourceTables.containsKey(tableName)) {
sourceTables.put(tableName, new ArrayList<String>());
}
sourceTables.get(tableName).add(columnName + " " + dataType);
}
// 在目标数据库中创建与源数据库相同的表结构
Statement stmt2 = targetConn.createStatement();
for (String tableName : sourceTables.keySet()) {
String tableSql = "CREATE TABLE " + tableName + "(" + String.join(",", sourceTables.get(tableName)) + ")";
stmt2.execute(tableSql);
}
// 分别对每个表进行数据同步
for (String tableName : sourceTables.keySet()) {
Statement stmt3 = sourceConn.createStatement();
ResultSet rs2 = stmt3.executeQuery("SELECT * FROM " + tableName);
Statement stmt4 = targetConn.createStatement();
while (rs2.next()) {
String insertSql = "INSERT INTO " + tableName + " VALUES(";
for (int i = 1; i <= rs2.getMetaData().getColumnCount(); i++) {
insertSql += "'" + rs2.getString(i) + "',";
}
insertSql = insertSql.substring(0, insertSql.length() - 1) + ")";
stmt4.executeUpdate(insertSql);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (sourceConn != null) {
sourceConn.close();
}
if (targetConn != null) {
targetConn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
```
这个类接受源数据库和目标数据库的连接字符串、用户名和密码,提供一个sync方法来执行同步操作。在sync方法中,我们首先获取源数据库中所有表的表名和表结构,然后在目标数据库中创建与源数据库相同的表结构,最后分别对每个表进行数据同步。在数据同步时,我们使用ResultSet来获取源数据库中的数据,然后使用Statement来将数据插入到目标数据库中。
为了支持多表并发执行同步,我们可以使用线程池来同时处理多个表的数据同步。具体实现如下:
```java
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
public class OracleSync {
private String sourceUrl; // 源数据库连接字符串
private String sourceUsername; // 源数据库用户名
private String sourcePassword; // 源数据库密码
private String targetUrl; // 目标数据库连接字符串
private String targetUsername; // 目标数据库用户名
private String targetPassword; // 目标数据库密码
public OracleSync(String sourceUrl, String sourceUsername, String sourcePassword,
String targetUrl, String targetUsername, String targetPassword) {
this.sourceUrl = sourceUrl;
this.sourceUsername = sourceUsername;
this.sourcePassword = sourcePassword;
this.targetUrl = targetUrl;
this.targetUsername = targetUsername;
this.targetPassword = targetPassword;
}
public void sync() {
Connection sourceConn = null;
Connection targetConn = null;
try {
// 获取源数据库和目标数据库的连接
sourceConn = DriverManager.getConnection(sourceUrl, sourceUsername, sourcePassword);
targetConn = DriverManager.getConnection(targetUrl, targetUsername, targetPassword);
// 获取源数据库中所有表的表名和表结构
Statement stmt = sourceConn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT table_name, column_name, data_type FROM user_tab_cols");
Map<String, List<String>> sourceTables = new HashMap<>();
while (rs.next()) {
String tableName = rs.getString("table_name");
String columnName = rs.getString("column_name");
String dataType = rs.getString("data_type");
if (!sourceTables.containsKey(tableName)) {
sourceTables.put(tableName, new ArrayList<String>());
}
sourceTables.get(tableName).add(columnName + " " + dataType);
}
// 在目标数据库中创建与源数据库相同的表结构
Statement stmt2 = targetConn.createStatement();
for (String tableName : sourceTables.keySet()) {
String tableSql = "CREATE TABLE " + tableName + "(" + String.join(",", sourceTables.get(tableName)) + ")";
stmt2.execute(tableSql);
}
// 使用线程池来同时处理多个表的数据同步
ExecutorService executor = Executors.newFixedThreadPool(10);
for (String tableName : sourceTables.keySet()) {
executor.submit(new SyncTask(sourceConn, targetConn, tableName));
}
executor.shutdown();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (sourceConn != null) {
sourceConn.close();
}
if (targetConn != null) {
targetConn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private class SyncTask implements Runnable {
private Connection sourceConn;
private Connection targetConn;
private String tableName;
public SyncTask(Connection sourceConn, Connection targetConn, String tableName) {
this.sourceConn = sourceConn;
this.targetConn = targetConn;
this.tableName = tableName;
}
public void run() {
try {
Statement stmt3 = sourceConn.createStatement();
ResultSet rs2 = stmt3.executeQuery("SELECT * FROM " + tableName);
Statement stmt4 = targetConn.createStatement();
while (rs2.next()) {
String insertSql = "INSERT INTO " + tableName + " VALUES(";
for (int i = 1; i <= rs2.getMetaData().getColumnCount(); i++) {
insertSql += "'" + rs2.getString(i) + "',";
}
insertSql = insertSql.substring(0, insertSql.length() - 1) + ")";
stmt4.executeUpdate(insertSql);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
```
在这个实现中,我们使用了一个SyncTask类来表示一个表的数据同步任务,它实现了Runnable接口。在其run方法中,我们使用ResultSet来获取源数据库中的数据,然后使用Statement来将数据插入到目标数据库中。在sync方法中,我们使用线程池来同时处理多个表的数据同步任务。这样就可以实现多表并发执行同步了。
阅读全文