Oracle文章系列--批量转表结构导出到EXCEL

之前手上有一个自己写的数据库设计EXCEL模板，通过宏控件能够生产sql语句。维护好EXCEL，读取也比较方便。这次涉及oracle迁移tidb，想着也复用一下该模板，同时解决项目组之前文档缺失的问题。

处理思路

1、通过java读取数据库信息，然后将表结构的基础信息写入到指定的模板中。
补充编写好的代码：

表头类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;

public class TableHeaderExcelProperty extends BaseRowModel {

    /**
     * value: 表头名称
     * index: 列的号, 0表示第一列
     */
    @ExcelProperty(value = "姓名", index = 0)
    private String name;

    @ExcelProperty(value = "年龄",index = 1)
    private int age;

    @ExcelProperty(value = "学校",index = 2)
    private String school;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getSchool() {
        return school;
    }

    public void setSchool(String school) {
        this.school = school;
    }
}

数据库表结构读取并批量写入到EXCEL中

public class OracleTableToTidbUtil {
    /**
     * 打印所有表结构
     *
     * @throws Exception exception
     */
    public static void printTableStructure(Connection connection,String catalog,String tabNamePattern) throws Exception {


        //connection = DriverManager.getConnection(url,props);

        DatabaseMetaData metaData = connection.getMetaData();
        // 获取所有表
        ResultSet tableResultSet = metaData.getTables(catalog, null, tabNamePattern, new String[]{"TABLE"});
        String filePath = "D:/oracleToTidbTable.xlsx";



        ///----------------读取单个表
        int sheetNo=5;
        ExcelWriter excelWriter = null;
        //EasyExcel.write(filePath, TableHeaderExcelProperty.class).sheet(tableName).doWrite(data);
        // 这里 指定文件
        excelWriter = EasyExcel.write(filePath).build();
        ArrayList<CatalogHeader> catalogDataList = new ArrayList<>();
        int catalogSeqNo=1;
        while (tableResultSet.next()) {
            CatalogHeader catalogHeader = new CatalogHeader();

            String tableName = tableResultSet.getString("TABLE_NAME");
            String tableNameToL = tableName.toLowerCase();
            catalogHeader.setTableName(tableNameToL);
            System.out.println("table:" + tableName);

            catalogHeader.setSeqNo(catalogSeqNo++);
            String tableType = tableResultSet.getString("TABLE_TYPE");
            // explanatory comment on the table
            String tableRemark = tableResultSet.getString("REMARKS");
            catalogHeader.setTableRemark(tableRemark);
            catalogDataList.add(catalogHeader);

            // 获取表字段结构
            ResultSet columnResultSet = metaData.getColumns(catalog, "%", tableName, "%");

            ArrayList<DbTableHeader> data = new ArrayList<>();
            int seqNo=0;
            while (columnResultSet.next()) {
                DbTableHeader dbTableHeader = new DbTableHeader();
                seqNo=seqNo+1;
                dbTableHeader.setSeqNo(seqNo);
                // 字段名称
                String columnName = columnResultSet.getString("COLUMN_NAME").toLowerCase();
                dbTableHeader.setColName(columnName);

                // 数据类型
                String columnType = columnResultSet.getString("TYPE_NAME").toLowerCase();
                // 字段长度
                int datasize = columnResultSet.getInt("COLUMN_SIZE");
                // 小数部分位数
                int digits = columnResultSet.getInt("DECIMAL_DIGITS");
                //如果是number，转为decimal
                String colTypeAndlength="";
                if("number".equals(columnType)){//number Oracle底层实现，方便迁移别的数据库
                    //number 不指定长度比较麻烦 转换成 decimal(0,-127)
                    if(datasize==0){//代表是默认的number，小数位为0，最长精度38位
                        colTypeAndlength="bigint";
                    }else {
                        //SMALLINT	SMALLINT	NUMBER(5)
                        //INTEGER	INTEGER	NUMBER(10)
                        //BIGINT	BIGINT	NUMERIC(20)
                        if(digits==0){//代表没有小数位
                            if(datasize<=5){
                                colTypeAndlength="int";
                            }else if(datasize<=10){
                                colTypeAndlength="int";
                            }else if(datasize<=20){
                                colTypeAndlength="bigint";
                            }else{
                                colTypeAndlength="decimal("+datasize+",0)";
                            }
                        }else{
                            colTypeAndlength="decimal("+datasize+","+digits+")";
                        }
                    }
                }else if("decimal".equals(columnType)){
                    //SMALLINT	SMALLINT	NUMBER(5)
                    //INTEGER	INTEGER	NUMBER(10)
                    //BIGINT	BIGINT	NUMERIC(20)
                    if(digits==0){//代表没有小数位
                        if(datasize<=5){
                            colTypeAndlength="int";
                        }else if(datasize<=10){
                            colTypeAndlength="int";
                        }else if(datasize<=20){
                            colTypeAndlength="bigint";
                        }else{
                            colTypeAndlength="decimal("+datasize+",0)";
                        }
                    }else{
                        colTypeAndlength="decimal("+datasize+","+digits+")";
                    }
                }else if(columnType.contains("timestamp")){
                    //65 start_date timestamp(6) with time zone 13 6 0 null
                    System.out.println("-----timestamp----");
                    colTypeAndlength="datetime";
                }else if("varchar2".equals(columnType)){
                    colTypeAndlength="varchar("+datasize+")";
                }else if("clob".equals(columnType)){
                    colTypeAndlength="longtext";
                }else{
                    colTypeAndlength=columnType;
                }
                dbTableHeader.setColTypeAndLength(colTypeAndlength);

                // 是否可为空 1代表可空 0代表不可为空
                int nullable = columnResultSet.getInt("NULLABLE");
                if(nullable == 0){
                    dbTableHeader.setIsNull("否");
                }
                // 描述
               // String remarks = columnResultSet.getString("REMARKS");
                String comments = columnResultSet.getString("REMARKS");
                dbTableHeader.setColComment(comments);
                dbTableHeader.setColShortComment(comments);

                System.out.println(sheetNo+" "+columnName + " " + columnType + " " + datasize + " " + digits + " " + nullable + " " + comments);
                data.add(dbTableHeader);
            }

            // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo,tableNameToL).head(DbTableHeader.class).relativeHeadRowIndex(8).build();

            // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
            excelWriter.write(data, writeSheet);
            sheetNo ++;

            System.out.println("=================================");
        }

        // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
        WriteSheet catalogWriteSheet = EasyExcel.writerSheet(1,"目录").head(CatalogHeader.class).relativeHeadRowIndex(1).build();

        // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
        excelWriter.write(catalogDataList, catalogWriteSheet);

        // 千万别忘记finish 会帮忙关闭流
        if (excelWriter != null) {
            excelWriter.finish();
        }

    }

编写main方法

public class FetchDbTableInfo {

