db2转orcale 建表篇

最新推荐文章于 2023-08-29 16:43:41 发布

尖笔尖

最新推荐文章于 2023-08-29 16:43:41 发布

阅读量389

点赞数

分类专栏：实用工具文章标签：数据库 sql oracle

本文链接：https://blog.csdn.net/LIFE_PLAN/article/details/129094623

版权

实用工具专栏收录该内容

1 篇文章 0 订阅

订阅专栏

最近有个需求，要将一个项目的后台数据库从 db2 转到 orcale，拿到数据库一看，我滴个乖乖，几百个表，这要是一个一个的去手工转表，估计得花我好长时间，果断上网收集资料看看有没有什么快捷方式，其实方法很简单，通过 jdbc 可以获取到数据库元数据，然后通过元数据就可以拿到所有表信息，有了方向，说干就干。

思路很简单：首先查询所有 db2 的表名，然后挨个的去创建 orcale 对应的建表语句，生成建表语句后还要生成索引创建信息，最后将他们保存的一个 sql 文件中，然后就可以一键执行了。

获取所有表名

这个很简单，调用元数据 DatabaseMetaData 对象的 getTables() 方法即可，直接看代码

public List<String> getAllTableNames(
DatabaseMetaData metaData, 
String db2Schema) throws Exception {

    List<String> tableNameList = new ArrayList<String>();
    ResultSet resultSet = metaData.getTables(null, db2Schema, null, new String[]{"TABLE"}));
    while (resultSet.next()) {
        String tableName = resultSet.getString("TABLE_NAME");
        tableNameList.add(tableName);
    }
    return tableNameList;

}

生成建表语句

主要是通过元数据 DatabaseMetaData 对象的 getColumns() 方法获取表字段信息，包含字段名称、数据类型、字段长度、小数位数、默认值、是否允许为空标示位，以及字段描述信息，有了这些信息我们就可以生成建表语句了。

其中，需要注意的地方有：

1、db2 对于字符串类型而言是可以存空字符串的，而 orcale 会将空字符串转化成 null，所以一般来讲，在 orcale 中，空字符串我们会转存为一个空格；

2、对于某些字段类型，要做一定的转化。

private List<String> genCreateTableSql(
    DatabaseMetaData metaData, 
    String db2Schema, 
    String orcaleSchema,
    String tableName, 
    String tableSpaceName) throws Exception {
    
    List<String> res = new ArrayList<>();
    List<String> commentList = new ArrayList<>();
    String comment = "";

    StringBuffer sql = new StringBuffer();
    sql.append(String.format("------------- CREATE TABLE %s.%s ------------------------\n\n", orcaleSchema, tableName));
    sql.append("CREATE TABLE ").append(orcaleSchema).append(".").append(tableName).append(" (\n");

    // 获取所有列信息
    try(ResultSet columnResultSet = metaData.getColumns(null,db2Schema, tableName, "%")){
        while (columnResultSet.next()) {
            // 字段名称
            String columnName = columnResultSet.getString("COLUMN_NAME");
            // 数据类型
            String columnType = columnResultSet.getString("TYPE_NAME");
            // 字段长度
            int datasize = columnResultSet.getInt("COLUMN_SIZE");
            // 小数部分位数
            int digits = columnResultSet.getInt("DECIMAL_DIGITS");
            // 默认值
            String column_def = columnResultSet.getString("COLUMN_DEF");
            // 空字符串特殊处理。
            if (StringUtils.isNoneBlank(column_def) && column_def.equals("''")) {
                column_def = "' '";
            }
            // 是否可为空 1代表可空 0代表不可为空
            int nullable = columnResultSet.getInt("NULLABLE");
            // 描述
            String remarks = columnResultSet.getString("REMARKS");
 
            sql.append("\t").append(columnName).append(" ");
            sql.append(columnTypeMap(columnType, datasize, digits)).append(" ");

            if (StringUtils.isNoneBlank(column_def)) {
                sql.append("DEFAULT ON NULL ").append(column_def).append(" ");
            }
            if (nullable == 0) {
                sql.append("NOT NULL,\n");
            } else {
                sql.append(",\n");
            }
            if(StringUtils.isNoneBlank(remarks)){
                comment = String.format("comment on column %s.%s.%s is '%s';\n", orcaleSchema, tableName, columnName, remarks);
                commentList.add(comment);
            }
        }
    }

    sql.deleteCharAt(sql.length() - 2);
    sql.append(") tablespace ").append(tableSpaceName).append(";");

    // 保存建表语句
    res.add(sql.toString());
    // 保存字段描述信息
    res.addAll(commentList);

    return res;
}

// 字段类型转换，这里只包含部分，根据实际需要添加
private String columnTypeMap(String columnType, int datasize, int digits) {
    switch (columnType) {
        case "BIGINT":
            return "NUMBER(19,0)";
        case "VARCHAR":
        case "CHAR":
            return "VARCHAR(" + datasize + ")";
        case "DECIMAL":
            return "NUMBER(" + datasize + "," + digits + ")";
        case "DOUBLE":
            return "FLOAT(53)";
        case "INTEGER":
            return "NUMBER(11,0)";
        case "SMALLINT":
            return "NUMBER(6,0)";
        case "TIME":
            return "DATE";
        default:
            return columnType;
    }
}

生成创建索引语句

索引要区分 主键索引、唯一索引、普通索引，因为三类索引的创建语句不一致。

主键索引可以通过元数据 DatabaseMetaData 对象的 getPrimaryKeys() 方法获取，其他索引则是通过getIndexInfo() 方法获取。

