java代码实现oracle到oracle的表迁移
时间: 2023-10-24 22:09:08 浏览: 38
以下是Java代码实现Oracle到Oracle的表迁移的一个示例:
```java
import java.sql.*;
public class OracleTableMigration {
public static void main(String[] args) {
Connection sourceConn = null;
Connection targetConn = null;
Statement sourceStatement = null;
Statement targetStatement = null;
ResultSet resultSet = null;
String sourceUrl = "jdbc:oracle:thin:@source_server:1521:ORCL";
String sourceUser = "source_username";
String sourcePassword = "source_password";
String targetUrl = "jdbc:oracle:thin:@target_server:1521:ORCL";
String targetUser = "target_username";
String targetPassword = "target_password";
try {
// Connect to source database
sourceConn = DriverManager.getConnection(sourceUrl, sourceUser, sourcePassword);
sourceStatement = sourceConn.createStatement();
// Connect to target database
targetConn = DriverManager.getConnection(targetUrl, targetUser, targetPassword);
targetStatement = targetConn.createStatement();
// Query source table
String query = "SELECT * FROM source_table";
resultSet = sourceStatement.executeQuery(query);
// Create target table
ResultSetMetaData rsmd = resultSet.getMetaData();
int columnCount = rsmd.getColumnCount();
String columns = "";
for (int i = 1; i <= columnCount; i++) {
String name = rsmd.getColumnName(i);
String type = rsmd.getColumnTypeName(i);
columns += name + " " + type + ",";
}
columns = columns.substring(0, columns.length() - 1);
String createTable = "CREATE TABLE target_table (" + columns + ")";
targetStatement.executeUpdate(createTable);
// Insert data into target table
while (resultSet.next()) {
String insert = "INSERT INTO target_table VALUES (";
for (int i = 1; i <= columnCount; i++) {
String value = resultSet.getString(i);
if (value == null) {
insert += "null,";
} else {
insert += "'" + value.replace("'", "''") + "',";
}
}
insert = insert.substring(0, insert.length() - 1) + ")";
targetStatement.executeUpdate(insert);
}
System.out.println("Table migration completed successfully.");
} catch (SQLException e) {
System.out.println("Table migration failed. Error message: " + e.getMessage());
} finally {
try {
if (resultSet != null) resultSet.close();
if (sourceStatement != null) sourceStatement.close();
if (sourceConn != null) sourceConn.close();
if (targetStatement != null) targetStatement.close();
if (targetConn != null) targetConn.close();
} catch (SQLException e) {
System.out.println("Failed to close database connection. Error message: " + e.getMessage());
}
}
}
}
```
在这个示例中,我们使用JDBC连接到源数据库和目标数据库,然后执行以下操作:
1. 查询源表。
2. 创建目标表,使用与源表相同的列名和数据类型。
3. 将源表中的数据插入到目标表中。
请注意,这个示例假定源表和目标表在两个不同的数据库中。如果源表和目标表在同一个数据库中,您可以省略连接到第二个数据库的步骤,并将目标表的名称设置为源表的名称。
此外,请注意,这个示例只能处理普通的表。如果源表包含视图、存储过程或其他类型的数据库对象,您需要采取不同的方法来进行迁移。