    public static void main(String[] args) throws Exception {
        printOracle();
    }
    private static void printOracle()  throws Exception{

        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@//localhost:1521/orcl";
        String user = "batch";
        String password = "password";
        Class.forName(driver);
        Properties props =new Properties();
        props.setProperty("user",user);
        props.setProperty("password",password);
        props.put("remarksReporting","true");

        Connection connection = DriverManager.getConnection(url,props);

        OracleTableToTidbUtil.printTableStructure(connection,user,"TST_%");
    }
 
}

遇到过的问题

获取字段注释为空的解决办法：

oracle是需要在连接JDBC获取connection时通过一个参数来设置是否能获取comments.具体如下:

Properties props =newProperties();

props.put("remarksReporting","true");
Class.forName("oracle.jdbc.driver.OracleDriver");

dbConn = DriverManager.getConnection(url, props);
DatabaseMetaData dbmd = dbConn.getMetaData();

rs = dbmd.getColumns(null,"OPS$AIMSADM","AIRCRAFTS","CODE");

if(rs.next()) {System.out.println("Remarks: "+ rs.getObject(12)); }
参考：https://www.cnblogs.com/discuss/articles/1866940.html

参考资料：
处理细节获取表结构信息：
java获得数据库信息常用API(DatabaseMetaData)示例https://my.oschina.net/lison/blog/5434

package com.database.manager;   
  
import java.sql.Connection;   
import java.sql.DatabaseMetaData;   
import java.sql.DriverManager;   
import java.sql.ResultSet;   
import java.sql.SQLException;   
  
/**  
 * @author daoger  
 *   
 * 2009-9-24  
 */  
public class DatabaseMetaDateApplication   
{   
    private DatabaseMetaData dbMetaData = null;   
  
    private Connection con = null;   
  
    private void getDatabaseMetaData()   
    {   
        try  
        {   
            if (dbMetaData == null)   
            {   
                Class.forName("oracle.jdbc.driver.OracleDriver");   
                String url = "jdbc:oracle:thin:@192.168.0.2:1521:×××";   
                String user = "×××";   
                String password = "×××";   
                con = DriverManager.getConnection(url, user, password);   
                dbMetaData = con.getMetaData();   
            }   
        } catch (ClassNotFoundException e)   
        {   
            // TODO: handle ClassNotFoundException   
            e.printStackTrace();   
        } catch (SQLException e)   
        {   
            // TODO: handle SQLException   
            e.printStackTrace();   
        }   
    }   
  