getIndexInfo() 方法参数比较多，单独看下：

getIndexInfo(String catalog, String schema, String table, boolean unique, boolean approximate):

catalog : 类别名称，直接填 null 即可；
schema : 如果表都位于一个模式下，就填那个模式名，要不然填 null 即可；
table : 表名称；
unique : 该参数为 true 时，仅返回惟一值的索引；该参数为 false 时，返回所有索引，不管它们是否惟一
approximate : 是否要求返回精确结果，false 表示是，一般填 false。

需要注意的就是 unique 参数，如果你传 true，只能获取到唯一索引，只有传 false 才可以获取到普通索引，但是它同时还包含了主键索引，所以我们要首先获取主键索引，然后在获取其他索引时，还要过滤一次，以免重复创建索引。

private List<String> genCreateIndexSql(
    DatabaseMetaData metaData,
    String db2Schema, 
    String orcaleSchema, 
    String tableName,
	String indexSpaceName) throws Exception {
    
    List<String> res = new ArrayList<String>();
    String primaryKeyName = "";
    String sql = "";
    List<String> primaryKeysist = new ArrayList<String>();

    // 获取主键索引
    try(ResultSet primaryKeys = metaData.getPrimaryKeys(null, db2Schema, tableName)){
        while (primaryKeys.next()) {
            primaryKeyName = primaryKeys.getString("PK_NAME");
            String COLUMN_NAME = primaryKeys.getString("COLUMN_NAME");
            primaryKeysist.add(COLUMN_NAME);
        }
    }

    // 生成创建主键索引语句
    if (primaryKeysist.size() > 0) {
        sql = String.format("ALTER TABLE %s.%s ADD CONSTRAINT %s PRIMARY KEY (", orcaleSchema, tableName, primaryKeyName);
        sql += primaryKeysist.get(0);
        for (int i = 1; i < primaryKeysist.size(); i++) {
            sql = sql + ", " + primaryKeysist.get(i);
        }
        sql += ") using index tablespace" + indexSpaceName + ";\n";
        res.add(sql);
    }

    String preIndexName = "";
    StringBuffer sb = new StringBuffer();

    // 获取所有索引信息
    try(ResultSet otherIndexs = metaData.getIndexInfo(null, DataSourceConfig.DB2SCHEMA, tableName, false, false)){
        while (otherIndexs.next()) {
            String indexName = otherIndexs.getString("INDEX_NAME");
            if(StringUtils.isBlank(indexName)){
                if(sb.length() > 0){
                    sb.append(") tablespace TBS_CUR_IDX;\n");
                    sql = sb.toString();
                    res.add(sql);
                }
                break;
            }
            // 过滤掉主键索引
            if(indexName.equals(primaryKeyName)){
                continue;
            }
            String columnName = otherIndexs.getString("COLUMN_NAME");
            // 区分 唯一索引 和 普通索引
            boolean noUnique = otherIndexs.getBoolean("NON_UNIQUE");
            if(!preIndexName.equals(indexName)){
                if(!"".equals(preIndexName)){
                    sb.append(") tablespace TBS_CUR_IDX;\n");
                    sql = sb.toString();
                    res.add(sql);
                    sb.setLength(0);
                }
                preIndexName = indexName;
                sb.append("CREATE ");
                if(!noUnique){
                    sb.append("UNIQUE ");
                }
                sb.append("INDEX ").append(DataSourceConfig.ORCALESCHEMA).append(".").append(indexName).append(" ON ").append(DataSourceConfig.ORCALESCHEMA).append(".").append(tableName).append("(");
                sb.append(columnName);
            }else{
                sb.append(",").append(columnName);
            }
        }
    }

    return res;
}

至此，我们已经拿到了完整的建表信息，剩下的就是把它写到文件里就行了，这个很简单，就不单独写了。

此外，如果存在视图的话我们也可以通过元数据获取：

创建视图

1、首先获取所有视图名，这个和获取表名方法一致，只是 getTable() 方法最后一个参数有点区别。

public List<String> getAllViewNames(
DatabaseMetaData metaData, 
String db2Schema) throws Exception {

    List<String> viewNameList = new ArrayList<String>();
    ResultSet resultSet = metaData.getTables(null, db2Schema, null, new String[]{"VIEW"}));
    while (resultSet.next()) {
        String tableName = resultSet.getString("TABLE_NAME");
        viewNameList.add(tableName);
    }
    return viewNameList;
   
    return null;

}

2、生成视图创建语句，db2 可以通过 sql 语句

SELECT TEXT FROM syscat.views "WHERE viewschema='%s' AND viewname='%s'

来获取视图创建语句，只要将模式名替换一下即可。

private List<String> genCreateViewSql(
    Connection dbCon, 
    String viewName,
    String db2Schema,
    String orcaleSchema) throws Exception {
    
    List<String> res = new ArrayList<>();
    List<String> commentList = new ArrayList<>();

    String sql = String.format("SELECT TEXT FROM syscat.views " +
            "WHERE viewschema='%s' AND viewname='%s'", db2Schema, viewName);

    PreparedStatement db2Stmt = dbCon.prepareStatement(sql);
    ResultSet rs = db2Stmt.executeQuery();

    while (rs.next()){
        sql = rs.getString(1);
    }

    sql = sql.replaceAll(db2Schema, orcaleSchema);

    res.add(String.format("------------- CREATE VIEW %s.%s ------------------------\n\n", orcaleSchema, viewName));
    res.add(sql + ";");
    res.add("\n");
    return res;
}