用java代码写一个mysql转oracle代码的方法
时间: 2023-05-10 19:55:37 浏览: 260
可以使用以下代码将 MySQL 数据库中的表转换为 Oracle 数据库中的表:
```
public static void mysqlToOracle(String mysqlTable, String oracleTable) {
Connection mysqlConn = null;
Connection oracleConn = null;
Statement mysqlStmt = null;
Statement oracleStmt = null;
ResultSet mysqlRs = null;
try {
// MySQL 数据库连接
Class.forName("com.mysql.jdbc.Driver");
mysqlConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "password");
mysqlStmt = mysqlConn.createStatement();
mysqlRs = mysqlStmt.executeQuery("SELECT * FROM " + mysqlTable);
// Oracle 数据库连接
Class.forName("oracle.jdbc.driver.OracleDriver");
oracleConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
oracleStmt = oracleConn.createStatement();
// 创建 Oracle 表
ResultSetMetaData mysqlMeta = mysqlRs.getMetaData();
int columnCount = mysqlMeta.getColumnCount();
StringBuilder createSql = new StringBuilder("CREATE TABLE " + oracleTable + " (");
for (int i = 1; i <= columnCount; i++) {
String columnName = mysqlMeta.getColumnName(i);
String columnType = mysqlMeta.getColumnTypeName(i);
createSql.append(columnName).append(" ");
if (columnType.equalsIgnoreCase("varchar")) {
createSql.append("VARCHAR2(255)");
} else if (columnType.equalsIgnoreCase("int")) {
createSql.append("NUMBER(10)");
} else if (columnType.equalsIgnoreCase("datetime")) {
createSql.append("DATE");
} else {
createSql.append(columnType);
}
if (i < columnCount) {
createSql.append(", ");
}
}
createSql.append(")");
oracleStmt.executeUpdate(createSql.toString());
// 插入 Oracle 数据
while (mysqlRs.next()) {
StringBuilder insertSql = new StringBuilder("INSERT INTO " + oracleTable + " VALUES (");
for (int i = 1; i <= columnCount; i++) {
String columnValue = mysqlRs.getString(i);
if (columnValue == null) {
insertSql.append("NULL");
} else {
insertSql.append("'").append(columnValue).append("'");
}
if (i < columnCount) {
insertSql.append(", ");
}
}
insertSql.append(")");
oracleStmt.executeUpdate(insertSql.toString());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (mysqlRs != null) mysqlRs.close();
if (mysqlStmt != null) mysqlStmt.close();
if (mysqlConn != null) mysqlConn.close();
if (oracleStmt != null) oracleStmt.close();
if (oracleConn != null) oracleConn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
```
其中,`mysqlTable` 为 MySQL 数据库中的表名,`oracleTable` 为 Oracle 数据库中的表名。在该方法中,首先通过 MySQL 数据库连接获取 MySQL 表的元数据,然后根据元数据创建 Oracle 表,并将 MySQL 数据插入到 Oracle 表中。