    public void colseCon()   
    {   
        try  
        {   
            if (con != null)   
            {   
                con.close();   
            }   
        } catch (SQLException e)   
        {   
            // TODO: handle SQLException   
            e.printStackTrace();   
        }   
    }   
  
    /**  
     * 获得数据库的一些相关信息  
     */  
    public void getDataBaseInformations()   
    {   
        try  
        {   
            System.out.println("URL:" + dbMetaData.getURL() + ";");   
            System.out.println("UserName:" + dbMetaData.getUserName() + ";");   
            System.out.println("isReadOnly:" + dbMetaData.isReadOnly() + ";");   
            System.out.println("DatabaseProductName:" + dbMetaData.getDatabaseProductName() + ";");   
            System.out.println("DatabaseProductVersion:" + dbMetaData.getDatabaseProductVersion() + ";");   
            System.out.println("DriverName:" + dbMetaData.getDriverName() + ";");   
            System.out.println("DriverVersion:" + dbMetaData.getDriverVersion());   
        } catch (SQLException e)   
        {   
            // TODO: handle SQLException   
            e.printStackTrace();   
        }   
    }   
  
    /**  
     * 获得该用户下面的所有表  
     */  
    public void getAllTableList(String schemaName)   
    {   
        try  
        {   
            // table type. Typical types are "TABLE", "VIEW", "SYSTEM   
            // TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS",   
            // "SYNONYM".   
            String[] types =   
            { "TABLE" };   
            ResultSet rs = dbMetaData.getTables(null, schemaName, "%", types);   
            while (rs.next())   
            {   
                String tableName = rs.getString("TABLE_NAME");   
                // table type. Typical types are "TABLE", "VIEW", "SYSTEM   
                // TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS",   
                // "SYNONYM".   
                String tableType = rs.getString("TABLE_TYPE");   
                // explanatory comment on the table   
                String remarks = rs.getString("REMARKS");   
                System.out.println(tableName + "-" + tableType + "-" + remarks);   
            }   
        } catch (SQLException e)   
        {   
            // TODO: handle SQLException   
            e.printStackTrace();   
        }   
    }   
  
    /**  
     * 获得该用户下面的所有视图  
     */  
    public void getAllViewList(String schemaName)   
    {   
        try  
        {   
            String[] types =   
            { "VIEW" };   
            ResultSet rs = dbMetaData.getTables(null, schemaName, "%", types);   
            while (rs.next())   
            {   
                String viewName = rs.getString("TABLE_NAME");   
                // table type. Typical types are "TABLE", "VIEW", "SYSTEM   
                // TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS",   
                // "SYNONYM".   
                String viewType = rs.getString("TABLE_TYPE");   
                // explanatory comment on the table   
                String remarks = rs.getString("REMARKS");   
                System.out.println(viewName + "-" + viewType + "-" + remarks);   
            }   
        } catch (SQLException e)   
        {   
            // TODO: handle SQLException   
            e.printStackTrace();   
        }   
    }   
  
    /**  
     * 获得数据库中所有方案名称  
     */  
    public void getAllSchemas()   
    {   
        try  
        {   
            ResultSet rs = dbMetaData.getSchemas();   
            while (rs.next())   
            {   
                String tableSchem = rs.getString("TABLE_SCHEM");   
                System.out.println(tableSchem);   
            }   
        } catch (SQLException e)   
        {   
            // TODO: handle SQLException   
            e.printStackTrace();   
        }   
    }   
  
