使用Java实现Oracle表结构转换为PostgreSQL的示例方案(AI)

核心代码

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;public class OracleToPGConverter {// 类型映射表private static final Map<String, String> TYPE_MAPPING = new HashMap<>();static {TYPE_MAPPING.put("VARCHAR2", "VARCHAR");TYPE_MAPPING.put("NUMBER", "NUMERIC");TYPE_MAPPING.put("DATE", "TIMESTAMP");TYPE_MAPPING.put("CLOB", "TEXT");TYPE_MAPPING.put("BLOB", "BYTEA");}public static void main(String[] args) {String oracleTable = "EMPLOYEES";try {// 1. 连接Oracle数据库Connection oracleConn = DriverManager.getConnection("jdbc:oracle:thin:@host:port:sid", "user", "password");// 2. 获取表结构元数据TableMetaData tableMeta = extractMetaData(oracleConn, oracleTable);// 3. 生成PG DDLString pgDDL = generatePGDDL(tableMeta);// 4. 连接PostgreSQL执行Connection pgConn = DriverManager.getConnection("jdbc:postgresql://host:port/dbname", "user", "password");executeDDL(pgConn, pgDDL);// 关闭连接oracleConn.close();pgConn.close();System.out.println("转换完成！");} catch (SQLException e) {e.printStackTrace();}}// 提取Oracle元数据private static TableMetaData extractMetaData(Connection conn, String tableName) throws SQLException {TableMetaData meta = new TableMetaData();meta.tableName = tableName.toLowerCase(); // PG默认小写// 获取列信息try (Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("SELECT column_name, data_type, data_length, data_precision, data_scale, nullable " +"FROM all_tab_columns WHERE table_name = '" + tableName + "'")) {while (rs.next()) {Column col = new Column();col.name = rs.getString("column_name").toLowerCase();col.type = rs.getString("data_type");col.length = rs.getInt("data_length");col.precision = rs.getInt("data_precision");col.scale = rs.getInt("data_scale");col.nullable = "Y".equals(rs.getString("nullable"));meta.columns.add(col);}}// 获取主键信息try (Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("SELECT column_name FROM all_cons_columns " +"WHERE constraint_name = ( " +"  SELECT constraint_name FROM all_constraints " +"  WHERE table_name = '" + tableName + "' AND constraint_type = 'P'))")) {while (rs.next()) {meta.primaryKeys.add(rs.getString("column_name").toLowerCase());}}return meta;}// 生成PostgreSQL DDLprivate static String generatePGDDL(TableMetaData meta) {StringBuilder ddl = new StringBuilder();ddl.append("CREATE TABLE ").append(meta.tableName).append(" (\n");List<String> columnDefs = new ArrayList<>();for (Column col : meta.columns) {String def = "  " + col.name + " " + mapDataType(col);if (!col.nullable) def += " NOT NULL";columnDefs.add(def);}if (!meta.primaryKeys.isEmpty()) {columnDefs.add("  PRIMARY KEY (" + String.join(", ", meta.primaryKeys) + ")");}ddl.append(String.join(",\n", columnDefs));ddl.append("\n);");return ddl.toString();}// 数据类型转换private static String mapDataType(Column col) {String mappedType = TYPE_MAPPING.getOrDefault(col.type, "TEXT");if ("NUMBER".equals(col.type)) {if (col.scale > 0) {return "NUMERIC(" + col.precision + "," + col.scale + ")";} else if (col.precision > 0) {return "NUMERIC(" + col.precision + ")";}return "NUMERIC";}if ("VARCHAR2".equals(col.type)) {return "VARCHAR(" + (col.length > 0 ? col.length : 255) + ")";}return mappedType;}// 执行DDLprivate static void executeDDL(Connection conn, String ddl) throws SQLException {try (Statement stmt = conn.createStatement()) {stmt.executeUpdate(ddl);}}// 元数据存储类static class TableMetaData {String tableName;List<Column> columns = new ArrayList<>();List<String> primaryKeys = new ArrayList<>();}// 列定义类static class Column {String name;String type;int length;int precision;int scale;boolean nullable;}
}

使用说明

‌1.配置数据库连接‌

修改Oracle连接字符串（jdbc:oracle:thin:@host:port）
修改PostgreSQL连接字符串（jdbc:postgresql://host/dbname）
填写正确的用户名密码

2.功能扩展建议

// 添加索引转换（示例）
private static void extractIndexes(Connection conn, TableMetaData meta) throws SQLException {try (Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("SELECT index_name, column_name " +"FROM all_ind_columns " +"WHERE table_name = '" + meta.tableName.toUpperCase() + "'")) {while (rs.next()) {String indexName = rs.getString("index_name");String column = rs.getString("column_name").toLowerCase();// 处理索引元数据...}}
}// 处理自增序列（Oracle序列转PG SERIAL）
private static String handleAutoIncrement() {// Oracle: 使用触发器+序列// PostgreSQL: 使用SERIAL类型或GENERATED ALWAYS AS IDENTITYreturn "SERIAL";
}// 添加注释支持
private static void addColumnComments() {// 从Oracle的ALL_COL_COMMENTS获取注释// 生成PostgreSQL的COMMENT语句
}

‌3.‌运行注意事项‌

需要JDBC驱动：
- Oracle: ojdbc8.jar
- PostgreSQL: postgresql-42.x.jar
权限要求：
- Oracle用户需要访问ALL_TAB_COLUMNS等系统视图
- PG用户需要有建表权限
命名规范：
- 强制转换为小写（PostgreSQL默认小写）
- 特殊字符需用引号处理

‌4.数据类型映射扩展

// 更完整的类型映射建议
TYPE_MAPPING.put("NVARCHAR2", "VARCHAR");
TYPE_MAPPING.put("RAW", "BYTEA");
TYPE_MAPPING.put("TIMESTAMP(6)", "TIMESTAMP");
TYPE_MAPPING.put("FLOAT", "DOUBLE PRECISION");