    /**  
     * 获得表或视图中的所有列信息  
     */  
    public void getTableColumns(String schemaName, String tableName)   
    {   
        try  
        {   
            ResultSet rs = dbMetaData.getColumns(null, schemaName, tableName, "%");   
            while (rs.next())   
            {   
                // table catalog (may be null)   
                String tableCat = rs.getString("TABLE_CAT");   
                // table schema (may be null)   
                String tableSchemaName = rs.getString("TABLE_SCHEM");   
                // table name   
                String tableName_ = rs.getString("TABLE_NAME");   
                // column name   
                String columnName = rs.getString("COLUMN_NAME");   
                // SQL type from java.sql.Types   
                int dataType = rs.getInt("DATA_TYPE");   
                // Data source dependent type name, for a UDT the type name is   
                // fully qualified   
                String dataTypeName = rs.getString("TYPE_NAME");   
                // table schema (may be null)   
                int columnSize = rs.getInt("COLUMN_SIZE");   
                // the number of fractional digits. Null is returned for data   
                // types where DECIMAL_DIGITS is not applicable.   
                int decimalDigits = rs.getInt("DECIMAL_DIGITS");   
                // Radix (typically either 10 or 2)   
                int numPrecRadix = rs.getInt("NUM_PREC_RADIX");   
                // is NULL allowed.   
                int nullAble = rs.getInt("NULLABLE");   
                // comment describing column (may be null)   
                String remarks = rs.getString("REMARKS");   
                // default value for the column, which should be interpreted as   
                // a string when the value is enclosed in single quotes (may be   
                // null)   
                String columnDef = rs.getString("COLUMN_DEF");   
                //                 
                int sqlDataType = rs.getInt("SQL_DATA_TYPE");   
                //                 
                int sqlDatetimeSub = rs.getInt("SQL_DATETIME_SUB");   
                // for char types the maximum number of bytes in the column   
                int charOctetLength = rs.getInt("CHAR_OCTET_LENGTH");   
                // index of column in table (starting at 1)   
                int ordinalPosition = rs.getInt("ORDINAL_POSITION");   
                // ISO rules are used to determine the nullability for a column.   
                // YES --- if the parameter can include NULLs;   
                // NO --- if the parameter cannot include NULLs   
                // empty string --- if the nullability for the parameter is   
                // unknown   
                String isNullAble = rs.getString("IS_NULLABLE");   
                // Indicates whether this column is auto incremented   
                // YES --- if the column is auto incremented   
                // NO --- if the column is not auto incremented   
                // empty string --- if it cannot be determined whether the   
                // column is auto incremented parameter is unknown   
                String isAutoincrement = rs.getString("IS_AUTOINCREMENT");   
                System.out.println(tableCat + "-" + tableSchemaName + "-" + tableName_ + "-" + columnName + "-"  
                        + dataType + "-" + dataTypeName + "-" + columnSize + "-" + decimalDigits + "-" + numPrecRadix   
                        + "-" + nullAble + "-" + remarks + "-" + columnDef + "-" + sqlDataType + "-" + sqlDatetimeSub   
                        + charOctetLength + "-" + ordinalPosition + "-" + isNullAble + "-" + isAutoincrement + "-");   
            }   
        } catch (SQLException e)   
        {   
            // TODO: handle SQLException   
            e.printStackTrace();   
        }   
    }   
  
    /**  
     * 获得一个表的索引信息  
     */  
    public void getIndexInfo(String schemaName, String tableName)   
    {   
        try  
        {   
            ResultSet rs = dbMetaData.getIndexInfo(null, schemaName, tableName, true, true);   
            while (rs.next())   
            {   
                // Can index values be non-unique. false when TYPE is   
                // tableIndexStatistic   
                boolean nonUnique = rs.getBoolean("NON_UNIQUE");   
                // index catalog (may be null); null when TYPE is   
                // tableIndexStatistic   
                String indexQualifier = rs.getString("INDEX_QUALIFIER");   
                // index name; null when TYPE is tableIndexStatistic   
                String indexName = rs.getString("INDEX_NAME");   
                // index type:   
                // tableIndexStatistic - this identifies table statistics that   
                // are returned in conjuction with a table's index descriptions   
                // tableIndexClustered - this is a clustered index   
                // tableIndexHashed - this is a hashed index   
                // tableIndexOther - this is some other style of index   
                short type = rs.getShort("TYPE");   
                // column sequence number within index; zero when TYPE is   
                // tableIndexStatistic   
                short ordinalPosition = rs.getShort("ORDINAL_POSITION");   
                // column name; null when TYPE is tableIndexStatistic   
                String columnName = rs.getString("COLUMN_NAME");   
                // column sort sequence, "A" => ascending, "D" => descending,   
                // may be null if sort sequence is not supported; null when TYPE   
                // is tableIndexStatistic   
                String ascOrDesc = rs.getString("ASC_OR_DESC");   
                // When TYPE is tableIndexStatistic, then this is the number of   
                // rows in the table; otherwise, it is the number of unique   
                // values in the index.   
                int cardinality = rs.getInt("CARDINALITY");   
                System.out.println(nonUnique + "-" + indexQualifier + "-" + indexName + "-" + type + "-"  
                        + ordinalPosition + "-" + columnName + "-" + ascOrDesc + "-" + cardinality);   
            }   
        } catch (SQLException e)   
        {   
            // TODO: handle SQLException   
            e.printStackTrace();   
        }   
    }   
  
    /**  
     * 获得一个表的主键信息  
     */  
    public void getAllPrimaryKeys(String schemaName, String tableName)   
    {   
        try  
        {   
            ResultSet rs = dbMetaData.getPrimaryKeys(null, schemaName, tableName);   
            while (rs.next())   
            {   
                // column name   
                String columnName = rs.getString("COLUMN_NAME");   
                // sequence number within primary key( a value of 1 represents   
                // the first column of the primary key, a value of 2 would   
                // represent the second column within the primary key).   
                short keySeq = rs.getShort("KEY_SEQ");   
                // primary key name (may be null)   
                String pkName = rs.getString("PK_NAME");   
                System.out.println(columnName + "-" + keySeq + "-" + pkName);   
            }   
        } catch (SQLException e)   
        {   
            // TODO: handle SQLException   
            e.printStackTrace();   
        }   
    }   
  
    /**  
     * 获得一个表的外键信息  
     */  
    public void getAllExportedKeys(String schemaName, String tableName)   
    {   
        try  
        {   
            ResultSet rs = dbMetaData.getExportedKeys(null, schemaName, tableName);   
            while (rs.next())   
            {   
                // primary key table catalog (may be null)   
                String pkTableCat = rs.getString("PKTABLE_CAT");   
                // primary key table schema (may be null)   
                String pkTableSchem = rs.getString("PKTABLE_SCHEM");   
                // primary key table name   
                String pkTableName = rs.getString("PKTABLE_NAME");   
                // primary key column name   
                String pkColumnName = rs.getString("PKCOLUMN_NAME");   
                // foreign key table catalog (may be null) being exported (may   
                // be null)   
                String fkTableCat = rs.getString("FKTABLE_CAT");   
                // foreign key table schema (may be null) being exported (may be   
                // null)   
                String fkTableSchem = rs.getString("FKTABLE_SCHEM");   
                // foreign key table name being exported   
                String fkTableName = rs.getString("FKTABLE_NAME");   
                // foreign key column name being exported   
                String fkColumnName = rs.getString("FKCOLUMN_NAME");   
                // sequence number within foreign key( a value of 1 represents   
                // the first column of the foreign key, a value of 2 would   
                // represent the second column within the foreign key).   
                short keySeq = rs.getShort("KEY_SEQ");   
                // What happens to foreign key when primary is updated:   
                // importedNoAction - do not allow update of primary key if it   
                // has been imported   
                // importedKeyCascade - change imported key to agree with   
                // primary key update   
                // importedKeySetNull - change imported key to NULL if its   
                // primary key has been updated   
                // importedKeySetDefault - change imported key to default values   
                // if its primary key has been updated   
                // importedKeyRestrict - same as importedKeyNoAction (for ODBC   
                // 2.x compatibility)   
                short updateRule = rs.getShort("UPDATE_RULE");   
  
                // What happens to the foreign key when primary is deleted.   
                // importedKeyNoAction - do not allow delete of primary key if   
                // it has been imported   
                // importedKeyCascade - delete rows that import a deleted key   
                // importedKeySetNull - change imported key to NULL if its   
                // primary key has been deleted   
                // importedKeyRestrict - same as importedKeyNoAction (for ODBC   
                // 2.x compatibility)   
                // importedKeySetDefault - change imported key to default if its   
                // primary key has been deleted   
                short delRule = rs.getShort("DELETE_RULE");   
                // foreign key name (may be null)   
                String fkName = rs.getString("FK_NAME");   
                // primary key name (may be null)   
                String pkName = rs.getString("PK_NAME");   
                // can the evaluation of foreign key constraints be deferred   
                // until commit   
                // importedKeyInitiallyDeferred - see SQL92 for definition   
                // importedKeyInitiallyImmediate - see SQL92 for definition   
                // importedKeyNotDeferrable - see SQL92 for definition   
                short deferRability = rs.getShort("DEFERRABILITY");   
                System.out.println(pkTableCat + "-" + pkTableSchem + "-" + pkTableName + "-" + pkColumnName + "-"  
                        + fkTableCat + "-" + fkTableSchem + "-" + fkTableName + "-" + fkColumnName + "-" + keySeq + "-"  
                        + updateRule + "-" + delRule + "-" + fkName + "-" + pkName + "-" + deferRability);   
            }   
        } catch (SQLException e)   
        {   
            // TODO: handle SQLException   
            e.printStackTrace();   
        }   
    }   
  
    public DatabaseMetaDateApplication()   
    {   
        this.getDatabaseMetaData();   
    }   
}